TABLOCK vs TABLOCKX

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 "sharing" 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.

Big difference, TABLOCK will try to grab "shared" 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.

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 ...