2019-06-18

Lock Escalation is evaluated per Statement only, NOT per Session


Lock Escalation is an internal optimization process that SQL Server converts many fine-grained locks such as row/page locks into a single coarse-grained table/partition lock, in order to release memory held by the many fine-grained locks. When SQL Server is going to modify a large number of rows, it is more efficient for the database engine to take fewer, larger locks instead of dealing with a large number of individual locks. In this blog post, I'm going to explain that SQL Server evaluating lock escalation is per each statement, rather than per session. Let's take a look at the below demonstration:
USE AdventureWorks2008R2
GO
BEGIN TRAN
DELETE TOP(5000) FROM [Sales].[SalesOrderHeader];
-- Lock Escalation to Table Level
SELECT resource_type,resource_subtype,request_mode,COUNT(*)
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
GROUP BY resource_type,resource_subtype,request_mode;
ROLLBACK
GO
BEGIN TRAN
DECLARE @loop int = 1;
WHILE @loop < 10
BEGIN
    DELETE TOP(1000) FROM [Sales].[SalesOrderHeader];
    SET @loop += 1;
END
-- Lock Escalation NOT occurred!
SELECT resource_type,resource_subtype,request_mode,COUNT(*)
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
GROUP BY resource_type,resource_subtype,request_mode;
ROLLBACK

Below is the result:

As you can see, the first batch deletes 5000 rows in one statement, key and page locks are escalated into one table lock. The second batch deletes 10k rows by using a while loop, each statement just deletes 1000 rows, so no any locks being escalated.