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; |
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.