Lock Escalation in the SQL Engine

Locking is an essential part of upholding data integrity. Locks are 96 byte in-memory structures that are especially essential when it comes to the isolation requirement in ACID compliancy.

However there is a draw back to having transactions doing too many locks, and that is it can be quite memory intensive if a single query is acquiring many locks. In order to reduce system overhead, SQL Server goes and does what is called lock escalation.

What is Lock Escalation?

Lock escalation is the process of converting many fine-grained low level locks (e.g row/page locks) into table level locks. This reduces system overhead but increases the possibility for concurrency contention, so it’s a bit of a double edged sword in that we reduce memory pressure but now we may have to deal with resource contention.

Lock escalation applies to SQL Server, Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics.

When does Lock Escalation get triggered?

Lock escalation occurs when a single transaction acquires at least 5,000 locks on a single resource, or when the number of locks in an instance exceeds the configured memory thresholds.

Lock Escalation in Action

To demonstrate, I start by attempting to insert 100 records into the ‘OrderItems’ table. This is executed from session 56.

-- Session 56

BEGIN TRAN;
INSERT INTO OrderItems (OrderID, OrderYear, StockID, Quantity, Discount)
SELECT TOP (100) v.*
FROM (VALUES (1,2019,1,1,0)) v(a,b,c,d,e)
CROSS JOIN sys.columns c1
CROSS JOIN sys.columns c2;
--ROLLBACK;

Notice that I commented out ROLLBACK. This is because I wanted to leave the transaction open for us to analyse.

Now I will use the dynamic management view (DMV) ‘dm_tran_locks’ to investigate what is currently being locked by this transaction. I execute this from another session – session 54.

-- Session 54

SELECT 
	request_session_id,
	resource_type,
	request_mode,
	request_status,
	resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = db_id(N'TSQLV5')
AND request_session_id = 56

I filtered the result set by the database name and session number. Database name being TSQLV5 and the session number is 56 as that is the session that is applying the locks.

Below are the results from ‘dm_tran_locks’.

The INSERT statement from session 56 is applying 103 locks. Majority of those locks are row level locks, this is indicated by the KEY value in the resource_type column. This is to be expected as we are aiming to insert 100 records. So the transaction applied 100 row level locks, and 3 intent level locks on database objects higher in the lock hierarchy. The intent locks are there to enforce data integrity.

Let us now change the INSERT statement to insert 10,000 records.

-- Session 56

--BEGIN TRAN;
INSERT INTO OrderItems (OrderID, OrderYear, StockID, Quantity, Discount)
SELECT TOP (10000) v.*
FROM (VALUES (1,2019,1,1,0)) v(a,b,c,d,e)
CROSS JOIN sys.columns c1
CROSS JOIN sys.columns c2;
--ROLLBACK;

Notice that I also commented out the BEGIN TRAN. This is because I do not want to create another separate transaction. Keep in mind that I have not rollbacked the earlier transaction.

Again I run the ‘dm_tran_locks’ query to take a look at the locking going on by Session 56.

After increasing the number of inserts, the number of locks have been reduced. Now applying only 7 locks. We can see that an Exclusive (X) lock has been applied on an OBJECT resource_type. This indicates an exclusive lock on the table. This is a clear demonstration of lock escalation in action as the SQL engine knew applying over 10,000 row level locks would be too memory intensive and instead decided to escalate those locks to the table level and in turn applying an exclusive lock on the table. Doing this reduces memory consumption but also increases the possibility of lock contention to that table.

Advertisement