动态

详情 返回 返回

SQL Server 2025中解決“寫寫阻塞”的利器 - 动态 详情

SQL Server 2025中解決“寫寫阻塞”的利器

 

最近安裝了SQL Server 2025的CTP 2.0 版本，做了一些測試

QQ截圖20250816165828

SQLServer 2025安裝包獲取

通過網盤分享的文件：SQLServer 2025-x64-ENU.iso

鏈接: https://pan.baidu.com/s/1vs4QIwynJqzGlh0uVtBZWw?pwd=uyzn 

提取碼: uyzn

背景

在當今的應用場景中，可擴展性和併發性已成為核心需求，如何在保持數據一致性的同時提供高性能是一項複雜的挑戰。SQL Server 2025 對數據庫引擎進行了多項增強，以提升性能和可擴展性。

“Optimized Locking”—— 這個擁有奇妙名稱的功能改變了 DML 語句獲取鎖的方式。該功能最初在 雲上Azure SQL 中推出，如今已在 本地SQL Server 2025 中可用。

 

數據庫引擎增強

SQL Server 2025 版本體現了對數據庫引擎的重大投入。

QQ截圖20250816165902

  • 在 Tempdb 系統數據庫上默認啓用加速數據庫恢復（ADR）功能。涉及臨時表、表變量或在 Tempdb 中創建的非臨時表的事務可能會受到回滾和過度事務日誌使用的負面影響。在 Tempdb 中啓用 ADR 後，即使是影響 tempdb 對象的事務，也能實現事務日誌的即時截斷。

  • 為 Linux 上的 SQL Server 在 tmpfs文件系統上啓用Tempdb。

  • 強化了 “可讀輔助副本持久化統計信息” 功能，該功能最初在 SQL Server 2022 中引入，但默認處於禁用狀態，需要啓用跟蹤標誌 12606 才能使用。在 SQL Server2025 中，可讀副本的查詢存儲默認啓用。

優化鎖定

優化鎖定是 SQL Server 2025 引擎中的一項新功能。它旨在減少鎖管理所用的內存、減少鎖升級的發生，並提高數據庫的併發性。

在深入瞭解優化鎖定的工作原理之前，有必要先了解併發模型和鎖定機制。

併發模型和鎖模式

鎖定是一種防止多個事務同時修改相同數據的機制，從而確保事務符合 ACID 屬性。

當事務需要修改數據時，引擎會請求對目標數據加鎖。只有當同一數據集上沒有不兼容的鎖時，該鎖才能被授予，允許事務繼續執行更新。

如果已存在其他鎖，事務必須等待該鎖釋放後才能繼續。鎖定是數據庫引擎根據所選隔離級別強制執行事務隔離的機制之一。 SQL Server 數據庫引擎支持兩種併發模型：

  • 悲觀併發模型
  • 樂觀併發模型

在悲觀模型中，讀事務獲取的鎖會阻塞嘗試獲取寫鎖的事務，反之亦然。在樂觀模型中，讀事務獲取的鎖不會阻塞寫鎖，但寫鎖之間仍然不兼容。

每個事務都指定一個隔離級別，該級別決定了它與其他事務的隔離程度。

在SQL Server裏，對於悲觀併發模型，有四個隔離級別：

  • 未提交讀
  • 已提交讀
  • 可重複讀
  • 序列化

對於樂觀併發模型，有兩個隔離級別：

  • 快照
  • 已提交讀快照

並非所有人都知道，已提交讀快照並非一個獨立的隔離級別，而是當啓用 READ_COMMITTED_SNAPSHOT 選項時，對已提交讀隔離級別的行為修改。

根據所選的隔離級別，數據庫引擎會使用不同的鎖模式鎖定資源，這些鎖模式決定了併發事務對這些資源的訪問方式（或限制）。

背後的技術

優化鎖定依賴於兩項早已集成到數據庫引擎中的技術。

  1. 第一項是SQL Server 2019推出的加速數據庫恢復（ADR），它是啓用優化鎖定的必要前提。加速數據庫恢復必須在數據庫級別啓用，它通過加快事務回滾操作和更高效地處理未提交的更改來改進事務恢復。

  2. 第二項技術是已提交讀快照隔離（RCSI）。雖然並非嚴格要求，但啓用 RCSI 可以充分發揮優化鎖定的優勢。

以下 T-SQL 代碼開啓加速數據庫恢復、已提交讀快照和優化鎖定功能，並且檢查當前的開啓狀態。

use master
go

-- 加速數據庫恢復（ADR）
ALTER DATABASE test SET ACCELERATED_DATABASE_RECOVERY = ON;

--已提交讀快照隔離級別（RCSI）以獲得最大收益
ALTER DATABASE test  SET READ_COMMITTED_SNAPSHOT ON;  --不是必須

--開啓優化鎖定
ALTER DATABASE test SET OPTIMIZED_LOCKING = ON;



--檢查開啓狀態
SELECT
  IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
  ,RCSI = is_read_committed_snapshot_on
  ,ADR  = is_accelerated_database_recovery_on
 FROM
   sys.databases
 WHERE
   (name = DB_NAME());

優化鎖定基於兩項關鍵機制：

  • 1、事務 ID（TID）鎖   （Transaction ID (TID) locking）

  • 2、驗證後鎖定（LAQ） （Lock After Qualification (LAQ)）

事務 ID（TID）鎖

相較於之前的行鎖（key或者rid）、頁鎖、表鎖，新增了一種鎖資源類型XACT，解決鎖升級之後的更嚴重的阻塞問題。 當使用已提交讀快照隔離級別和啓用加速數據庫恢復（ADR）後，數據庫中的每行數據內部都包含一個事務 ID（TID）。當事務修改某行數據時，會將自身的TID分配給該行。

 

在 TID 鎖定機制中，鎖並非獲取在行的key或者rid上，而是獲取在行的 TID 上。修改數據行的事務會對自身的 TID 持有排他（X）鎖。其他需要修改同一行數據的事務會對該 TID 獲取共享（S）鎖，以等待第一個事務（update該行數據的事務）完成。

通過 TID 鎖，第一個事務修改過程中仍會獲取頁鎖和行鎖，但這些頁鎖和行鎖在數據行更改完成後會立即釋放，即使事務尚未提交或回滾。事務結束前唯一持有的鎖是對 TID 資源的單個排他（X）鎖，它有效地替代了多個頁鎖和行鎖（key或者rid）。

在啓用了優化鎖定的 SQL Server 2025 數據庫中執行。

示例表 dbo.SensorReadings 包含兩列：

SensorID（整數類型，為主鍵）

ReadingValue（整數類型，用於存儲示例值）。

use test
go

DROP TABLE IF EXISTS dbo.SensorReadings;

CREATE TABLE dbo.SensorReadings
(
  SensorID INTEGER PRIMARY KEY NOT NULL,
  ReadingValue INTEGER NOT NULL
);

INSERT INTO dbo.SensorReadings VALUES (1, 10), (2, 20), (3, 30);
GO

 

我們將更新 ReadingValue 中存儲的值，同時使用動態管理視圖 sys.dm_tran_locks 監控獲取的鎖。

use test
go

-- 通過 sys.dm_tran_locks 檢查更新行上的鎖
BEGIN TRANSACTION;

UPDATE
  dbo.SensorReadings
SET
  ReadingValue = ReadingValue + 10;

SELECT
  *
FROM
  sys.dm_tran_locks
WHERE
  request_session_id = @@SPID
AND
  resource_type IN ('PAGE', 'RID', 'KEY', 'XACT');

COMMIT TRANSACTION;
GO

觀察到的唯一鎖是在 TID 上獲取的鎖，並且只有一個TID鎖，如下方圖片所示。

640 (1)  

而在禁用了優化鎖定的數據庫中對同一表執行相同操作時，會顯示符合條件的3個數據行上的排他（X）鎖以及包含這些數據行的數據頁上的意向排他（IX）鎖。這些鎖會保持到事務完成，如下方圖片所示。

640 (2)

640 (3)

可以看到，明明要申請三行數據上的三個排它鎖，現在只申請了一個事務鎖，大大降低了鎖升級導致的阻塞問題的發生。

 

驗證後鎖定（LAQ）-》需要打開（讀提交快照隔離級別）

DML 操作變慢的主要原因之一，是在搜索滿足查詢條件的數據行過程中獲取鎖。SQL Server 2025 中的 “驗證後鎖定” 改變了 DML 語句（UPDATE、DELETE、MERGE）獲取鎖的方式。

當未啓用優化鎖定（Optimized Locking ）時，查詢會在掃描過程中逐行評估謂詞。首先會對每行獲取更新（U）鎖，若謂詞條件滿足，則在更新行之前立即將其轉換為排他（X）鎖。排他（X）鎖會保持到事務完成。

當啓用優化鎖定（Optimized Locking ）時，會在不獲取任何鎖的情況下，基於行的最新已提交版本評估謂詞。若謂詞條件不滿足，掃描會繼續處理下一行；若謂詞條件滿足，則會獲取排他（X）鎖來執行更新。此鎖會在行更新完成後、事務結束前立即釋放。

由於謂詞評估過程無需加鎖，修改不同的數據行的併發查詢不會相互阻塞。

在啓用了優化鎖定（Optimized Locking ）的 SQL Server 2025 數據庫中執行下面語句



use test
go

DROP TABLE IF EXISTS dbo.EntityCounters;

CREATE TABLE dbo.EntityCounters
(
  EntityID INTEGER NOT NULL,
  CounterValue INTEGER NULL
);

INSERT INTO dbo.EntityCounters VALUES (1, 10), (2, 20), (3, 30);
GO

我們假設有兩個併發查詢，在兩個不同的會話中按照順序執行。 先執行會話一，再執行會話二，最後執行會話三





--會話一
use test
go

select @@SPID

BEGIN TRANSACTION;
UPDATE
dbo.EntityCounters
SET
CounterValue = CounterValue + 10
WHERE
EntityID = 2;






--會話二
--如果沒有開啓讀提交快照RCSI隔離級別，那麼這個語句會被阻塞，

use test
go


select @@SPID


BEGIN TRANSACTION;
UPDATE
dbo.EntityCounters
SET
CounterValue = CounterValue + 10
WHERE
EntityID = 1; 






--會話三
use test
go


SELECT
resource_type
,resource_database_id
,resource_description
,request_mode
,request_type
,request_status
,request_session_id
,resource_associated_entity_id
FROM
sys.dm_tran_locks
WHERE
request_session_id IN
(72, 73)  --代入實際的會話ID
AND resource_type IN
('PAGE', 'RID', 'KEY', 'XACT')
order by request_session_id;
  • 開啓了優化鎖定的情況

兩個會話各自申請了一個事務鎖，而且互相不會阻塞

640 (4)

 

  • 沒有開啓優化鎖定的情況

如果在沒有開啓優化鎖定（Optimized Locking ）的數據庫上執行，會發現會話二正在等待會話一在EntityID = 1; 這行數據上的U鎖，導致被阻塞。

當 UPDATE 查詢掃描表時，會首先對每個檢查的行獲取更新（U）鎖，以確保沒有其他事務能同時修改該行。 若 WHERE 子句中的謂詞條件滿足，更新（U）鎖會轉換為排他（X）鎖並更新行；若謂詞條件不滿足，更新（U）鎖會被釋放且不修改行，更新（U）鎖會一直保持到事務結束。

另一個查詢（會話 2）嘗試更新 跟（會話 1）非同一行數據，即使第一個查詢（會話 1）最終並未修改受更新（U）鎖影響的行（EntityID = 1這行數據），（會話 2） 也必須等待會話 1 釋放更新（U）鎖。

640 (5)

這時候只能讀取EntityID = 1這行數據，但是不能對這行數據做更新。

關於更新（U）鎖和共享（S）鎖兼容如下圖所示：

640 (6)

總結

640 (7)  

SQL Server 2025 和 Azure SQL Database 中的優化鎖技術，是併發管理領域的一項重大進步。 藉助事務 ID（TID）鎖和驗證後鎖定（LAQ）機制減少了鎖管理所需的內存消耗，並通過降低併發事務之間的鎖競爭，最大限度地減少了鎖升級情況的發生。


 

本文版權歸作者所有，未經作者同意不得轉載。

 

sqlserver
user avatar xiaoliepower 头像 cqai 头像 1810739137qq 头像 developer-tianyiyun 头像 chunzhendegaoshan 头像
点赞 5 用户, 点赞了这篇动态!
点赞

Add a new 评论

Some HTML is okay.