During rollback, is lock escalation reversed

Generally, SQL Server will take locks during updates. It also supports Lock Escalation:


Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

During a transaction ROLLBACK, does SQL Server de-escalate the locks, putting back the fine-grained locks?

SQL Server does not de-escalate the lock.

I investigated using a "Numbers" table with 100,000 rows. Empirically, updating 5,000 rows produced a corresponding number of RID locks in sys.dm_tran_locks. Updating a further 10,000 rows caused escalation to a single table lock. This was consistently reproducible. To minimise the objects involved the table was a heap without indexes.

I used Extended Event tracing to capture lock_acquired and lock_released events. For ease of analysis I used separate traces during the UPDATE and ROLLBACK phases.

Two sessions (SSMS windows) were used - one for the DML statements and one for the trace DDL. I could not use as single session as I wanted to stop and start traces while the transaction is open and this is not permitted. The isolation level throughout is READCOMITTED.

The result of select @@VERSION is


Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (Intel X86) Mar 26 2015 21:49:16 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

The steps taken were

-
Create and start the Acquire trace.

CREATE EVENT SESSION DuringUpdate
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'c:\temp\LockRollback\DuringUpdate.xel',
     metadatafile = 'c:\temp\LockRollback\DuringUpdate.xem')
WITH(EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
    );

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = start;

-
Begin a transaction and update 5000 rows.

begin transaction;

update dbo.Numbers
set Number = Number
where Number between 1 and 5000;

-
Update a further 10000 rows.

update dbo.Numbers
set Number = Number
where Number between 5001 and 15000;

-
Stop the acquire trace.

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = stop;

-
Verify there are locks by querying sys.dm_tran_locks (2 - DB, table).

-
Create and start the Release trace.

CREATE EVENT SESSION DuringRollback
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'c:\temp\LockRollback\DuringRollback.xel', 
         metadatafile = 'c:\temp\LockRollback\DuringRollback.xem')
WITH(EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
    );

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = start;

-
Roll back the transaction.

rollback;

-
Stop the Release trace.

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = stop;

-
Tidy up

DROP EVENT SESSION DuringUpdate
ON SERVER;

DROP EVENT SESSION DuringRollback
ON SERVER;

If lock de-escalation occurred I would expect complete symmetry in the two trace files - every lock acquired during UPDATE, and the escalation, would have a corresponding release during ROLLBACK. What I observed:

SELECT 
    COUNT(*) as DuringUpdate
FROM sys.fn_xe_file_target_read_file(
    'c:\temp\LockRollback\DuringUpdate*.xel', 
    'c:\temp\LockRollback\DuringUpdate*.xem', null, null);

SELECT 
    COUNT(*) as DuringRollback
FROM sys.fn_xe_file_target_read_file(
    'c:\temp\LockRollback\DuringRollback*.xel', 
    'c:\temp\LockRollback\DuringRollback*.xem', null, null);

was 383,889 rows and 166 rows respectively. Examining the event XML for the rollback file there is only one event relating to RIDs, PAGEs or OBJECTS - the release of an X lock on the table. I conclude from this that locks were not de-escalated during rollback.

This echos the documentation for rolling back to a named savepoint:


A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of escalations and conversions. These locks are not released, and they are not converted back to their previous lock mode.

StackExchange Database Administrators Q#137728, answer score: 11