FIX: Blocking Lock Remains or Server Stops After Heavy Deadlock (170508)


The information in this article applies to:

  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
This article was previously published under Q170508
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:

162361 : 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
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbBug kbfix kbusage KB170508
©2003 Microsoft Corporation. All rights reserved.