SQL Server Performance Forum – Threads Archive

transaction lock doesn´t run as i expected

I Tried to run the following script in the 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 next 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 lock 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 lock and produces output: spiddbidObjIdIndIdTypeResourceModeStatus
——————————————————————————-
51500DB SGRANT
511855753430TAB ISGRANT Could somebody explain me why?

---

I am not very sure if XLOCK behaves this way. Have you tried the same with UPDLOCK? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Hi ya, It definitely locks the second session on my SQL2K server… until I issue a commit in the first what connection properties have you set in Query Analyzer? Cheers
Twan

---

Hi Twan, This morning we tried with table authors in DB pubs and locks second session, we then try to do it with table ISOLATION_TEST (we created in DB pubs) but converting clustered index on primary key to nonclustered. And then ok it works properly ?????!!!!

---

Sounds very suspicious to me… I’d consider escalating this up to MS support… Cheers
Twan

---

I find a link that explains all:
http://support.microsoft.com/default.aspx?scid=kb;en-us;324417&Product=sql Thanks.

---

]]>