最佳化鎖定

發行項
12/19/2024

適用於：Microsoft Fabric 中的 Azure SQL 資料庫 SQL 資料庫

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

什麼是最佳化鎖定？

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

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

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

例如：

若未優化鎖定，更新數據表中的 1,000 行可能需要 1,000 個獨佔行鎖定 (X)，直到事務結束為止。
透過優化的鎖，更新資料表中的 1,000 個資料列可能需要 1,000 個 X 資料列鎖，但每更新一個資料列時，每個鎖都會立即釋放，在交易結束時，只會保持一個 TID 鎖。因為鎖定會快速釋出，因此鎖定記憶體使用量會降低，而且鎖定擴大的可能性要低得多，進而改善工作負載並行。

注意

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

可用性

Azure SQL Database 和 Microsoft Fabric中的 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;

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

如果未啟用優化鎖定，則相同的請求會保留四個鎖定：每個資料列上有三個 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 期間更新的資料行 t4 來更新資料表 b。

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 上 XACT 鎖定的 S 等待類型，以及 sys.dm_os_wait_stats (Transact-SQL) 中的資源說明：
  - LCK_M_S_XACT_READ - 當工作正在等候 XACTwait_resource 類型的共用鎖定時發生，且具有讀取意圖。
  - LCK_M_S_XACT_MODIFY - 當工作正在等候 XACTwait_resource 類型的共用鎖定時發生，且具有修改意圖。
  - LCK_M_S_XACT - 當任務正在等候 XACTwait_resource 類型的共用鎖定時，意圖無法被推斷。此案例並不常見。
鎖定資源可見度
- XACT 鎖定資源。如需詳細資訊，請參閱 resource_description 中的。
等候資源可見度
- XACT 等候資源。如需詳細資訊，請參閱 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，系統也會自動停用最佳化鎖定。

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

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

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

否，因為 DML 語句無法在唯讀副本上執行，因此不會取得對應的資料列和頁面鎖。

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

目前不能。

共用方式為

最佳化鎖定

什麼是最佳化鎖定？

可用性

最佳化鎖定是否已啟用？

鎖定概觀

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

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

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

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

最佳化鎖定的最佳做法

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

防止鎖定提示

常見問題集 (FAQ)

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

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

我的資料庫未啟用加速資料庫復原時，會發生什麼事？

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

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

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

意見反應

其他資源

共用方式為

最佳化鎖定

什麼是最佳化鎖定？

可用性

最佳化鎖定是否已啟用？

鎖定概觀

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

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

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

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

最佳化鎖定的最佳做法

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

防止鎖定提示

常見問題集 (FAQ)

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

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

我的資料庫未啟用加速資料庫復原時，會發生什麼事？

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

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

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

相關內容

意見反應

其他資源