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

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.

This is a regression bug for 6.50.258 Service Pack 3 (SP3). The symptoms are not seen in 6.50.201 (base release) or 6.50.240 (SP2).

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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.