優化鎖定

適用於： SQL Server 2025（17.x）Azure SQL Database Azure SQL Managed Instance Microsoft Fabric 中的 SQL 資料庫

優化鎖定增強了交易鎖定機制，以減少並行交易中的鎖定封鎖和鎖定記憶體使用量。

什麼是最佳化鎖定？

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

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

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

例如：

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

Note

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

Availability

下表總結了 SQL 平台上最佳化鎖定的可用性與啟用狀態。

Platform	Available	默認為啟用
Azure SQL Database	Yes	是（一律啟用）
Microsoft Fabric 中的 SQL 資料庫	Yes	是（一律啟用）
Azure SQL 受控實例^AUTD	Yes	是（一律啟用）
Azure SQL 受控執行個體²⁰²⁵	Yes	是（一律啟用）
Azure SQL 受控實例²⁰²²	No	N/A
SQL Server 2025 （17.x）	Yes	否（每個資料庫都可以開啟）
SQL Server 2022 （16.x）和舊版	No	N/A

啟用和停用

若要啟用或停用 SQL Server 資料庫的優化鎖定，請使用 ALTER DATABASE ... SET OPTIMIZED_LOCKING = ON | OFF 命令。如需詳細資訊，請參閱 ALTER DATABASE SET 選項。

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

您必須先在資料庫上啟用加速資料庫復原（ADR），才能啟用優化的鎖定。若要停用 ADR，您必須先停用優化鎖定（如果已啟用）。
為了充分運用最佳化鎖定的優點，應該為資料庫啟用讀取認可快照隔離 (RCSI)。只有在啟用 RCSI 時，最佳化鎖定的 LAQ 元件才會生效。

ADR 始終在 Azure SQL 資料庫、Azure SQL Managed Instance 以及 Microsoft Fabric 的 SQL 資料庫中啟用。 RCSI 預設在 Azure SQL 資料庫中啟用，SQL 資料庫則在 Microsoft Fabric 中啟用。

若要確認目前資料庫已啟用這些選項，請連線資料庫並執行下列 T-SQL 查詢：

SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM sys.databases
WHERE name = DB_NAME();

最佳化鎖定是否已啟用？

每個資料庫都啟用了最佳化鎖定功能。連線您的資料庫，然後使用下列查詢來檢查是否已啟用最佳化鎖定：

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_enabled;

Result	Description
`0`	最佳化鎖定已停用。
`1`	最佳化鎖定已啟用。
`NULL`	無法使用優化的鎖定。

您也可以使用 sys.databases 目錄檢視。例如，若要查看是否為所有資料庫啟用優化鎖定，請執行下列查詢：

SELECT database_id,
       name,
       is_optimized_locking_on
FROM sys.databases;

上鎖概觀

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

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

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

當多個交易嘗試同時存取相同的數據時，資料庫引擎必須解決與並行讀取和寫入的潛在複雜衝突。該引擎可以透過鎖定機制提供 ANSI SQL 交易隔離等級語意。雖然資料庫中的鎖定是必要的，但其可能引發的並行性降低、死結、複雜性問題和鎖定的額外負擔，可能會影響效能和可擴展性。

交易標識碼（TID）鎖定

當使用資料列版本設定型的隔離等級或啟用 ADR 時，資料庫內部的每一列都包含一個交易 ID (TID)。 TID 與該列一起保存。每個修改資料列的交易都會將該列標記上其 TID。

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

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

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

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 鎖定。

如果未啟用優化鎖定，則相同的要求將保留四個鎖：一個 IX（意圖獨佔）鎖定在包含資料列的頁面上，以及每個資料列有三個 X 索引鍵鎖定。

sys.dm_tran_locks動態管理視圖（DMV）對於檢視或排除鎖定問題非常有用。此處用來觀察優化鎖定的運作情形。

資格後鎖定（LAQ）

以 TID 基礎結構為基礎，優化鎖定的 LAQ 元件會變更 DML 語句，例如 INSERT、 UPDATE和 DELETE 取得鎖定的方式。

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

透過最佳化鎖定，並啟用 READ COMMITTED 快照隔離層（RCSI）時，可以在不鎖定的情況下，對最新提交的列版本進行謂詞的樂觀檢查。如果條件不滿足，查詢就會移至掃描中的下一個資料列。如果符合述詞，則會佔用 X 資料列鎖定來更新資料列。

換句話說，鎖定會在數據列的限定 進行修改之後。交易結束之前，資料列更新完成時會立即釋出 X 資料列鎖定。

由於謂詞評估是在不取得任何鎖定的情況下進行，因此修改不同資料列的並行查詢不會彼此封鎖。

例如：

/* Confirm that optimized locking and read committed snapshot isolation (RCSI) are both enabled on this database. */
SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_optimized_locking_on,
       is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

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 鎖定的作用中交易，資料庫引擎會等候它完成。如果該列在先前評估謂詞後改變，資料庫引擎會在修改該列前再次評估（重新限定）該謂詞。如果仍然滿足述詞，則會修改資料列。

謂詞重新限定由部分查詢引擎運算子支援。如果需要謂詞重評估，但查詢計畫使用的運算子不支援謂詞重限定，資料庫引擎會內部中止語句處理，並在沒有 LAQ 的情況下重新啟動。發生這類中止時，會觸發 lock_after_qual_stmt_abort 擴展事件。

某些陳述式（例如 UPDATE 具有變數指派的陳述式及具有 OUTPUT 子句的陳述式）無法在不變更其語意的情況下中止並重新啟動。對於這類陳述，不使用 LAQ。

在下列範例中，會重新評估述詞，因為另一筆交易已變更數據列：

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

跳過索引鎖（SIL）

使用 TID 鎖定時，會使用短時間的排他型X列鎖以及意圖排他型的IX頁鎖來修改列。當使用 RCSI 和 LAQ 時，只有在可能有其他查詢存取該列並期望其穩定時，才需要這些鎖定。此類查詢的例子包括在 REPEATABLE READ or SERIALIZABLE 隔離層級下執行的查詢，或使用相應的鎖定提示。這類查詢稱為 列鎖定查詢 （RLQ）。

當沒有 RLQ 查詢存取某列時，資料庫引擎在修改資料列時可以跳過列與頁面鎖定，只使用專屬的頁面鎖存。此優化降低了鎖機制開銷，同時保留了 ACID 交易語意的完整性。跳過列和頁面鎖定對於修改大量列所涉及的交易特別有利。

目前，SIL 優化僅用於以下情況：

INSERT 關於堆積的陳述。
- IX 頁面鎖定被跳過。
UPDATE 關於聚類索引、非聚類索引與堆積的陳述。
- IX 頁鎖和 X 列鎖會被跳過。

SIL 優化目前未用於以下情況：

DELETE 陳述式。
UPDATE 如果該列包含現有的轉發指標，或在更新時增加新的轉發指標，則會在堆積中執行陳述。
若修改後的列中有任何使用 LOB 資料型別的欄位，如 varchar(max)、 nvarchar(max)、 varbinary(max)、 json和。
對於同一交易中被分割的頁面列。

LAQ 啟發式策略

如同在資格後鎖定（LAQ）中所述，當使用 LAQ 時，使用查詢運算子且不支援謂詞重限定的語句可能會在內部重新啟動並處理，此過程中不會使用 LAQ。如果這種情況經常發生，再處理的額外費用可能會變得相當龐大。為了降低開銷，優化鎖定採用啟發式回饋機制，當開銷超過閾值時會停用 LAQ。

就回饋機制而言，陳述句所完成的工作以邏輯讀取次數來衡量。如果資料庫引擎在陳述句處理開始後，修改了被其他交易修改的列，那麼該陳述句所做的工作會被視為可能被浪費，因為該陳述句可能需要重新處理。

在語句執行時，資料庫引擎會維護 LAQ 回饋資料，追蹤可能浪費的工作、語句重處理的發生情況，以及可能被重新處理的語句所完成的總工作量。

若潛在浪費工作的比例與總工作量的比例，或重處理語句與總語句數量的比值超過各自的門檻，則 LAQ 將被停用。若這兩個比率均低於門檻，則重新啟用 LAQ。

LAQ 回饋資料會在兩個層級被追蹤：

作為 查詢計畫。
- 資料庫引擎會在首次發生語句重處理時開始追蹤 LAQ 回饋，制定計畫。
- 如果查詢被記錄在 Query Store，LAQ 回饋也會被記錄在 Query Store。資料庫引擎會利用這些回饋，在資料庫重新啟動時，讓計畫中啟用或停用 LAQ。
- 帶有 LAQ 回饋的查詢計畫，在 plan_id 目錄檢視中有一列與匹配值。 feature_id欄位和 feature_desc 分別設為 4 和LAQ Feedback。
用來做 資料庫。
- 回饋會彙整所有沒有查詢計畫層級回饋的語句，例如查詢未被 Query Store 捕捉。
- 回饋自資料庫啟動起即被追蹤，並在每次啟動後重新建立。

在決定是否使用 LAQ 來處理語句時，系統會利用查詢計畫的回饋（如果有的話）。否則，它會使用資料庫層級的回饋。這表示有些語句可能使用 LAQ 執行，有些則可能不使用 LAQ。例如，在查詢計畫中可能會停用 LAQ，但在資料庫中則會啟用，反之亦然。

LAQ 限制

資格確認後鎖定在以下情況下將不被使用：

被 LAQ 啟發式停用時。
發生使用衝突的鎖定提示時，例如 UPDLOCK、READCOMMITTEDLOCK、XLOCK或HOLDLOCK。
當交易隔離等級不是 READ COMMITTED 時，或資料庫選項 READ_COMMITTED_SNAPSHOT 被停用時。
正在修改的數據表具有列存儲索引時。
當 DML 語句包含變數指派時。
當 DML 語句有 OUTPUT 子句時。
當 DML 語句使用一個以上的索引搜尋或掃描運算符來讀取正在修改的數據列時。
在語句 MERGE 中。

使用最佳化鎖定和 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

Important

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

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

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

最佳化鎖定的等候類型
- XACT TID 的鎖定等待類型，以及 S 中的資源描述：
  - LCK_M_S_XACT_READ - 當工作正在等候 XACTwait_resource 類型的共用鎖定時發生，且具有讀取意圖。
  - LCK_M_S_XACT_MODIFY - 當工作正在等候 XACTwait_resource 類型的共用鎖定時發生，且具有修改意圖。
  - LCK_M_S_XACT - 當任務正在等候 XACTwait_resource 類型的共用鎖定時，意圖無法被推斷。此案例並不常見。
鎖定資源可見度
- XACT 鎖定資源。如需詳細資訊，請參閱resource_descriptionsys.dm_tran_locks。
等候資源可見度
- XACT 等候資源。如需詳細資訊，請參閱wait_resourcesys.dm_exec_requests。
死結圖表
- 在鎖死報表 <resource-list> 中的每個資源下，每個 <xactlock> 元素報告鎖死中每個成員鎖定的基礎資源和特定資訊。如需詳細資訊和範例，請參閱最佳化鎖定和鎖死。
延伸事件
- lock_after_qual_stmt_abort事件會觸發，當敘述因與其他交易衝突而需要內部重新處理時。如需詳細資訊，請參閱限定性條件鎖定 (LAQ)。
- 該 locking_stats 事件每隔幾分鐘對每個資料庫觸發一次，並提供該時間區間的累積鎖定統計數據，例如鎖升級次數、是否啟用 TID 鎖定與優化鎖的 LAQ 組件，以及因各種原因未使用 LAQ 的查詢次數。儘管優化鎖定已被停用，此事件仍會觸發。
- 在 SQL Server 和 Azure SQL 管理實例中， locking_stats2 事件每隔幾分鐘對每個資料庫觸發一次，並提供了跳過索引鎖與 LAQ 啟發法統計數據。

最佳化鎖定的最佳做法

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

若要將最佳化鎖定的優點最大化，建議您在資料庫上啟用讀取認可快照集隔離（RCSI），並使用 READ COMMITTED 隔離作為預設隔離層級。

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

防止鎖定提示

雖然在啟用最佳化鎖定時，會尊重、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 Database、Azure SQL Managed Instance^AUTD 以及 Microsoft Fabric 中的 SQL 資料庫中，是的。在 SQL Server 2025（17.x）中，最適化鎖定預設為停用，但可以在任何已啟用加速資料庫復原的使用者資料庫上啟用。

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

請參閱已啟用優化鎖定嗎？

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

如果已啟用 RCSI，請使用 READCOMMITTEDLOCK 資料表提示，在啟用優化鎖定時強制兩個查詢之間的阻塞。

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

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

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

目前不能。

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-24