FIX: Blocking Lock Remains or Server Stops After Heavy Deadlock

Last reviewed: December 19, 1997
Article ID: Q170508

----------------------- -------------------------------------- The information in this article applies to:

Microsoft SQL Server, versions 6.0 and 6.5

BUG #: 16986 (6.5)

       15716 (6.0)

SYMPTOMS

Under heavy deadlock conditions, certain locking patterns may cause blocking locks to remain. This may appear as a spid exiting without lock cleanup. For example a spid holding blocking locks may be KILLed or exit, yet the locks it held may remain and continue to block others. Afterwards sp_who may show the blocking spid as a newly-logged in process that holds no locks in the database, yet is blocking others.

This condition may infrequently occur on SQL Server builds 6.00.121 through 6.50.258. The sysprocesses.waittype for the blocked or blocking spids is usually 0x8003 (waiting on exclusive intent lock) or 0x8004 (waiting on shared intent lock). In some cases, a blocking spid may have a 0x8003 or 0x8004 waittype (hence not running, and will continue blocking others), yet not itself be blocked as indicated by sysprocesses.blocked. Less frequently, the server may stop responding following this occurrence.

Killing blocked spids under these conditions can cause the debug assertions described below (taken from SQL Server build 6.50.255).

SQL Server version 6.0 does not have assertions, but the overall behavior is similar. On SQL Server 6.0, sometimes you will see sysprocesses.blocked having an irrational value such as 4832. The best way to identify this problem on SQL Server 6.5 is to run a debug server, watch for abnormal blocking behavior, and observe whether the following assertions occur:

   Location: lockmgr.c: 5903
   Expression: lo
   Spid: 11
   Description: pss not found on the wait list

   Location: lockmgr.c: 2317
   Expression: pss->pssnext==NULL
   Spid: 11

For more information on what constitutes normal or abnormal blocking, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q162361
   TITLE     : Understanding and Resolving SQL Server Blocking Problems

WORKAROUND

To work around this problem, use standard techniques to reduce locking contention and deadlocks. This can include shortening the transaction path length, using lower transaction isolation levels, eliminating extraneous indexes, or ensuring that transactions acquire locks in the same order.

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 6.0 and 6.5. This problem was corrected in the latest Microsoft SQL Server 6.5 U.S. Service Pack. For information on obtaining the service pack, query on the following word in the Microsoft Knowledge Base (without the spaces):

   S E R V P A C K

Keywords          : kbbug6.00 kbbug6.50 SSrvGen SSrvLock kbfix6.50.sp4 kbusage
Version           : 6.0 6.5
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbworkaround

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 19, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.