FIX: Delete Using EXISTS/NOT EXISTS and Subquery Does Not Escalate Page Lock to Table Lock

• Microsoft SQL Server version 6.5 Service Pack 3 and later

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 SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: prodsql sp3 sp5 correlated escalation escalate decouple

Keywords          : SSrvLock kbbug6.50.sp3 kbfix6.50.SP5 
Version           : winnt:6.5 Service Pack 3 and later
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 21, 1999