September 18, 2003 at 2:29 am
I Tried to run following script in Query Analizer in two different opened connetions
In connection 1:
USE pubs
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM ISOLATION_TEST WITH (XLOCK) WHERE col1 = 10
SELECT @@spid, OBJECT_ID('ISOLATION_TEST')
EXEC sp_lock @@spid
--not commiting the transaction in order to run code in connection 2
I Get the output:
spiddbidObjIdIndIdTypeResourceModeStatus
-------------------------------------------------------------------------------
53500DB SGRANT
5356455773381PAG1:28 IXGRANT
531855753430TAB ISGRANT
5356455773380TAB IXGRANT
5356455773381KEY(0a0087c006b1) XGRANT
In connection 2 I run the script:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM ISOLATION_TEST WHERE col1 = 10
SELECT @@spid, OBJECT_ID('ISOLATION_TEST')
EXEC sp_lock @@spid
COMMIT TRAN
I expected this second script in connection 2 to block because it tries to lock the same row with a shared lock that it is not compatible with previously granted exclusive lock to connection 1. But it does not block and produces output (after commiting transaction in connection 1):
spiddbidObjIdIndIdTypeResourceModeStatus
-------------------------------------------------------------------------------
51500DB SGRANT
511855753430TAB ISGRANT
Could somebody explain me why SELECT in connection 2 is allowed to get shared lock over the same row that has been exclusively locked by process in connection 1?
5356455773381KEY(0a0087c006b1) XGRANT?
September 18, 2003 at 12:29 pm
I'm not sure it will resolve your problem. Try it again. I just did it with pubs..authors and it worked as intended on S2K SP3.
First connection:
USE pubs
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM authors WITH (XLOCK) WHERE au_lname = 'White'
SELECT @@spid, OBJECT_ID('authors')
EXEC sp_lock @@spid
Second connection was waining until i commit first one :
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM authors WHERE au_lname = 'White'
SELECT @@spid, OBJECT_ID('ISOLATION_TEST')
EXEC sp_lock @@spid
COMMIT TRAN
Here are locks:
db type object resource mode status objid indid spid
------- ---- -------------- --------------- ----- ------ ----------- ------ -----
pubs DB pubs S GRANT 0 0 52
pubs DB pubs S GRANT 0 0 53
pubs KEY aunmind (58039ef3ee55) S WAIT 1977058079 2 53
pubs KEY aunmind (58039ef3ee55) X GRANT 1977058079 2 52
pubs KEY UPKCL_auidind (02014f0bec4e) X GRANT 1977058079 1 52
pubs PAG authors 1:120 IX GRANT 1977058079 1 52
pubs PAG authors 1:124 IS GRANT 1977058079 2 53
pubs PAG authors 1:124 IX GRANT 1977058079 2 52
pubs TAB authors IS GRANT 1977058079 0 53
pubs TAB authors IX GRANT 1977058079 0 52
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply