FIX: SH_PAGE Locks May Be Held on Inner Tables of JOINs

• Microsoft SQL Server, version 6.5

SYMPTOMS

A join of tables may hold the shared page locks on the inner tables of the join(s) for the duration of the SELECT.

CAUSE

The page locks on the inner table(s) of joins where a unique index matches the join on the inner table are not released until the end of the SELECT. On earlier versions, SQL Server would obtain and release the locks one at a time as the page chain is traversed. The outer table's page locks are not held. If the tables are large, this can be a significant concurrency impact. This did not happen in prior versions of SQL Server.

WORKAROUND

Use non-unique indexes instead of unique.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.