BUG: Locks May Be Held Longer Than Necessary with Subqueries

• Microsoft SQL Server, version 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.