TAB Lock in SQL Server

 TABLOCK is a table hint that instructs SQL Server to acquire a table-level lock rather than many row-level or page-level locks. The primary goal is to reduce lock management overhead, memory consumption, and CPU costs associated with maintaining large numbers of locks. TABLOCK is commonly used in ETL processes, bulk inserts, large updates, data warehouse loads, and maintenance operations. For read operations, TABLOCK typically results in a Shared Table Lock, while for update operations it generally results in an Exclusive Table Lock. One of the major advantages of TABLOCK is improved performance during bulk operations, and under certain conditions it can also enable minimal logging. However, the trade-off is reduced concurrency because the entire table becomes locked. In OLTP systems, excessive use of TABLOCK can lead to blocking and poor user experience, whereas in batch-processing or data warehouse environments it is often a highly effective optimization technique. Understanding when to trade concurrency for throughput is the key to using TABLOCK correctly in production systems.

Suppose we are updating a table which has millions of records

UPDATE Employee

SET Salary = Salary + 1000;

90,00,000 rows

Without TABLOCK SQL Server may create:

Row Lock

Row Lock

Row Lock

Row Lock

...

Thousands or millions of locks. It uses More Memory More CPU More Lock Management

TABLOCK Solution: Instead of 90,00,000 Row Locks SQL Server acquires 1 Table Lock.

Let’s see the demo

In session 1:

Running the below query.

BEGIN TRAN; UPDATE Employee WITH (TABLOCK) SET Salary = Salary + 1000; --commit

 

It acquired Exclusive Table Lock.

Session 2:

We are selecting records 

SELECT * FROM Employee;

Getting blocked.

Because table is locked.

TABLOCK vs Normal Locking

Feature	Normal Locking	TABLOCK
Lock Level	Row/Page	Table
Concurrency	High	Low
Memory Usage	Higher	Lower
Blocking	Less	More
Bulk Load Speed	Normal	Faster

TABLOCK is good for below cases:

Ø  Bulk Insert

Ø  ETL Loads

Ø  Large Data Warehouse Loads

Ø  Mass Update

Ø  Mass Delete

Ø  Maintenance Jobs

Bad for below cases:

Ø  OLTP Applications

Ø  High Concurrent Systems

Ø  Online Banking

Ø  E-commerce Checkout

Ø  Real-Time Applications