TABLOCK implications

I've been planning to write about the implications of TABLOCK since I started the this tablock hint series(part-1 and part-2), but I got sidetracked. So today, here I am, writing about it.

TABLOCK can greatly improve insert performance under the right conditions, but it has some implications to consider. It’s important to understand where this hint cannot be used to avoid shooting yourself in the foot, specifically due to the locking behavior of TABLOCK.

In this article, we'll explore how TABLOCK's locking behavior makes it less suitable for tables that cannot afford significant blocking.

Demo

1. Let’s create a table called dbo.TestLock and load initial test data into it.

   USE DBATEST;
   GO
   
   DROP TABLE IF EXISTS dbo.TestLock;
   GO
   
   --Create table
   CREATE TABLE dbo.TestLock (
       id INT IDENTITY(1,1) PRIMARY KEY,
       data VARCHAR(100)
   );
   GO
   
   
   -- Load table with initial data
   INSERT INTO dbo.TestLock (data)
   SELECT TOP 2000 'TestData'
   FROM master.dbo.spt_values;
   GO

2. Verify there are no other locks on this database, before we demo the locks from TABLOCK.

   Result: There is a shared(S) lock on the database from the current session. In this case, it is spid 77 which has a connection open to the database. If you open another SSMS window, another spid will appear with a shared lock, this is normal.

   --Verify existing locks
   SELECT request_session_id
   	,resource_type, resource_subtype
   	,resource_description
   	,request_type
   	,request_mode
   	,request_status
   FROM sys.dm_tran_locks
   WHERE request_session_id =@@SPID

3. Now, insert data without the hint and keep the transaction open to view the acquired locks.

   --Insert data without hint and do not commit
   BEGIN TRAN;
   INSERT INTO dbo.TestLock (data)
   SELECT TOP 2000 'TestData'
   FROM master.dbo.spt_values;
   GO

4. Let’s check the locks acquired by this regular insert statement. Note that this insert is still not committed for us to view the locks.

   Result: Around 2000 exclusive (X) locks at the key level.

   --Check acquired locks 
   SELECT request_session_id
   	,resource_type, resource_subtype
   	,resource_description
   	,request_type
   	,request_mode
   	,request_status
   FROM sys.dm_tran_locks
   WHERE request_session_id = @@SPID  

• Around 2000 exclusive (X) locks at the key level. This is the lowest level lock. This is to make sure that multiple updates are not made to the same row at the same time.

• Granular key-level locks reduce blocking, because concurrent operations on the table can access different parts of the table.

• These locks can escalate to page or table-level locks if thresholds are crossed.

• There is also an Intent Exclusive (IX) lock on the object, which means that there is an intention to place exclusive lock on the object. (IX lock is compatible with another IX lock from other operation, and the other operation can continue when the current operation’s X lock at row level is removed.)

5. Next, issue ROLLBACK.

6. Then, insert same data using TABLOCK hint and do not commit it.

   --Insert data with hint and do not commit
   BEGIN TRAN;
   INSERT INTO dbo.TestLock WITH (TABLOCK) (data)
   SELECT TOP 2000'TestData'
   FROM master.dbo.spt_values;
   GO

7. Let’s check what the acquired locks are this time, from the TABLOCK hint.
   Result:

   1. An exclusive (X) lock on the entire table. For regular insert without the hint, it was X lock on the keys(row-level) and not on the entire table.

   2. This table-level lock prevents any other operations from accessing the table, causing blocking.

      --Check acquired locks 
      SELECT request_session_id
      	,resource_type, resource_subtype
      	,resource_description
      	,request_type
      	,request_mode
      	,request_status
      FROM sys.dm_tran_locks
      WHERE request_session_id =@@SPID

      

8. Issue a ROLLBACK or COMMIT to wrap up the demo.

Takeaways:

For a regular insert operation:

• Row-level (RID or KEY) or page-level (PAG) locks.

• Less blocking

• Lock escalation occurs if lock threshold is reached

For insert with TABLOCK hint:

• Table-level lock (TAB)

• More blocking

• No lock escalation

Best Practices for Using TABLOCK

Suitable Use Cases:

• Temp tables in stored procedures, because there is no concurrent access within the session.

• OLAP environments: Columnstore indexes and large data loads without concurrency concerns.

• OLTP environments: On tables only when concurrency isn't critical or blocking is acceptable.

Avoid TABLOCK when:

• Concurrency is crucial.

• Working with volatile tables in OLTP systems.

• Tables with clustered or non-clustered indexes.

Pro Tip

If you don't see faster insert performance with TABLOCK, check for indexes on the table. I've made this mistake a few times and wondered why the operation wasn’t any faster.

Conclusion

Understanding the locking behavior of TABLOCK helps you decide when to use it for performance optimization and when to avoid it to prevent blocking.

Resources:

Part-1 and 2 of TABLOCK hint series:

• https://gohigh.substack.com/p/insert-with-tablock-hint-faster

• https://gohigh.substack.com/p/insert-with-tablock-hint-tables-with

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-blocking-problems-caused-lock-escalation#lock-escalation-thresholds

Thanks for reading,
Haripriya.
My LinkedIn