TabLock and TablLockX Confusion

Question

TabLock and TablLockX Confusion

JKSQL

SSCrazy Eights

Points: 8481
More actions
March 4, 2013 at 8:54 pm

#290650

I am trying to figure out how this works. Locking difference between TabLock and TablLockX. They seem to do the same thing. I did my queries on the tally table for simplicity (1 mill +)
BEGIN
BEGIN TRANSACTION
-- UPDATE Common.Tally WITH (TABLOCK)
--SET N = 4
--WHERE N = 4
SELECT N FROM Common.Tally WITH (TABLOCK)
WHERE N = 4
WAITFOR DELAY '00:15'
COMMIT TRANSACTION
END
You can use the update or the Select it has the same effect. In another SSMS window I now run a Select
Select * FROM Common.Tally
WHERE N=100
When I execute SP_Lock I get
Tally X (exclusive Lock)
Tally IS (Intent to share)
I thought LockX locked it down to where it could not be multi-user. I would like to Update/Insert/Delete and as long as it is not the same records. People should be able to ping the table with selects. Can someone help with what I am missing?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The IS lock is most likely in a wait status while the X lock is in a grant status.

Check sys.dm_tran_locks and see what is happening there. I am seeing the IS lock in a wait status using the code you supplied.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

The IS lock is most likely in a wait status while the X lock is in a grant status.

If X lock is in grant status then why does the select not work? I would think grant status would allow reads.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The X lock is coming from an update statement. The update while there is a table lock is not permitting the select to run until that transaction is committed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 4

So I can take that as when you apply a hint it is going to lock that table whether it is Tablock or TablockX while the update is going and no selects will be able to be made while the transaction is uncommitted.

If I do a Select with a tablock it looks like I can not get a shared lock on that either. Tablock and TablockX seem to perform the same way not allowing any shares until the transaction is complete.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Tablock -> take locks at the table level. If the statement would normally take shared locks, that will be a shared table lock. If the statement would normally take exclusive locks, that will be an exclusive table lock.

TablockX -> take a table-level exclusive lock regardless of what lock type would normally be used.

Update, delete and insert all take exclusive locks. selects take shared locks.

TablockX = Tablock, XLock

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

JKSQL (3/5/2013)
So I can take that as when you apply a hint it is going to lock that table whether it is Tablock or TablockX while the update is going and no selects will be able to be made while the transaction is uncommitted.
If I do a Select with a tablock it looks like I can not get a shared lock on that either. Tablock and TablockX seem to perform the same way not allowing any shares until the transaction is complete.

Using the same code you provided, when I perform the select within the loop using a tablock hint, I am able to run the second select without problem.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 7

I apologize - I must have killed the session after the update the first time. So I still had a transaction open. If I run the select in the tablock and then do the select under it I get a shared lock. I think the documentation is correct then.