最佳化鎖定

文章
10/16/2024

適用於：Azure SQL 資料庫

本文介紹最佳化鎖定功能，這是資料庫引擎的新功能，提供改良的交易鎖定機制，可減少鎖定記憶體耗用量和封鎖並行交易。

什麼是最佳化鎖定？

最佳化鎖定有助於減少鎖定記憶體，因為連大型交易也會佔用非常少的鎖定。此外，最佳化鎖定也可防止鎖定擴大。如此一來可對資料表進行更多並行存取。

最佳化鎖定由兩個主要元件組成：交易識別碼 (TID) 鎖定和限定性條件鎖定 (LAQ)。

交易識別碼 (TID) 是交易的唯一識別碼。每個資料列都會加上標籤，標註上次進行修改的 TID。此處不可能有許多金鑰或資料列識別碼鎖定，而是使用 TID 上的單一鎖定。如需詳細資訊，請參閱交易識別碼 (TID) 鎖定。
限定性條件鎖定 (LAQ) 是一項最佳化功能，可評估對最新認可資料列版本查詢的述詞，而不需要取得鎖定，藉此改善並行。如需詳細資訊，請參閱限定性條件鎖定 (LAQ)。

例如：

若沒有最佳化鎖定，更新資料表中的一千個資料列時，可能需要一千個獨佔 (X) 資料列鎖定，直到交易結束為止。
透過最佳化鎖定，更新資料表中的一千個資料列時，可能需要一千個 X 資料列鎖定，但每個鎖定在每次更新每個資料列時都會釋出，而且交易結束前只會佔用一個 TID 鎖定。因為鎖定會快速釋出，因此鎖定記憶體使用量會降低，而且鎖定擴大的可能性要低得多，進而改善工作負載並行。

注意

啟用最佳化鎖定可減少或消除資料操作語言 (DML) 陳述式取得的資料列和頁面鎖定，例如 INSERT、UPDATE、DELETE、MERGE。此動作不會影響其他種類的資料庫和物件鎖定，例如結構描述鎖定。

可用性

最佳化鎖定僅供 Azure SQL 資料庫使用，適用於所有服務層級和計算大小。

最佳化鎖定目前無法在 Azure SQL 受控執行個體或 SQL Server 中使用。

最佳化鎖定是否已啟用？

系統會針對每個使用者資料庫啟用最佳化鎖定。連線您的資料庫，然後使用下列查詢來檢查是否已啟用最佳化鎖定：

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

結果	描述
`0`	最佳化鎖定已停用。
`1`	最佳化鎖定已啟用。
`NULL`	最佳化鎖定無法使用。

最佳化鎖定的基礎是其他資料庫功能：

最佳化鎖定需要在資料庫上啟用加速資料庫復原 (ADR)。
為了充分運用最佳化鎖定的優點，應該為資料庫啟用讀取認可快照隔離 (RCSI)。只有在啟用 RCSI 時，最佳化鎖定的 LAQ 元件才會生效。

預設情況下，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();

鎖定概觀

這是未啟用最佳化鎖定時的行為簡短摘要。如需詳細資訊，請參閱交易鎖定與資料列版本設定指南。

在資料庫引擎中，鎖定機制可防止多個交易同時更新相同的資料，以保證交易的 ACID 屬性。

當交易需要修改資料時，可以要求鎖定資料。資料如果沒有佔用其他衝突的鎖定，則會授與鎖定，且交易可以繼續進行修改。如果資料有佔用另一個衝突的鎖定，則交易必須等待鎖定釋出才能繼續。

允許多個交易同時存取相同的資料時，資料庫引擎必須解決與並行讀取和寫入的潛在複雜衝突。該引擎可以透過鎖定機制提供 ANSI SQL 交易隔離等級語意。雖然資料庫中的鎖定很重要，但減少並行、鎖死、複雜度和鎖定額外負荷會影響效能和可擴縮性。

最佳化鎖定和交易識別碼 (TID) 鎖定

當使用資料列版本設定型的隔離等級或啟用 ADR 時，資料庫內部的每一列都包含一個交易 ID (TID)。此 TID 會保存在磁碟上。修改資料列的每個交易都會以其 TID 為資料列加上戳記。

使用 TID 鎖定時，不會在資料列的索引鍵上佔用鎖定，而是會在資料列的 TID 上佔用鎖定。修改交易時會在其 TID 上佔用 X 鎖定。其他交易會取得 S TID 的鎖定，以等到第一筆交易完成為止。使用 TID 鎖定時，會繼續佔用頁面和資料列鎖定以進行修改，但每次修改每個資料列後，就會立即釋出每個頁面和資料列鎖定。交易結束時佔用的唯一鎖定是 TID 資源的單一 X 鎖定，取代多個頁面和資料列 (索引鍵) 鎖定。

請考慮下列範例，在寫入交易為使用中時顯示目前工作階段的鎖定：

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

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 TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

如果啟用最佳化鎖定，則要求只持有 XACT (交易) 資源上的單一 X 鎖定。

如果未啟用最佳化鎖定，則相同的要求會持有四個鎖 - 每一列上有三個 X 索引鍵鎖定，包含這些資料列的頁面上有一個 IX (意圖獨佔) 鎖定：

sys.dm_tran_locks 動態管理檢視 (DMV) 有助於檢查或疑難排解鎖定問題，如觀察作用中的最佳化鎖定。

最佳化鎖定和限定性條件鎖定 (LAQ)

在 TID 基礎結構上組建，最佳化鎖定會變更 DML 陳述式，例如 INSERT、UPDATE、DELETE 和 MERGE 取得鎖定的方式。

如果沒有使用最佳化鎖定，系統會先佔用更新 (U) 資料列鎖定，在掃描中逐列檢查查詢的述詞。如果符合述詞，則在更新該資料列之前，會取得排除 (X) 資料列鎖定，並維持到交易結束。

若使用最佳化鎖定且啟用 READ COMMITTED 快照隔離等級 (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 TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

如果沒有最佳化鎖定，工作階段 2 就會封鎖，因為工作階段 1 持有工作階段 2 需要更新的資料列的 U 鎖定。然而，在最佳化鎖定的情況下，工作階段 2 並未封鎖，因為您未使用 U 鎖定，而且在資料列 1 的最新認可的版本中，資料欄 a 等於 1，這並不滿足工作階段 2 的述詞。

由於未採用 LAQ U 鎖定，因此在評估述詞之後，並行交易可能會修改資料列。如果滿足述詞，而且資料列上沒有其他使用中的交易 (沒有 X TID 鎖定)，則會修改資料列。如果有使用中的交易，資料庫引擎會等待它完成，並在修改時再次重新評估述詞，因為其他交易可能已經修改了資料列。如果仍然滿足述詞，則會修改資料列。

考慮下列的範例，由於另一個交易改變了資料列，因此述詞評估會自動重試：

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

工作階段 1	工作階段 2
`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

使用最佳化鎖定和 RCSI 變更查詢行為

啟用最佳化鎖定時，讀取認可快照隔離 (RCSI) 下的並行工作負載，會依賴交易的執行順序，因此可能會遇到不同的查詢行為。

請考慮下列範例，其中交易 T2 會根據交易 T1 期間更新的資料行 b 來更新資料表 t4。

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

工作階段 1	工作階段 2
`BEGIN TRANSACTION T1;` `UPDATE t4` `SET b = 2` `WHERE a = 1;`
	`BEGIN TRANSACTION T2;` `UPDATE t4` `SET b = 3` `WHERE b = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

讓我們評估上述案例的結果，即使用和不使用限定性條件鎖定 (LAQ) 的情況。

不使用 LAQ

如果沒有 LAQ，交易 T2 中的 UPDATE 陳述式會遭封鎖，等待交易 T1 完成。 T1 完成後，T2 會將資料列 b 更新為 3，因為已滿足其述詞。

在兩個交易提交之後，資料表 t4 將會包含以下資料列：

 a | b
 1 | 3

使用 LAQ

使用 LAQ 時，交易 T2 使用列 b 等於 1 的資料列的最新認可的版本來評估它的述詞 (b = 2)。該資料列不符合條件，因此會略過，而陳述式在沒有遭交易 T1 封鎖的情況下完成。在此範例中，LAQ 會移除封鎖，但會導致不同的結果。

在兩個交易提交之後，資料表 t4 將會包含以下資料列：

 a | b
 1 | 2

重要

即使沒有 LAQ，當使用基於資料列版本設定的隔離等級時，應用程式也不應假設資料庫引擎在不使用鎖定提示的情況下保證嚴格的排序。對於在 RCSI 下執行依賴嚴格交易執行順序的並行工作負載的客戶 (如前一個範例所示)，我們的一般建議是使用更嚴格的隔離等級，例如 REPEATABLE READ 和 SERIALIZABLE。

最佳化鎖定的診斷新增項目

啟用最佳化鎖定時，下列改善可協助您監控封鎖和鎖死，並疑難排解：

最佳化鎖定的等候類型
- TID 上 S 鎖定的 XACT 等待類型，以及 sys.dm_os_wait_stats (Transact-SQL) 中的資源說明：
  - LCK_M_S_XACT_READ - 當工作正在等候 XACT wait_resource 類型的共用鎖定時發生，且具有讀取意圖。
  - LCK_M_S_XACT_MODIFY - 當工作正在等候 XACT wait_resource 類型的共用鎖定時發生，且具有修改意圖。
  - LCK_M_S_XACT - 當工作正在等候 XACT wait_resource 類型的共用鎖定時發生，且具有無法推斷的意圖。這不是常見情況。
鎖定資源可見度
- XACT 鎖定資源。如需詳細資訊，請參閱 sys.dm_tran_locks (Transact-SQL) 中的 resource_description。
等候資源可見度
- XACT 等候資源。如需詳細資訊，請參閱 sys.dm_exec_requests (Transact-SQL) 中的 wait_resource。
鎖死圖形
- 在鎖死報表 <resource-list> 中的每個資源下，每個 <xactlock> 元素報告鎖死中每個成員鎖定的基礎資源和特定資訊。如需詳細資訊和範例，請參閱最佳化鎖定和鎖死。

最佳化鎖定的最佳做法

啟用讀取認可快照隔離 (RCSI)

若要充分發揮最佳化鎖定的優點，建議您為資料庫啟用讀取認可快照隔離 (RCSI)，並使用 READ COMMITTED 隔離作為預設隔離等級。如果尚未啟用，請連線到 master 資料庫並執行下列陳述式來啟用 RCSI：

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

在 Azure SQL Database 中，RCSI 預設為啟用，且 READ COMMITTED 為預設隔離等級。啟用 RCSI 且使用 READ COMMITTED 隔離等級時，讀取資料列會從陳述式開始時的快照讀取一個版本。使用 LAQ 時，寫入器會根據資料列的最新認可版本來依照述詞限定資料列資格，而不需要取得 U 鎖定。使用 LAQ 時，只有在資料列符合資格，且該資料列上有作用中的寫入交易時，查詢才會等待。根據最新的認可版本來限定資格，並只鎖定符合條件的資料列，可減少封鎖並增加並行。

除了減少封鎖之外，所需的鎖定記憶體也會減少。這是因為讀取器不會佔用任何鎖定，而且寫入器只需要短暫的持續時間鎖定，而不是保留到交易結束的鎖定。當使用更嚴格的隔離等級 (例如 REPEATABLE READ 或 SERIALIZABLE) 時，即使啟用了最佳化鎖定，資料庫引擎仍會保留資料列和頁面鎖定，直到交易結束為止，這對讀取和寫入都會造成封鎖和鎖定記憶體使用量增加。

防止鎖定提示

雖然在啟用最佳化鎖定時，會尊重 UPDLOCK、READCOMMITTEDLOCK、XLOCK、HOLDLOCK 等資料表和查詢提示，但它們會降低最佳化鎖定的效益。鎖定提示會強制資料庫引擎取得資料列或頁面鎖定並保留它們直到交易結束，以尊重鎖定提示的意圖。某些應用程式具有需要鎖定提示的邏輯，例如使用 UPDLOCK 提示讀取資料列，之後再進行更新。建議您只在需要時使用鎖定提示。

使用最佳化鎖定時，現有的查詢沒有任何限制，且不需要重寫查詢。不使用提示的查詢可充分發揮最佳化鎖定的優點。

查詢中某個資料表的資料表提示，不會停用相同查詢中其他資料表的最佳化鎖定。此外，最佳化鎖定只會影響 DML 陳述式 (如 INSERT、UPDATE、DELETE 或 MERGE) 所更新資料表的鎖定行為。例如：

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

在上一個查詢範例中，只有資料表 t6 會受到鎖定提示的影響，而 t5 仍可受益於最佳化鎖定。

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

在上一個查詢範例中，只有資料表 t5 會使用 REPEATABLE READ 隔離等級，並佔用鎖定直到交易結束為止。 t5 的其他更新仍可受益於最佳化鎖定。同樣情況也適用於 HOLDLOCK 提示。

常見問題集 (FAQ)

新的和現有的資料庫是否預設啟用最佳化鎖定？

在 Azure SQL 資料庫中，為是。

如何偵測是否已啟用最佳化鎖定？

請參閱最佳化鎖定是否已啟用？。

我的資料庫沒有啟用加速資料庫復原 (ADR) 會怎麼樣？

如果停用 ADR，系統也會自動停用最佳化鎖定。

我想要強制查詢無視最佳化鎖定並進行封鎖，該怎麼做？

使用 RCSI 的客戶若要在啟用最佳化鎖定的情況下，強制在兩個查詢之間進行封鎖，請使用 READCOMMITTEDLOCK 查詢提示。

唯讀次要複本是否使用最佳化鎖定？

否，因為 DML 陳述式無法在唯讀複本上執行，而且不會取得對應的資料列和頁面鎖定。

在 tempdb 和暫存資料表中修改資料時，是否使用最佳化鎖定？

目前不能。

分享方式：

最佳化鎖定

什麼是最佳化鎖定？

可用性

最佳化鎖定是否已啟用？

鎖定概觀

最佳化鎖定和交易識別碼 (TID) 鎖定

最佳化鎖定和限定性條件鎖定 (LAQ)

使用最佳化鎖定和 RCSI 變更查詢行為

最佳化鎖定的診斷新增項目

最佳化鎖定的最佳做法

啟用讀取認可快照隔離 (RCSI)

防止鎖定提示

常見問題集 (FAQ)

新的和現有的資料庫是否預設啟用最佳化鎖定？

如何偵測是否已啟用最佳化鎖定？

我的資料庫沒有啟用加速資料庫復原 (ADR) 會怎麼樣？

我想要強制查詢無視最佳化鎖定並進行封鎖，該怎麼做？

唯讀次要複本是否使用最佳化鎖定？

在 tempdb 和暫存資料表中修改資料時，是否使用最佳化鎖定？

意見反映

更多資源

分享方式：

最佳化鎖定

什麼是最佳化鎖定？

可用性

最佳化鎖定是否已啟用？

鎖定概觀

最佳化鎖定和交易識別碼 (TID) 鎖定

最佳化鎖定和限定性條件鎖定 (LAQ)

使用最佳化鎖定和 RCSI 變更查詢行為

最佳化鎖定的診斷新增項目

最佳化鎖定的最佳做法

啟用讀取認可快照隔離 (RCSI)

防止鎖定提示

常見問題集 (FAQ)

新的和現有的資料庫是否預設啟用最佳化鎖定？

如何偵測是否已啟用最佳化鎖定？

我的資料庫沒有啟用加速資料庫復原 (ADR) 會怎麼樣？

我想要強制查詢無視最佳化鎖定並進行封鎖，該怎麼做？

唯讀次要複本是否使用最佳化鎖定？

在 tempdb 和暫存資料表中修改資料時，是否使用最佳化鎖定？

相關內容

意見反映

更多資源