BUG: Delete Using EXISTS & Subquery Doesn't Escalate Page Lock

• Microsoft SQL Server, version 6.5 Service Pack 3

SYMPTOMS

Deleting a table that includes at least one text or image datatype by using Transact-SQL statements EXISTS or NOT EXISTS and a correlated subquery, will not properly escalate page locks to a table lock. Although sp_lock will show an Ex_table lock on the table, Ex_page locks will continue to be acquired, in excess of the LE threshold, for all pages involved in the deletion. On larger tables, such deletions can exhaust available locks and generate a system message 1204:

   SQL Server has run out of LOCKS.

WORKAROUND

You can work around the problem by using either of these methods:

Separate the correlated subquery from the deletion by using the correlated subquery to move data into a temporary table.

-or-

Use a cursor on a unique index to delete the rows one by one.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 3. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.