BUG: Locks May Be Held Longer Than Necessary with Subqueries (179923)
The information in this article applies to:
This article was previously published under Q179923
BUG #: NT: 17713 (6.5)
SYMPTOMS
A subquery run at the default transaction isolation level, READ COMMITTED,
holds a shared intent (SH_INT) lock on the table(s) referenced in the
subquery until the entire transaction is either committed or rolled back.
However, there is no need to hold the lock unless SQL Server is running at
a higher transaction isolation level.
WORKAROUND
If possible, rewrite the statement so as to not use a subquery; many
subqueries can be rewritten as joins, which oftentimes execute faster than
a subquery. Alternatively, you can use a temporary table or variable to
store a value representing the result of the expression containing the
subquery. You can populate this table or variable in a prior query, which
will release the lock when the query completes.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5. We
are researching this problem and will post new information here in the
Microsoft Knowledge Base as it becomes available.
MORE INFORMATION
The default transaction isolation level in SQL Server is READ COMMITTED.
When running in this mode, SQL Server is free to release page locks on each
page as soon as it has successfully sent the qualifying rows from that page
to the client application. As soon as all results have been processed, the
shared intent (SH_INT) lock on the table is also released. A subquery
expression simply returns a value of TRUE or FALSE, and the results are
implicitly processed within the server. SQL Server is unnecessarily
deferring the release of the SH_INT lock on the tables in the subquery
until the end of the transaction, rather than after processing the result
of the subquery.
In SQL Server 6.5, REPEATABLE READ is a synonym for SERIALIZABLE. When
these more restrictive isolation levels are specified, the locks must be
held so that the subquery would not visit any new or changed rows if it
were to be executed again within the same transaction. Because of this
restriction, these isolation levels require the SH_INT lock to be held on
the table until the transaction completes.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug KB179923 |
|---|
|