HINT XLOCK working properly? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

HINT XLOCK working properly?

Author

Topic

acanabate
Starting Member

1 Post

Posted - 2003-09-18 : 04:31:05

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:
spid dbid ObjId IndId Type Resource Mode Status
-------------------------------------------------------------------------------
53 5 0 0 DB S GRANT
53 5 645577338 1 PAG 1:28 IX GRANT
53 1 85575343 0 TAB IS GRANT
53 5 645577338 0 TAB IX GRANT
53 5 645577338 1 KEY (0a0087c006b1) X GRANT

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:
spid dbid ObjId IndId Type Resource Mode Status
-------------------------------------------------------------------------------
51 5 0 0 DB S GRANT
51 1 85575343 0 TAB IS GRANT

Could somebody explain me why?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources