Microsoft KB Archive/170508
Article ID: 170508
Article Last Modified on 10/16/2003
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q170508
BUG #: 16986 (6.5) 15716 (6.0)
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
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.
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: kbbug kbfix kbusage KB170508