在回滾期間，鎖定升級是否反轉

通常，SQL Server 會在更新期間鎖定。它還支持鎖升級：

鎖升級是將許多細粒度鎖轉換為較少粗粒度鎖的過程，減少系統成本，同時增加並發爭用的機率。

在事務回滾期間，SQL Server 是否會降級鎖，放回細粒度鎖？

SQL Server 不會解除鎖定。

我使用具有 100,000 行的“數字”表進行了調查。根據經驗，更新 5,000 行會在sys.dm_tran_locks. 再更新 10,000 行導致升級為單個表鎖。這是始終可重複的。為了最小化所涉及的對象，該表是一個沒有索引的堆。

我使用擴展事件跟踪來擷取lock_acquired事件lock_released。為了便於分析，我在 UPDATE 和 ROLLBACK 階段使用了單獨的跟踪。

使用了兩個會話（SSMS 視窗）——一個用於 DML 語句，一個用於跟踪 DDL。我不能使用單個會話，因為我想在事務打開時停止和啟動跟踪，這是不允許的。整個隔離級別是 READCOMITTED。

結果select @@VERSION是

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

採取的步驟是

1. 創建並啟動 Acquire 跟踪。

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;

2. 開始一個事務並更新 5000 行。

begin transaction;

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

3. 再更新 10000 行。

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

4. 停止獲取跟踪。

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = stop;

5. sys.dm_tran_locks通過查詢（2 - DB，表）驗證是否有鎖。
6. 創建並啟動發布跟踪。

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;

7. 回滾事務。

rollback;

8. 停止發布跟踪。

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = stop;

9. 整理

DROP EVENT SESSION DuringUpdate
ON SERVER;

DROP EVENT SESSION DuringRollback
ON SERVER;

如果發生鎖降級，我希望兩個跟踪文件完全對稱 - 在 UPDATE 和升級期間獲取的每個鎖都會在 ROLLBACK 期間有相應的釋放。我觀察到的：

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

分別為 383,889 行和 166 行。檢查回滾文件的事件 XML，只有一個與 RID、PAGE 或 OBJECTS 相關的事件 - 釋放表上的 X 鎖。我由此得出結論，在回滾期間鎖沒有降級。

這與回滾到命名保存點的文件相呼應：

指定 savepoint_name 的 ROLLBACK TRANSACTION 語句會釋放在保存點之外獲取的所有鎖，但升級和轉換除外。這些鎖不會被釋放，也不會轉換回之前的鎖模式。

引用自：https://dba.stackexchange.com/questions/137728