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?
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.
XLOCK. This hint specifies that SQL Server should take an exclusive lock that is held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the specified resource.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With