Article ID: 236955
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q236955
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:
162361 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.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
MORE INFORMATION
Applications written to use Microsoft Transaction Server (MTS) components with transactional support provided by the Microsoft Distribution Transaction Coordinator (DTC) may be more susceptible to the problem. The default isolation level used by the MTS/DTC combination is serializable.
Keywords: kbbug kbfix KB236955
