BUG: Lock Conversion Processing Does Not Properly Wakeup Lock Waiter

ID: Q236955

The information in this article applies to:
BUG #: 56013 (SQLBUG_70)

SYMPTOMS

Lock conversion is the process of upgrading a lock to provide an enhanced control over a resource. For example, you set the transaction isolation level to serializable and then perform a select followed by an update of the same row in which a conversion from a shared lock to an exclusive lock is performed. Lock conversions are commonly encountered when using repeatable read or serializable locking isolation levels, but are not limited to these isolation levels. The following example shows the conversion from an IS-S to and IS-X to accommodate the conversion. 


use pubs
go

set transaction isolation level serializable
go

begin tran
go

select * from authors where au_id = '172-32-1176'
go

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------ 
7      1      0           0      DB                    S        GRANT
7      5      117575457   1      PAG  1:96             IS       GRANT
7      5      0           0      DB                    S        GRANT
7      5      0           0      DB                    S        GRANT
7      5      117575457   1      KEY  (28024f0bec4e)   IS-S     GRANT
7      5      117575457   0      TAB                   IS       GRANT

update authors set au_lname = 'test' where  au_id = '172-32-1176'
go

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------ 
7      1      0           0      DB                    S        GRANT
7      5      117575457   1      PAG  1:123            IX       GRANT
7      5      117575457   1      PAG  1:96             IX       GRANT
7      5      0           0      DB                    S        GRANT
7      5      0           0      DB                    S        GRANT
7      5      117575457   1      KEY  (28024f0bec4e)   IS-X     GRANT
7      5      117575457   2      KEY  (c706a58e9ca3)   X        GRANT
7      5      117575457   2      KEY  (08079ef3ee55)   X        GRANT


When working with multiple connections, which attempt to access the same lock resource, it is possible to get a standard blocking scenario. The following example is a simple block showing the sysprocesses blocked indication and the corresponding WAIT from sp_lock. 

spid   blocked status                         waittype waittime    
------ ------- ------------------------------ -------- ----------- 
7      0       sleeping                       0x0000   0
9      7       sleeping                       0x0009   235032

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------ 
9      5      117575457   1      KEY  (28024f0bec4e)   U        WAIT
When SPID 7 is done with the lock it will GRANT SPID 9 the lock and allow SPID 9 to run.

For these conditions, refer to the following article in the Microsoft Knowledge Base:
Q162361 INF: Understanding and Resolving SQL Server Blocking Problems
However, under some circumstances the lock conversion process does GRANT the lock to a waiting SPID but does not properly allow the SPID to execute. You may see the following output under these conditions: 

spid   blocked status                         waittype waittime    
------ ------- ------------------------------ -------- ----------- 
7      0       sleeping                       0x0000   0
9      0       sleeping                       0x0009   235032

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------ 
9      5      117575457   1      KEY  (28024f0bec4e)   U        GRANT
SPID 7 completed use of the lock resource that SPID 9 was waiting on. SPID 7 then GRANTed the lock to SPID 9 but did not clear the waittype and waitresource for SPID 9, leaving it in the sleeping state.

The nature of this problem is such that the SPID is actually granted the lock but the SPID remains in a sleeping state with a lock waittype. (Lock mode waittypes would include, but may not be limited to, those waittypes of 0x1 to 0xF.)

When in this state, closing the client application connection or using the T-SQL KILL command rolls back the transaction and allows the server to continue normal processing.


RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
The English version of this fix should have the following file attributes or later: 

   File name      Platform
   ---------------------------------------------------------
   s70710i.exe    Intel
   s70710a.exe    Alpha
NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

NOTE: If this product was already installed on your computer when you purchased it from the Original Equipment Manufacturer (OEM) and you need this fix, please call the Pay Per Incident number listed on the above Web site. If you contact Microsoft to obtain this fix, and if it is determined that you only require the fix you requested, no fee will be charged. However, if you request additional technical support, and if your no-charge technical support period has expired, or if you are not eligible for standard no-charge technical support, you may be charged a non-refundable fee.

For more information about eligibility for no-charge technical support, see the following article in the Microsoft Knowledge Base:
Q154871 Determining If You Are Eligible for No-Charge Technical Support


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.


MORE INFORMATION

Applications written to use Microsoft Transaction Server (MTS) components with transactional support provided by the Distribution Transaction Coordinator (DTC) may be more susceptible to the problem. The default isolation level used by the MTS/DTC combination is serializable.

Additional query words: 


Keywords          : kbSQLServ700bug 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: July 16, 1999