TABLOCK vs TABLOCKX

by

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.

More Related Contents:

  1. Update a table using JOIN in SQL Server?
  2. Comma separated results in SQL
  3. T-SQL stored procedure that accepts multiple Id values
  4. Understanding PIVOT function in T-SQL
  5. Calculate running total / running balance
  6. How do I escape a percentage sign in T-SQL?
  7. Using RegEx in SQL Server
  8. List of all index & index columns in SQL Server DB
  9. How do you count the number of occurrences of a certain substring in a SQL varchar?
  10. Find index of last occurrence of a sub-string using T-SQL
  11. Hidden Features of SQL Server
  12. SQL Server 2008 Empty String vs. Space
  13. Preserving ORDER BY in SELECT INTO
  14. SQL Server: combining multiple rows into one row
  15. Query to list all stored procedures
  16. SQL Server: Index columns used in like?
  17. WHERE clause on SQL Server “Text” data type
  18. Return all possible combinations of values on columns in SQL
  19. SQL Server error on update command – “A severe error occurred on the current command”
  20. Recursive sum in tree structure
  21. Multiple Column Pivot in T-SQL
  22. Joining multiple tables returns NULL value
  23. Which join syntax is better?
  24. Compute MD5 hash of a UTF8 string
  25. LPAD in SQL Server 2008
  26. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  27. How do I remove non-breaking spaces from a column in SQL server?
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment