Microsoft KB Archive/177114

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

Article ID: 177114

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 3

-



This article was previously published under Q177114



BUG #: 17399 (NT: 6.5)



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.

Additional query words: prodsql sp3 sp5 correlated escalation escalate decouple

Keywords: kbbug kbfix KB177114

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.