TABLOCK vs TABLOCKX

Question

what is the difference between TABLOCK and TABLOCKX http   msdn microsoft com en-us library ms187373 aspx states that TABLOCK is a shared lock while TABLOCKX is an exclusive lock  Is the first maybe only an index lock of sorts  And what is the concept of sharing a lock

User · Answer

This is more of an example where TABLOCK did not work for me and TABLOCKX did.

I have 2 sessions, that both use the default (READ COMMITTED) isolation level:

Session 1 is an explicit transaction that will copy data from a linked server to a set of tables in a database, and takes a few seconds to run. [Example, it deletes Questions] Session 2 is an insert statement, that simply inserts rows into a table that Session 1 doesn't make changes to. [Example, it inserts Answers].

(In practice there are multiple sessions inserting multiple records into the table, simultaneously, while Session 1 is running its transaction).

Session 1 has to query the table Session 2 inserts into because it can't delete records that depend on entries that were added by Session 2. [Example: Delete questions that have not been answered].

So, while Session 1 is executing and Session 2 tries to insert, Session 2 loses in a deadlock every time.

So, a delete statement in Session 1 might look something like this: DELETE tblA FROM tblQ LEFT JOIN tblX on ... LEFT JOIN tblA a ON tblQ.Qid = tblA.Qid WHERE ... a.QId IS NULL and ...

The deadlock seems to be caused from contention between querying tblA while Session 2, [3, 4, 5, ..., n] try to insert into tblA.

In my case I could change the isolation level of Session 1's transaction to be SERIALIZABLE. When I did this: The transaction manager has disabled its support for remote/network transactions.

So, I could follow instructions in the accepted answer here to get around it: The transaction manager has disabled its support for remote/network transactions

But a) I wasn't comfortable with changing the isolation level to SERIALIZABLE in the first place- supposedly it degrades performance and may have other consequences I haven't considered, b) didn't understand why doing this suddenly caused the transaction to have a problem working across linked servers, and c) don't know what possible holes I might be opening up by enabling network access.

There seemed to be just 6 queries within a very large transaction that are causing the trouble.

So, I read about TABLOCK and TabLOCKX.

I wasn't crystal clear on the differences, and didn't know if either would work. But it seemed like it would. First I tried TABLOCK and it didn't seem to make any difference. The competing sessions generated the same deadlocks. Then I tried TABLOCKX, and no more deadlocks.

So, in six places, all I needed to do was add a WITH (TABLOCKX).

So, a delete statement in Session 1 might look something like this: DELETE tblA FROM tblQ q LEFT JOIN tblX x on ... LEFT JOIN tblA a WITH (TABLOCKX) ON tblQ.Qid = tblA.Qid WHERE ... a.QId IS NULL and ...

User · Answer

Quite an old article on mssqlcity attempts to explain the types of locks   Shared locks are used for operations that do not change or update data  such as a SELECT statement  Update locks are used when SQL Server intends to modify a page  and later promotes the update page lock to an exclusive page lock before actually making the changes  Exclusive locks are used for the data modification operations  such as UPDATE  INSERT  or DELETE   What it doesn t discuss are Intent  which basically is a modifier for these lock types   Intent  Shared Exclusive  locks are locks held at a higher level than the real lock  So  for instance  if your transaction has an X lock on a row  it will also have an IX lock at the table level  which stops other transactions from attempting to obtain an incompatible lock at a higher level on the table  e g  a schema modification lock  until your transaction completes or rolls back    The concept of  quot sharing quot  a lock is quite straightforward - multiple transactions can have a Shared lock for the same resource  whereas only a single transaction may have an Exclusive lock  and an Exclusive lock precludes any transaction from obtaining or holding a Shared lock

User · Answer

Big difference  TABLOCK will try to grab  quot shared quot  locks  and TABLOCKX exclusive locks  If you are in a transaction and you grab an exclusive lock on a table  EG  SELECT 1 FROM TABLE WITH  TABLOCKX  No other processes will be able to grab any locks on the table  meaning all queries attempting to talk to the table will be blocked until the transaction commits  TABLOCK only grabs a shared lock  shared locks are released after a statement is executed if your transaction isolation is READ COMMITTED  default   If your isolation level is higher  for example  SERIALIZABLE  shared locks are held until the end of a transaction   Shared locks are  hmmm  shared  Meaning 2 transactions can both read data from the table at the same time if they both hold a S or IS lock on the table  via TABLOCK   However  if transaction A holds a shared lock on a table  transaction B will not be able to grab an exclusive lock until all shared locks are released  Read about which locks are compatible with which at msdn   Both hints cause the db to bypass taking more granular locks  like row or page level locks   In principle  more granular locks allow you better concurrency  So for example  one transaction could be updating row 100 in your table and another row 1000  at the same time from two transactions  it gets tricky with page locks  but lets skip that   In general granular locks is what you want  but sometimes you may want to reduce db concurrency to increase performance of a particular operation and eliminate the chance of deadlocks  In general you would not use TABLOCK or TABLOCKX unless you absolutely needed it for some edge case

[sql-server] TABLOCK vs TABLOCKX

Examples related to sql-server

Examples related to tsql