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