Sunday, 4 October 2026

Lock Escalation in SQL Server

Imagine a mall with 10,000 parking slots. Each parking slot represents a row in a SQL Server table. Parking Slot 1, Parking Slot 2, Parking Slot 3 ...... Parking Slot 10000

Small Transaction (No Escalation): Suppose you reserve only 5 parking slots.

Slot 101, Slot 102, Slot 103, Slot 104, Slot 105. Mall management locks only those slots. Other customers can still park in other parking slot. This is similar to SQL Server using Row Locks. No lock escalation.

Case 2: Large Transaction: Now imagine a VIP event. We reserve 8000 parking slots. Mall management thinks, why manage 8000 individual reservations? Instead, we put ENTRY CLOSED

WHOLE PARKING RESERVED. Now nobody else can enter. This is equivalent to 8000 Row Locks 1 Table Lock, SQL Server says managing thousands of locks is expensive. Let me lock the entire table.

This is called Lock Escalation.

 

Lock Escalation is an internal SQL Server mechanism that converts many fine-grained locks such as row or page locks into a larger table or partition lock to reduce lock management overhead. Every lock consumes memory and CPU resources, so when SQL Server detects a large number of locks on the same object, typically around 5000 or more, or when lock memory pressure occurs, it may escalate them into a single higher-level lock. While escalation improves memory efficiency, it can significantly increase blocking because an entire table or partition becomes locked. Common causes include large UPDATE, DELETE, and INSERT operations. In production environments, lock escalation issues are usually mitigated by processing data in smaller batches, creating efficient indexes, partitioning large tables, and monitoring lock escalation events through Extended Events. Understanding lock escalation is important because it is a frequent root cause of blocking and performance issues in high-volume OLTP systems.

Let’s see the demo

Creating the table in inserting 100k records

CREATE TABLE dbo.Employee

(

    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,

    EmployeeName VARCHAR(100),

    Salary INT

);

GO

INSERT INTO dbo.Employee(EmployeeName, Salary)

SELECT TOP (110000)

       'Employee_' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(10)),

       50000

FROM sys.objects a

CROSS JOIN sys.objects b;

GO

SELECT COUNT(*) FROM dbo.Employee; 

 Session 1:

Updating 5 records.

BEGIN TRAN;

UPDATE dbo.Employee

SET Salary = Salary + 1000

WHERE EmployeeID <= 5;

--Commit

Transection is not yet committed. See the session log details.

SELECT

    request_session_id,

    resource_type,

    request_mode,

    resource_description

FROM sys.dm_tran_locks

Session 54 we are updating the 5 records. Here 5 key lock queried on the rows. Now committing the session54.

All lock released.

Now in the new session we are updating 10k records. Let’s see it will lock 10k rows or it issue the table lock.

See the lock details.

It acquired only 1 lock called Lock escalation.

 

No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts