最佳化鎖定
本文介紹最佳化鎖定功能，這是 SQL Server 資料庫引擎的新功能，提供改良的交易鎖定機制，可減少鎖定記憶體耗用量和封鎖並行交易。
什麼是最佳化鎖定？
最佳化鎖定有助於減少鎖定記憶體，因為大型交易會佔用非常少的鎖定。 此外，最佳化鎖定也可防止鎖定擴大。 如此一來可對資料表進行更多並行存取。
最佳化鎖定由兩個主要元件組成：交易識別碼 (TID) 鎖定和限定性條件鎖定 (LAQ)。
- 交易識別碼 (TID) 是交易的唯一識別碼。 每個資料列都會加上標籤，標註上次進行修改的 TID。 此處不可能有許多金鑰或資料列識別碼鎖定，而是使用 TID 上的單一鎖定。 如需詳細資訊，請參閱交易識別碼 (TID) 鎖定一節。
- 限定性條件鎖定 (LAQ) 是一項最佳化功能，可評估對最新認可資料列版本查詢的述詞，而不需要取得鎖定，藉此改善並行。 如需詳細資訊，請參閱限定性條件鎖定 (LAQ) 一節。
例如：
- 若沒有最佳化鎖定，更新資料表中的 1 百萬個資料列時，可能需要 1 百萬個獨佔 (X) 資料列鎖定，直到交易結束為止。
- 透過最佳化鎖定，更新資料表中的 1 百萬個資料列時，可能需要 1 百萬個 X 資料列鎖定，但每個鎖定在每次更新每個資料列時都會釋出，而且交易結束前只會佔用一個 TID 鎖定。
本文詳細說明最佳化鎖定的這兩個核心概念。
可用性
目前，最佳化鎖定僅適用於 Azure SQL 資料庫。 如需詳細資訊，請參閱最佳化鎖定目前的適用範圍為何？
最佳化鎖定是否已啟用？
系統會針對每個使用者資料庫啟用最佳化鎖定。 連線您的資料庫，然後使用下列查詢來檢查資料庫是否已啟用最佳化鎖定：
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');
如果沒有連線到 DATABASEPROPERTYEX 中指定的資料庫，結果會為 NULL。 您應該會收到 0 (最佳化鎖定已停用) 或 1 (已啟用)。
最佳化鎖定的基礎是其他資料庫功能：
- 最佳化鎖定需要在資料庫上啟用加速資料庫復原 (ADR)。
- 為了充分運用最佳化鎖定的優點，應該為資料庫啟用讀取認可快照隔離 (RCSI)。
預設情況下，Azure SQL 資料庫會啟用 ADR 和 RCSI。 若要確認目前資料庫已啟用這些選項，請使用下列 T-SQL 查詢：
SELECT name
, is_read_committed_snapshot_on
, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = db_name();
鎖定概觀
這是未啟用最佳化鎖定時的行為簡短摘要。 如需詳細資訊，請參閱交易鎖定與資料列版本設定指南。
在資料庫引擎中，鎖定機制可防止多個交易同時更新相同的資料，以確保資料完整性和一致性。
當交易需要修改資料時，可以要求鎖定資料。 資料如果沒有佔用其他衝突的鎖定，則會授與鎖定，且交易可以繼續進行修改。 如果資料有佔用另一個衝突的鎖定，則交易必須等待鎖定釋出才能繼續。
允許多個交易同時存取相同的資料時，資料庫引擎必須解決與並行讀取和寫入的潛在複雜衝突。 資料庫引擎可以透過鎖定機制提供 ANSI SQL 交易隔離等級語意。 雖然資料庫中的鎖定很重要，但減少並行、鎖死、複雜度和鎖定額外負荷會影響效能和可擴縮性。
最佳化鎖定和交易識別碼 (TID) 鎖定
使用資料列版本設定時，資料庫引擎中的每個資料列內部都會包含交易識別碼 (TID)。 此 TID 會保存在磁碟上。 修改資料列的每個交易都會以其 TID 為資料列加上戳記。
使用 TID 鎖定時，不會在資料列的索引鍵上佔用鎖定，而是會在資料列的 TID 上佔用鎖定。 修改交易時會在其 TID 上佔用 X 鎖定。 其他交易會取得 TID 的 S 鎖定，以檢查第一筆交易是否仍在使用中。 使用 TID 鎖定時，會繼續佔用頁面和資料列鎖定以進行更新，但每次更新每個資料列後，就會立即釋出每個頁面和資料列鎖定。 交易結束時佔用的唯一鎖定是 TID 資源的 X 鎖定，取代頁面和資料列 (索引鍵) 鎖定，如下一個示範所示。 (最佳化鎖定不會影響其他標準資料庫和物件鎖定。)
最佳化鎖定有助於減少鎖定記憶體，因為大型交易會佔用非常少的鎖定。 此外，最佳化鎖定也可防止鎖定擴大。 如此一來可讓其他並行交易存取資料表。
請考慮下列尋找使用者目前工作階段鎖定的 T-SQL 範例案例：
CREATE TABLE t0
(a int PRIMARY KEY not null
,b int null);
INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO
BEGIN TRAN
UPDATE t0
SET b=b+10;
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_type in ('PAGE','RID','KEY','XACT');
COMMIT TRAN
GO
DROP TABLE IF EXISTS t0;
沒有利用最佳化鎖定優點的相同查詢，會建立四個鎖定：
sys.dm_tran_locks 動態管理檢視 (DMV) 有助於檢查或疑難排解鎖定問題，包括觀察作用中的最佳化鎖定。
最佳化鎖定和限定性條件鎖定 (LAQ)
最佳化鎖定是在 TID 基礎結構上建置，可變更查詢述詞保護鎖定的方式。
如果沒有使用最佳化鎖定，系統會先佔用更新 (U) 資料列鎖定，在掃描中逐列檢查查詢的述詞。 如果符合述詞，系統會在更新資料列之前佔用 X 資料列鎖定。
若使用最佳化鎖定且啟用讀取認可快照集隔離等級 (RCSI)，會在最新的認可版本上套用述詞，而不需要佔用任何資料列鎖定。 如果不符合述詞，查詢會移至掃描中的下一個資料列。 如果符合述詞，則會佔用 X 資料列鎖定來實際更新資料列。 交易結束之前，資料列更新完成時會立即釋出 X 資料列鎖定。
由於系統不須取得任何鎖定就可執行述詞評估，因此修改不同資料列的並行查詢不會彼此封鎖。
範例：
CREATE TABLE t1
(a int not null
,b int null);
INSERT INTO t1 VALUES (1,10),(2,20),(3,30);
GO
| 工作階段 1 | 工作階段 2 |
|---|---|
BEGIN TRANUPDATE t1SET b=b+10WHERE a=1; |
|
BEGIN TRANUPDATE t1SET b=b+10WHERE a=2; |
|
COMMIT TRAN |
|
COMMIT TRAN |
上一個範例中，使用最佳化鎖定來封鎖變更的行為。 若沒有使用最佳化鎖定，工作階段 2 會遭到封鎖。
不過若使用最佳化鎖定，工作階段 2 不會遭到封鎖，因為資料列 1 的最新認可版本包含 a=1，這不符合工作階段 2 的述詞。
如果符合述詞，便須等待資料列上的所有作用中交易完成。 如果必須等待 S TID 鎖定，表示資料列可能已變更，且最新的認可版本也可能已變更。 在此情況下，資料庫引擎不會因為更新衝突而中止交易，而是會在相同資料列上重試述詞評估。 如果重試時述詞符合資格，便會更新資料列。
請思考下列自動重試述詞變更的範例：
CREATE TABLE t2
(a int not null
,b int null);
INSERT INTO t2 VALUES (1,10),(2,20),(3,30);
GO
| 工作階段 1 | 工作階段 2 |
|---|---|
BEGIN TRANUPDATE t2SET b=b+10WHERE a=1; |
|
BEGIN TRANUPDATE t2SET b=b+10WHERE a=1; |
|
COMMIT TRAN |
|
COMMIT TRAN |
使用最佳化鎖定和 RCSI 變更查詢行為
啟用最佳化鎖定時，讀取認可快照隔離等級 (RCSI) 下的並行系統，其工作負載會依賴交易的執行順序，因此可能會遇到不同的查詢行為。
請考慮下列範例，其中交易 T2 會根據交易 T1 期間更新的資料行 b 來更新資料表 t1。
CREATE TABLE t1 (a int not null, b int null);
INSERT INTO t1 VALUES (1,1);
GO
| 工作階段 1 | 工作階段 2 |
|---|---|
| BEGIN TRAN T1 UPDATE t1 SET b=2 WHERE a=1; |
|
| BEGIN TRAN T2 UPDATE t1 SET b=3 WHERE b=2; |
|
| COMMIT TRAN | |
| COMMIT TRAN |
讓我們評估上述案例的結果，即使用和不使用限定性條件鎖定 (LAQ) 的情況，這是最佳化鎖定不可或缺的一部分。
不使用 LAQ
若不使用 LAQ，那麼交易 T2 將會遭到封鎖，且需等待交易 T1 完成。
在兩個交易提交之後，資料表 t1 將會包含以下資料列：
a | b
1 | 3
使用 LAQ
若使用 LAQ，那麼交易 T2 將會使用資料列 b 的最新提交版本 (在版本存放區中，b=1) 評估其述詞 (b=2)。 此資料列不符合資格；因此會略過，T2 會移至下一個資料列，而不會被交易 T1 封鎖。 在此範例中，LAQ 會移除封鎖，但會導致不同的結果。
在兩個交易提交之後，資料表 t1 將會包含以下資料列：
a | b
1 | 2
重要
即使未使用 LAQ，應用程式也不應該假設 SQL Server (根據版本設定的隔離等級) 會保證嚴格排序，而不使用鎖定提示。 對於使用 RCSI 下並行系統的客戶，其工作負載依賴嚴格交易執行順序 如同先前的練習所示)，我們的一般建議使用是使用更嚴格的隔離等級。
最佳化鎖定的診斷新增項目
若要支援使用最佳化鎖定進行封鎖和鎖死的監視和疑難排解，請尋找下列新增項目：
- 最佳化鎖定的等候類型
- sys.dm_os_wait_stats (Transact-SQL) 中的
XACT等候類型和資源描述：LCK_M_S_XACT_READ- 當工作正在等候XACTwait_resource類型的共用鎖定時發生，且具有讀取意圖。LCK_M_S_XACT_MODIFY- 當工作正在等候XACTwait_resource類型的共用鎖定時發生，且具有修改意圖。LCK_M_S_XACT- 當工作正在等候XACTwait_resource類型的共用鎖定時發生，且具有無法推斷的意圖。 罕見。
- sys.dm_os_wait_stats (Transact-SQL) 中的
- 鎖定資源可見度
XACT鎖定資源。 如需詳細資訊，請參閱 sys.dm_tran_locks (Transact-SQL) 中的resource_description。
- 等候資源可見度
XACT等候資源。 如需詳細資訊，請參閱 sys.dm_exec_requests (Transact-SQL) 中的wait_resource。
- 鎖死圖形
- 在鎖死報表
<resource-list>中的每個資源下，每個<xactlock>元素報告鎖死中每個成員鎖定的基礎資源和特定資訊。 如需詳細資訊和範例，請參閱最佳化鎖定和鎖死。
- 在鎖死報表
最佳化鎖定的最佳做法
啟用讀取認可快照隔離 (RCSI)
若要充分發揮最佳化鎖定的優點，建議您為資料庫啟用讀取認可快照隔離 (RCSI)，並使用讀取認可隔離作為預設隔離等級。 如果尚未啟用，請使用下列範例啟用 RCSI：
ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;
在 Azure SQL Database 中，RCSI 預設為啟用，且讀取認可為預設隔離等級。 啟用 RCSI 並使用讀取認可隔離等級時，讀取器不會封鎖寫入器，且寫入器不會封鎖讀取器。 讀取器會從查詢開始時建立的快照集讀取資料列版本。 使用 LAQ 時，寫入器會根據資料列的最新認可版本來依照述詞限定資料列資格，而不需要取得 U 鎖定。 使用 LAQ 時，只有在資料列符合資格，且該資料列上有作用中的寫入交易時，查詢才會等待。 根據最新的認可版本來限定資格，並只鎖定符合條件的資料列，可減少封鎖並增加並行。
除了減少封鎖之外，所需的鎖定記憶體也會減少。 這是因為讀取器不會佔用任何鎖定，而且寫入器只需要短暫的持續時間鎖定，而不是交易結束時才到期的鎖定。 若使用更嚴格的隔離等級 (例如可重複讀取或可序列化)，資料庫引擎會針對讀取器和寫入器強制佔用資料列和頁面鎖定，直到交易結束為止，這會造成封鎖和鎖定記憶體增加。
防止鎖定提示
雖然資料表和查詢提示有其功用，但是會減弱最佳化鎖定的優點。 查詢中的鎖定提示 (例如 UPDLOCK、READCOMMITTEDLOCK、XLOCK、HOLDLOCK 等) 可能會減弱最佳化鎖定的完整優點。 查詢中若有這類鎖定提示，會強制資料庫引擎取得資料列/頁面鎖定，並佔用到交易結束為止，以遵循鎖定提示的意圖。 某些應用程式具有需要鎖定提示的邏輯，例如使用 UPDLOCK 讀取具有 SELECT 的資料列，之後再進行更新。 建議您只在需要時使用鎖定提示。
使用最佳化鎖定時，現有的查詢沒有任何限制，且不需要重寫查詢。 不使用提示的查詢可充分發揮最佳化鎖定的優點。
查詢中某個資料表的資料表提示，不會停用相同查詢中其他資料表的最佳化鎖定。 此外，最佳化鎖定只會影響 UPDATE 陳述式所更新資料表的鎖定行為。 例如：
CREATE TABLE t3
(a int not null
, b int not null);
CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO
UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;
在上一個查詢範例中，只有資料表 t4 會受到鎖定提示的影響，而 t3 仍可受益於最佳化鎖定。
UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;
在上一個查詢範例中，只有資料表 t3 會使用可重複讀取隔離等級，並佔用鎖定直到交易結束為止。 t3 的其他更新仍可受益於最佳化鎖定。 同樣情況也適用於 HOLDLOCK 提示。
常見問題集 (FAQ)
最佳化鎖定目前的適用範圍為何？
目前，最佳化鎖定適用於 Azure SQL 資料庫。
最佳化鎖定可在下列服務層級使用：
- 所有 DTU 服務層級
- 所有虛擬核心服務層級，包括佈建和無伺服器
最佳化鎖定目前無法用於：
- Azure SQL 受控執行個體
- SQL Server 2022 (16.x)
新的和現有的資料庫是否預設啟用最佳化鎖定？
在 Azure SQL 資料庫中，為是。
如何偵測是否已啟用最佳化鎖定？
請參閱最佳化鎖定是否已啟用？
我的資料庫沒有啟用加速資料庫復原 (ADR) 會怎麼樣？
如果停用 ADR，系統也會自動停用最佳化鎖定。
我想要強制查詢無視最佳化鎖定並進行封鎖，該怎麼做？
使用 RCSI 的客戶若要在啟用最佳化鎖定的情況下，強制在兩個查詢之間進行封鎖，請使用 READCOMMITTEDLOCK 查詢提示。
相關內容
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應