交易鎖定與資料列版本設定指南

適用於：Microsoft Fabric 中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW） SQL Database

任何資料庫若交易管理不當，時常會導致多使用者的系統發生競爭與效能問題。 隨著存取資料的使用者數量增加，能夠有效使用交易的應用程式更形重要。 此指南描述資料庫引擎用以確保每筆交易完整性的鎖定及資料列版本設定機制，並提供有關應用程式如何能夠有效控制交易的資訊。

交易基本概念

交易就是以單一工作邏輯單元執行的一連串作業。 工作邏輯單元必須呈現出四種屬性，即不可部份完成性 (Atomicity)、一致性 (Consistency)、隔離性 (Isolation) 與耐久性 (Durability) 屬性，稱為 ACID，才能有資格成為一筆交易。

不可部份完成性
交易必須是不可部分完成 (Atomic) 的工作單位；資料的修改若非全部執行，就是全部不執行。

一致性
交易完成時，所有資料必須維持一致的狀態。 在關聯式資料庫 (Relational Database) 中，必須將所有的規則 (Rule) 套用於交易的修改，以維護所有的資料整合性 (Integrity)。 所有的內部資料結構，例如 B 型樹狀結構索引 (B-tree Index) 或是雙向連結串列 (Doubly Linked List)，在交易終止時必須是正確的。

隔離
並行交易所做的修改，必須與任何其他並行交易所做的修改隔離。 交易所辨識的資料不是處於另一筆並行的交易修改資料之前的狀態，就是處於第二筆交易完成後的狀態，但是卻無法辨識中繼狀態。 這稱為序列化能力 (Serializability)，因為這樣可以產生重新載入起始資料並重新執行一系列的交易，以便讓資料最終能夠與原始交易執行後的狀態相同的能力。

耐久性
完全持久交易完成之後，其作用便永遠存在於系統之中。 即使發生系統失敗仍會保存修改。 SQL Server 2014 (12.x) 和更新版本支援延遲的持久交易。 延遲的持久交易會在交易記錄檔記錄永久保存到磁碟之前認可。 如需有關延遲交易持久性的詳細資訊，請參閱控制交易持久性一文。

應用程式負責在強制資料邏輯一致性 (Consistency) 的點上啟動與結束交易。 應用程式必須定義資料修改順序，讓資料維持在與組織的商業規則有關的一致性狀態。 應用程式會在單一的交易中執行這些修改，讓資料庫引擎能夠強制確保交易的完整性。

企業資料庫系統 (例如資料庫引擎的執行個體) 有責任提供可確保每一筆交易完整性的機制。 資料庫引擎可提供：

• 保持交易隔離性 (Isolation) 的鎖定機能 (Locking Facility)。

• 記錄機能可確保交易持久性。 針對完全持久交易，記錄檔記錄會在交易認可之前強行寫入磁碟。 因此，即使伺服器硬體、作業系統或資料庫引擎執行個體本身失敗，該執行個體在重新啟動時會使用交易記錄檔，將所有未完成的交易自動復原到系統失敗點。 延遲的持久交易會在交易記錄檔記錄強行寫入磁碟之前認可。 如果在記錄檔記錄先強行寫入磁碟之前發生系統失敗，這類交易可能會遺失。 如需有關延遲交易持久性的詳細資訊，請參閱控制交易持久性一文。

• 強制不可部份完成性 (Atomicity) 與一致性的交易管理功能。 交易在啟動之後必須成功地完成 (認可)，否則資料庫引擎會將交易啟動後由該交易所做的所有資料修改動作復原。 這項作業稱為回復交易，因為資料將恢復為任何變更發生之前的狀態。

控制交易

應用程式主要是透過指定交易何時啟動及結束來控制交易。 您可利用 Transact-SQL 陳述式或資料庫應用程式開發介面 (API) 函數來指定此項目。 系統也必須能夠正確地處理交易完成之前便結束交易的錯誤。 如需詳細資訊，請參閱交易、執行 ODBC 中的交易及 SQL Server Native Client 中的交易。

依照預設，會在連接層級管理交易。 在連接上啟動交易時，連接上執行的所有 Transact-SQL 陳述式在交易結束之前都是該交易的一部分。 但是，在 Multiple Active Result Set (MARS) 工作階段下，Transact-SQL 明確或不明確交易會成為在批次層級管理的批次範圍交易。 當批次完成時，如果未認可或復原批次範圍的交易，資料庫引擎會自動將其復原。 如需詳細資訊，請參閱使用 Multiple Active Result Sets (MARS)。

啟動交易

您可以使用 API 函數和 Transact-SQL 陳述式，以明確、自動認可或隱含交易來啟動交易。

明確交易

外顯交易是透過 API 函數或藉由發出 Transact-SQL BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK Transact-SQL 陳述式，明確定義交易的啟動與結束的一種交易。 當交易結束時，連線便會回到外顯交易啟動之前的交易模式，可能是隱含或自動認可模式。

在明確的交易中，您可以使用除了下列陳述式以外的所有 Transact-SQL 陳述式：

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• 全文檢索系統預存程序
• sp_dboption 以設定資料庫選項，或於外顯或隱含交易中修改 master 資料庫的任何系統程序。

自動認可交易

自動認可模式是資料庫引擎的預設交易管理模式。 每一個 Transact-SQL 陳述式都會在完成時認可或回復。 陳述式如果成功地完成便被認可；若是遇到任何錯誤則被復原。 只要這個預設模式沒有被外顯交易或隱含交易覆寫，資料庫引擎執行個體的連接都會在自動認可模式下操作。 自動認可模式也是 SqlClient、ADO、OLE DB 與 ODBC 的預設模式。

隱含交易

在隱含交易模式下操作連接時，資料庫引擎的執行個體會在目前交易完成認可或復原後，自動啟動新的交易。 您不需描述交易的啟動；只要認可或復原每一筆交易即可。 隱含交易模式產生連續的交易鍊。 透過 API 函數或 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 陳述式，可將隱含交易模式設為開啟。 這種模式也稱為 Autocommit OFF，請參閱 setAutoCommit 方法 (SQLServerConnection)。

當連接的隱含交易模式設定為開啟之後，資料庫引擎的執行個體便會在第一次執行下列任一個陳述式時，自動啟動一筆交易：

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

批次範圍交易

僅適用於 Multiple Active Result Sets (MARS)，在 MARS 工作階段下啟動的 Transact-SQL 外顯或隱含交易會變成批次範圍的交易。 當批次完成時，資料庫引擎會自動回復未認可或未回復批次範圍的交易。

分散式交易

分散式交易跨越二或多個稱為資源管理員的伺服器。 交易的管理必須由一種稱為交易管理員的伺服器元件在資源管理員之間協調。 資料庫引擎的每個執行個體在分散式交易中可作為資源管理員來運作，而由交易管理員 (例如 Microsoft 分散式交易協調器 (MS DTC)) 或其他支援分散式交易處理 Open Group XA 規格的交易管理員來協調分散式交易。 如需詳細資訊，請參閱 MS DTC 文件集。

在單一資料庫引擎執行個體內部，跨越兩個以上資料庫的交易，就是分散式交易。 執行個體是由內部來管理分散式交易；而對於使用者而言則是以本機交易來運作。

在應用程式中，分散式交易的管理與本機交易大致相同。 交易結束時，應用程式便要求認可或回復交易。 分散式認可必須另由交易管理員來管理，以便將網路失敗可能會造成某些資源管理員成功認可而其他資源管理員卻將交易回復的風險降至最低。 這可藉由在兩個階段 (準備階段與認可階段) 中管理認可過程來達到，稱為兩階段認可交易。

• 準備階段

  當交易管理員接收到認可的要求時，便傳送準備命令給所有參與交易的資源管理員。 然後每個 Resource Manager 都會執行讓交易持續所需的一切工作，而交易的所有交易記錄緩衝區都會排清到磁碟上。 當每個資源管理員完成準備階段時，便將準備的成功或失敗結果傳回交易管理員。 SQL Server 2014 (12.x) 導入了延遲的交易持久性。 延遲的持續交易會在每個 Resource Manager 上的交易記錄緩衝區排清到磁碟前提交。 如需有關延遲交易持久性的詳細資訊，請參閱控制交易持久性一文。

• 認可階段

  如果交易管理員從所有的資源管理員接收到準備成功，便傳送認可命令給每個資源管理員。 然後資源管理員即可完成認可。 如果全部的資源管理員都報告認可成功，交易管理員便傳送成功的通知給應用程式。 若有任何資源管理員報告準備失敗，交易管理員便傳送回復命令給每個資源管理員並告知應用程式認可失敗。

  資料庫引擎應用程式可以透過 Transact-SQL 或透過資料庫 API 來管理分散式交易。 如需詳細資訊，請參閱 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)。

結束交易

您可以使用 COMMIT 或 ROLLBACK 陳述式，或透過對應的 API 函數來結束交易。

• 認可

  交易如果成功便會認可交易。 COMMIT 陳述式可以保證交易所做的全部修改，都會變成資料庫永久的一部分。 提交同時也會釋放資源，例如交易所使用的鎖定。

• 復原

  如果交易中發生錯誤，或是使用者決定取消交易，交易便會復原。 ROLLBACK 陳述式藉由將資料帶回到交易啟動時的狀態，來取消交易中進行的所有修改動作。 復原也會釋放交易所佔用的資源。

交易處理期間的錯誤

如果交易因錯誤而無法順利完成，資料庫引擎會自動復原交易，並釋放交易所佔用的一切資源。 如果用戶端與資料庫引擎執行個體之間的網路連接已中斷，在網路通知該執行個體發生連線中斷時，該連接尚未處理完畢的交易會全部復原。 如果用戶端應用程式失敗或是用戶端電腦當機或重新啟動，也會中斷連接，在網路通知已發生連線中斷時，資料庫引擎執行個體會恢復所有尚未處理完畢的交易。 如果用戶端與資料庫引擎中斷連線，則會復原任何未完成的交易。

如果批次中發生執行階段陳述式錯誤 (例如限制式違規)，資料庫引擎的預設行為是僅復原產生錯誤的陳述式。 您可以使用 SET XACT_ABORT ON 陳述式來變更這個行為。 在執行 SET XACT_ABORT ON 之後，任何執行階段陳述式錯誤都會導致自動復原目前的交易。 編譯錯誤 (例如語法錯誤) 並不受 SET XACT_ABORT 影響。 如需詳細資訊，請參閱 SET XACT_ABORT (Transact-SQL)。

發生錯誤時，適用動作 (COMMIT 或 ROLLBACK) 應該包含在應用程式碼中。 處理錯誤的有效工具之一 (包括交易中的那些錯誤) 是 Transact-SQL TRY...CATCH 建構。 如需有關包括交易之範例的詳細資訊，請參閱 TRY...CATCH (Transact-SQL)。 從 SQL Server 2012 (11.x) 開始，您可以使用 THROW 陳述式來引發例外狀況，然後將執行轉移至 TRY...CATCH 建構的 CATCH 區塊。 如需詳細資訊，請參閱 THROW (Transact-SQL)。

自動認可模式下的編譯與執行階段錯誤

在自動認可模式中，有時資料庫引擎執行個體會好像已將整個批次復原，而非只有一個 SQL 陳述式。 這種情形只有遇到編譯錯誤時才會發生，執行階段錯誤則不會。 編譯錯誤會讓資料庫引擎無法建立執行計畫，因此批次中的任何陳述式都不會執行。 雖然產生錯誤的陳述式之前的所有陳述式都會回復，但錯誤會讓批次中的一切都不會執行。 在下列範例中，位於第三個批次的 INSERT 陳述式由於編譯錯誤而全部不執行。 前兩個 INSERT 陳述式由於並未執行而復原。

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

在下列範例中，第三個 INSERT 陳述式會產生執行階段重複的主索引鍵錯誤。 前兩個 INSERT 陳述式會成功並且受到認可，因此它們在執行階段錯誤之後仍會保留。

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

資料庫引擎會使用延遲的名稱解析，在執行時間解析物件名稱，而不是在編譯時間。 在下列範例中，前兩個 INSERT 陳述式會執行並認可，且這兩個資料列會在參考到不存在的資料表而產生執行階段錯誤的第三個 TestBatch 陳述式之後，仍然留在 INSERT 資料表。

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

鎖定與資料列版本設定基本概念

當多個使用者同時存取資料時，資料庫引擎會使用下列機制來確保交易完整性，並維護資料庫一致性：

• 鎖定

  每一個交易會要求資源上不同類型的鎖定，例如交易相依的資料列、頁面或資料表。 鎖定會阻擋其他交易修改資源，以免造成要求鎖定的交易發生問題。 每一個交易對於鎖定的資源不再具有相依性時，就會釋放它的鎖定。

• 資料列版本設定

  使用以資料列版本設定為基礎的隔離等級之後，資料庫引擎會維護每一個修改過的資料列的版本。 應用程式可指定交易使用資料列版本，來檢視交易或陳述式開始時已存在的資料，而不是以鎖定來保護所有讀取。 透過使用資料列版本設定，讀取作業封鎖其他交易的機會可大幅降低。

鎖定和資料列版本設定可防止使用者讀取尚未認可的資料，以及防止多個使用者同時變更同一筆資料。 若未使用鎖定或資料列版本設定，則對資料執行查詢時，可能會傳回尚未在資料庫中認可的資料，因而產生非預期的結果。

應用程式可以選擇交易隔離等級，用於定義交易的保護層級，以免被其他交易修改。 另可對個別 Transact-SQL 陳述式指定資料表層級提示，進一步修改行為，以符合應用程式的需求。

管理並行資料存取

多個使用者同時存取同一個資源可稱為並行存取資源。 並行資料存取需要一些機制，以防止多個使用者同時嘗試修改其他使用者目前正在使用的資源所造成的不利影響。

並行效果

使用者修改資料會影響到同時正在讀取或修改相同資料的其他使用者。 而我們就稱這些使用者正在並行地存取資料。 若資料庫沒有並行控制功能，使用者會看到下列副作用：

• 更新遺失

  如果二或多個交易選取相同資料列，接著又根據原先選取的值來更新資料列時，就會發生更新遺失的情形。 每個交易並不知道有其他的交易存在。 因此最後的更新會覆寫其他交易所做的更新，而造成資料遺失。

  例如，兩位編輯人員對同一份文件製作了電子副本。 這兩位編輯人員各自進行修改並儲存變更後的副本，覆寫了原始文件。 最後儲存已變更副本的編輯人員會覆寫前一位編輯人員所做的變更。 若第一位編輯人員完成和認可交易後才讓第二位編輯人員存取檔案的話，就能避免這個問題。

• 未認可相依性 (中途讀取)

  第二筆交易讀取的資料列已經被其他交易更新時，會發生未認可依存性 (Uncommitted Dependency)。 此時，第二筆交易讀取的是尚未認可且可能被更新資料列的交易變更之資料。

  例如，有一位編輯人員正在修改一份電子文件。 進行變更時，第二位編輯人員複製了包含目前所有變更的文件，並將文件散發給預期的讀者。 此時，第一位編輯人員認為截至目前的變更均有誤，所以移除了原先的變更後儲存文件。 散發的文件包含了一些現在已不存在的修改內容，而且這些修改應該視為從未發生過。 如果能在第一位編輯人員儲存最後修改並認可交易後才允許其他人讀取修改過的文件，即可避免這個問題。

• 不一致分析 (不可重複讀取)

  第二筆交易存取同一資料列數次，且每次讀取的資料內容都有變動時，就會產生不一致分析 (Inconsistent Analysis)。 不一致分析的情況是第一筆交易在變更資料時，第二筆交易卻在讀取這些資料，其發生原理與未認可依存性類似。 但是在不一致分析中，第二筆交易讀取的資料是由進行變更的交易認可。 此外，不一致分析包括同一資料列的多次讀取 (兩次或以上)，且每次資訊均被其他交易變更，因此稱為不可重複讀取 (Nonrepeatable Read)。

  例如，一位編輯人員兩次都讀取了相同的文件，但是在兩次讀取期間寫作人員重寫了文件。 因此在編輯者第二次讀取文件時，文件已經變更。 而原始讀取是不可重複的。 若能在編輯人員讀完文件後才讓寫作人員變更，就能避免這個問題。

• 虛設項目讀取

  虛設項目讀取是指當您執行兩個完全相同的查詢，但第二個查詢所傳回的資料列組不同時所發生的情況。 下列範例會顯示可能發生這種情況的作業方式。 假設下列兩筆交易都同時執行。 第一筆交易中的兩個 SELECT 陳述式可能會傳回不同的結果，因為第二筆交易中的 INSERT 陳述式會變更這兩筆交易所使用的資料。

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• 資料列更新造成遺漏讀取和重複讀取

  • 遺漏更新的資料列，或多次看到更新的資料列

    在 READ UNCOMMITTED 等級 (或使用 NOLOCK 資料表提示的陳述式) 執行的交易不會發出共用鎖定來防止其他交易修改目前交易所讀取的資料。 執行 READ COMMITTED 等級的交易則會發出共用鎖定，但是在讀取資料列後，就會解除資料列或頁面的鎖定。 在以上任一種情況下，當您掃描索引時，如果其他使用者變更了您讀取期間之資料列的索引鍵資料行，在索引鍵變更將資料列移到掃描前的任何位置後，該資料列可能會再次出現。 同樣地，如果索引鍵變更將資料列移到您已經讀取之索引中的位置，該資料列可能就不會被讀取到。 若要避免這種情況，請使用 SERIALIZABLE 或 HOLDLOCK 提示，或資料列版本設定。 如需詳細資訊，請參閱資料表提示 (Transact-SQL)。

  • 遺失一或多個非更新目標的資料列

    當您使用 READ UNCOMMITTED 時，如果您的查詢使用配置順序掃描 (使用 IAM 頁面) 來讀取資料列，則在另一個交易造成頁面分割時，您可能會遺失資料列。 當您使用 READ COMMITTED 隔離等級時，不會發生這種情況。

並行的類型

當有許多交易會同時嘗試修改資料庫中的資料時，必須實作一個控制系統，這樣某一個交易所做的修改才不會嚴重影響到另一個交易所做的修改。 這就叫做並行控制。

並行控制理論將制定並行控制的方法分為二類：

• 悲觀並行控制

  鎖定系統可防止交易以會影響其他交易的方法來修改資料。 交易在執行某個動作而造成套用鎖定之後，其他交易就不能執行會與該鎖定衝突的動作，直到擁有者解除鎖定為止。 這就叫做封閉式並行控制，因為這種方法主要是用在高度競爭資料的環境中，以鎖定方式來保護資料的成本，會低於發生並行衝突時回復交易的成本。

• 樂觀並行控制

  在開放式並行控制中，交易在讀取資料時，不會將資料鎖定。 但當交易更新資料時，系統會查看在讀取資料之後，是否有其他交易變更了該資料。 若有其他交易更新了該資料，就會產生錯誤。 一般而言，收到錯誤的交易會回復，並重新開始。 這就叫做開放式並行控制，因為這種方法主要是用在低度競爭資料的環境中，偶爾回復交易的成本會低於讀取時鎖定資料的成本。

資料庫引擎支援這兩種並行控制方法。 使用者可針對連接來選取交易隔離等級，或是在資料指標上選取並行選項，以指定並行控制的類型。 這些屬性可用 Transact-SQL 陳述式來定義，或是透過資料庫應用程式開發介面 (API) (例如 ADO、ADO.NET、OLE DB 及 ODBC) 的內容及屬性來定義。

資料庫引擎中的隔離等級

交易可指定隔離等級，以定義某個交易必須與其他交易所修改之資源或資料隔離的程度。 隔離等級是以並行副作用來表示，例如，允許中途讀取 (dirty read) 或虛設項目讀取 (phantom read)。

交易隔離等級控制：

• 在讀取資料時是否取得鎖定，以及要求哪一類型的鎖定。
• 保留讀取鎖定的時間長度。
• 讀取作業是否參考另一個交易修改的資料列：
  • 封鎖資料列上的獨佔鎖定直到釋放它為止。
  • 擷取在啟動陳述式或交易時即存在的資料列認可版本。
  • 讀取未認可的資料修改。

較低的隔離等級將可讓更多的交易同時存取資料，但也會增加交易可能遇到並行作用 (例如，中途讀取或遺失的更新) 的數目。 相反的，較高的隔離等級將可減少交易遇到並行作用的類型，但是將需要更多的系統資源並且會增加一個交易封鎖另一個交易的可能性。 選擇適當的隔離等級需視應用程式的資料完整性需求與每個隔離等級的額外負荷平衡而定。 最高的隔離等級為 SERIALIZABLE，可確保每次交易重複讀取作業時都能擷取相同的資料，但它是透過執行鎖定層級來達成此目的，因此在多使用者系統中有可能會影響其他交易。 最低隔離等級為 READ UNCOMMITTED，可能會擷取到其他交易已修改但尚未認可的資料。 在 READ UNCOMMITTED 中可能會發生所有的並行副作用，但由於沒有讀取鎖定或版本控制，因此可將額外負荷降到最低。

Database Engine 隔離等級

ISO 標準會定義以下隔離等級，且資料庫引擎可完全支援：

隔離等級	定義
READ UNCOMMITTED	最低隔離等級，隔離交易僅能確保不會讀取已實體不一致的資料。 這種等級下允許中途讀取，所以任何交易可能看得到其他交易所做的尚未認可變更。
READ COMMITTED	允許交易對另一筆交易先前讀取 (未修改) 的資料進行讀取，而不必等待前一筆交易完成。 資料庫引擎將維持寫入鎖定 (取自於選取的資料) 直到交易結束，但讀取鎖定會在讀取作業一經執行時即釋放。 這是資料庫引擎的預設等級。
REPEATABLE READ	資料庫引擎將維持讀取及寫入鎖定 (取自於選取的資料) 直到交易結束。 不過由於範圍鎖定未受管理，便有可能發生虛設項目讀取。
SERIALIZABLE	最高的等級，使交易完全與其他交易隔離。 資料庫引擎將維持讀取及寫入鎖定 (取自於選取的資料) 直到交易結束。 當 SELECT 作業使用界定範圍的 WHERE 子句時，就會取得範圍鎖定以特意避免虛設項目讀取。 注意：當您要求 SERIALIZABLE 隔離等級時，複寫資料表上的 DDL 作業和交易可能會失敗。 這是因為複寫查詢所使用的提示可能與 SERIALIZABLE 隔離等級不相容。

資料庫引擎也支援另外兩種使用資料列版本設定的交易隔離等級。 一個是 READ COMMITTED 隔離等級的實作，一個是 SNAPSHOT 交易隔離等級。

資料列版本設定隔離等級	定義
Read Committed Snapshot (RCSI)	當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON (這是 Azure SQL 資料庫的預設設定)，READ COMMITTED 隔離等級會使用資料列版本設定來提供陳述式等級的讀取一致性。 讀取作業只需要結構描述穩定性 (Sch-S) 資料表層級的鎖定，並不需要頁面或資料列的鎖定。 也就是說，資料庫引擎會利用資料列版本設定，依照資料在陳述式開始時存在的狀態，為每個陳述式提供該資料具有交易一致性的快照。 鎖定的使用目的不是為了防止其他交易更新資料。 使用者定義的函數可傳回在包含 UDF 的陳述式開始之後所認可的資料。 當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 OFF (SQL Server 和 Azure SQL 受控執行個體中的預設設定) 時，READ COMMITTED 隔離會在目前交易執行讀取作業的期間，利用共用鎖定來防止其他交易修改資料列。 共用鎖定也會封鎖陳述式，使它們在其他交易完成之前，無法讀取其他交易所修改的資料列。 這兩種實作都符合 READ COMMITTED 隔離的 ISO 定義。
SNAPSHOT	快照隔離等級使用資料列版本設定來提供交易層級的讀取一致性。 讀取作業並不需要頁面或資料列的鎖定，只需要結構描述穩定性 (Sch-S) 資料表鎖定。 當讀取其他交易所修改的資料列時，讀取作業會擷取在啟動交易時就已經存在的資料列版本。 只有當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 時，才能使用 SNAPSHOT 隔離。 預設情況下，此選項對於 SQL Server 和 Azure SQL 受控執行個體中的使用者資料庫設定為 OFF，對於 Azure SQL 資料庫中的資料庫設定為 ON。 注意：資料庫引擎不支援中繼資料的版本控制。 因此，哪些 DDL 作業可以在快照隔離之下執行的明確交易中執行會有一些限制。 在 BEGIN TRANSACTION 陳述式之後的快照隔離下，不允許使用下列 DDL 陳述式：ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME 或任何通用語言執行平台 (CLR) DDL 陳述式。 在隱含交易內使用快照集隔離時，這些陳述式會受到允許。 就定義而言，隱含交易是一種單一陳述式，可強制使用快照隔離的語意 (即使是 DDL 陳述式)。 違反這個原則可能會造成錯誤 3961：Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

下表顯示不同隔離等級所啟用的並行副作用。

隔離等級	中途讀取 (Dirty read)	非可重複讀取	虛設項目 (Phantom)
READ UNCOMMITTED	Yes	.是	是
READ COMMITTED	無	.是	是
REPEATABLE READ	無	無	.是
SNAPSHOT	無	無	無
SERIALIZABLE	無	無	No

如需詳細了解每個交易隔離等級所控制之特定類型的鎖定或資料列版本設定，請參閱 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

交易隔離等級可使用 Transact-SQL 或透過資料庫 API 來設定。

Transact-SQL
Transact-SQL 指令碼會使用 SET TRANSACTION ISOLATION LEVEL 陳述式。

ADO
ADO 應用程式會將 Connection 物件的 IsolationLevel 屬性設為 adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead 或 adXactReadSerializable。

ADO.NET
使用 System.Data.SqlClient 受控命名空間的 ADO.NET 應用程式可以呼叫 SqlConnection.BeginTransaction 方法，並將 IsolationLevel 選項設定為 Unspecified、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable 或 Snapshot。

OLE DB
交易開始時，應用程式會使用 OLE DB 呼叫 ITransactionLocal::StartTransaction，並將 isoLevel 設為 ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、ISOLATIONLEVEL_SNAPSHOT 或 ISOLATIONLEVEL_SERIALIZABLE。

在自動認可模式中指定隔離等級時，OLE DB 應用程式可以將 DBPROPSET_SESSION 屬性 DBPROP_SESS_AUTOCOMMITISOLEVELS 設為 DBPROPVAL_TI_CHAOS、DBPROPVAL_TI_READUNCOMMITTED、DBPROPVAL_TI_BROWSE、DBPROPVAL_TI_CURSORSTABILITY、DBPROPVAL_TI_READCOMMITTED、DBPROPVAL_TI_REPEATABLEREAD、DBPROPVAL_TI_SERIALIZABLE、DBPROPVAL_TI_ISOLATED 或 DBPROPVAL_TI_SNAPSHOT。

ODBC
ODBC 應用程式會呼叫 SQLSetConnectAttr，並將 Attribute 設定為 SQL_ATTR_TXN_ISOLATION、ValuePtr、SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED、SQL_TXN_REPEATABLE_READ 或 SQL_TXN_SERIALIZABLE。

對於快照交易，應用程式會呼叫 SQLSetConnectAttr 並將屬性 (Attribute) 設為 SQL_COPT_SS_TXN_ISOLATION，以及將 ValuePtr 設為 SQL_TXN_SS_SNAPSHOT。 快照交易可使用 SQL_COPT_SS_TXN_ISOLATION 或 SQL_ATTR_TXN_ISOLATION 進行擷取。

資料庫引擎中的鎖定

鎖定是資料庫引擎的一種機制，用以同步處理多個使用者在同一時間對相同資料的存取。

在交易取得資料目前狀態的相依性前 (例如讀取或修改資料)，它必須保護自己使其免於受到另一個交易修改相同資料的影響。 交易可以要求資料的鎖定以達到此目的。 鎖定有不同的模式，例如共用 (S) 或獨佔 (X)。 鎖定模式可定義交易在資料上的相依性層級。 若已授與該資料的鎖定模式給某個交易，就不會再授與鎖定給另一個交易，以免造成衝突。 如果交易所要求的鎖定模式，將和已授與相同資料的鎖定造成衝突，資料庫引擎將停止要求交易，直到釋放第一個鎖定為止。

當交易修改資料時，將會持有防止修改的特定鎖定，直到交易結束為止。 交易持有保護讀取作業的鎖定時間長度，取決於交易隔離等級設定，以及是否啟用最佳化鎖定。

• 未啟用最佳化鎖定時，寫入所需的資料列和頁面鎖定會保留到交易結束為止。

• 啟用最佳化鎖定時，只會保留交易識別碼 (TID) 鎖定到交易結束。 在預設 READ COMMITTED 隔離等級下，交易將不會保留寫入所需的資料列和頁面鎖定，直到交易結束為止。 這可減少所需的鎖定記憶體，並減少鎖定擴大的需求。 此外，當最佳化鎖定啟用時，限定性條件鎖定 (LAQ) 最佳化功能會評估對最新認可資料列版本查詢的述詞，而不需要取得鎖定，藉此改善並行。

交易完成 (認可或復原) 時，會釋放交易所持有的所有鎖定。

應用程式通常不會直接要求鎖定。 資料庫引擎中的鎖定管理員會在內部管理鎖定。 當資料庫引擎執行個體處理 Transact-SQL 陳述式時，資料庫引擎查詢處理器可以決定要存取哪些資源。 查詢處理器根據存取類型以及交易隔離等級設定，決定需要哪些類型的鎖定以保護每個資源。 查詢處理器接著會對鎖定管理員要求適當的鎖定。 如果沒有其他交易持有衝突的鎖定，鎖定管理員就會授與鎖定。

鎖定資料粒度和階層

資料庫引擎具有多資料粒度鎖定 (Multigranular Lock)，允許交易鎖定不同類型的資源。 為了把鎖定的成本降至最低，資料庫引擎自動依照工作的適當層級來鎖定資源。 鎖定於較小的資料粒度 (Granularity) 如資料列可以提高並行，但如果鎖定許多的資料列則由於必須持有更多的鎖定而造成更高的額外負荷。 鎖定於較大的資料粒度如資料表，從並行的角度來看則由於鎖定整個資料表會限制其他交易對於資料表其他部份的存取因而更費時。 但由於必須維持的鎖定較少因此額外負荷較低。

資料庫引擎通常必須在資料粒度的多個層級取得鎖定，以完全保護資源。 在資料粒度的多個層級之鎖定群組稱為鎖定階層。 例如，若要充份地保護索引的讀取，資料庫引擎可能需要取得資料列的共用鎖定，以及頁面和資料表的意圖共用鎖定。

下表顯示資料庫引擎可鎖定的資源。

資源	描述
RID	資料列識別碼，用來鎖定堆積內單一資料列。
KEY	在 B 型樹狀結構索引中鎖定單一資料列的資料列鎖定。
PAGE	資料庫中的 8 KB 頁面，例如資料或索引頁面。
EXTENT	連續八個頁面的群組，例如資料頁或索引頁面。
HoBT 1	堆積或 B 樹狀目錄。 針對資料表中沒有叢集索引的 B 型樹狀結構 (索引) 或堆積資料頁面進行保護鎖定。
TABLE 1	一整個資料表，包含所有資料和索引。
FILE	資料庫檔案
APPLICATION	應用程式指定資源。
METADATA	中繼資料鎖定。
ALLOCATION_UNIT	配置單位。
DATABASE	一整個資料庫。
XACT 2	用於最佳化鎖定的交易識別碼 (TID) 鎖定。 如需詳細資訊，請參閱交易識別碼 (TID) 鎖定。

¹ HoBT 和 TABLE 鎖定可受 ALTER TABLE 的 LOCK_ESCALATION 選項影響。

² 其他鎖定資源適用於 XACT 鎖定資源，請參閱針對優化鎖定的診斷新增項目。

鎖定模式

資料庫引擎使用可決定並行交易如何存取資源的各種鎖定模式來鎖定資源。

下表顯示資料庫引擎可使用的資源鎖定模式。

鎖定模式	描述
共用 (S)	用於不變更或更新資料的讀取作業，例如 SELECT 陳述式。
更新 (U)	用於可更新的資源上。 防止當多個工作階段正在讀取、鎖定及後來可能更新資源時發生常見的死結。
獨佔 (X)	用於資料修改動作，例如 INSERT、UPDATE 或 DELETE。 確保不能對相同資源同時進行多重更新。
意圖	用來建立鎖定階層。 意圖鎖定的類型為：意圖共用 (IS)、意圖獨佔 (IX) 與共用意圖獨佔 (SIX)。
結構描述	執行相依於資料表結構描述的作業時使用。 結構描述鎖定的類型為：結構描述修改 (Sch-M) 與結構描述穩定性 (Sch-S)。
大量更新 (BU)	使用 TABLOCK 提示將資料大量複製到資料表時使用。
索引鍵範圍	當使用 SERIALIZABLE 交易隔離等級時，保護查詢讀取的資料列範圍。 確定其他交易無法插入資料列，這些資料列在查詢重新執行時可限定 SERIALIZABLE 交易的查詢。

共用鎖定

共用 (S) 鎖定允許並行交易在封閉式 (Pessimistic) 並行控制之下讀取資源。 當資源存在共用 (S) 鎖定時，任何交易都無法修改資料。 除非交易隔離等級是設為 REPEATABLE READ 或更高等級，或是使用鎖定提示來保持交易期間的共用 (S) 鎖定，否則讀取作業一完成就會釋放資源的共用 (S) 鎖定。

更新鎖定

資料庫引擎會在準備執行更新時放置更新 (U) 鎖定。 U 鎖定與 S 鎖定相容，但一次只有一筆交易可以保存指定資源的 U 鎖定。 這是關鍵 - 許多並行交易可以保存 S 鎖定，但只有一筆交易可以保存一個資源的 U 鎖定。 更新 (U) 鎖定最終會升級為獨佔 (X) 鎖定，以更新資料列。

如果在陳述式中指定 UPDLOCK 資料表提示，UPDATE 以外的陳述式也可以使用更新 (U) 鎖定。

• 部分應用程式會使用「選取資料列，然後更新資料列」模式，其中讀取和寫入會在交易中明確分隔。 在此情況下，如果隔離等級為 REPEATABLE READ 或 SERIALIZABLE，並行更新可能會導致鎖死，如下所示：

  交易在讀取資料時取得資源的共用 (S) 鎖定，然後修改資料，此過程需要將鎖定轉換為獨佔 (X) 鎖定。 如果兩筆交易取得某個資源的共用 (S) 鎖定，然後嘗試同時更新資料，則其中一筆交易便會嘗試將鎖定轉換為獨佔 (X) 鎖定。 這種「從共用到獨佔」的鎖定轉換必須等待，因為某一筆交易的獨佔 (X) 鎖定與另一筆交易的共用 (S) 鎖定並不相容，所以會發生鎖定等候。 第二筆交易便嘗試取得更新時的獨佔 (X) 鎖定。 由於兩筆交易都轉換成獨佔 (X) 鎖定，且兩者皆等候另一筆交易釋放其共用 (S) 鎖定，因此便發生死結。

  在預設 READ COMMITTED 隔離等級中，S 鎖定持續時間很短，會在使用後立即釋放。 雖然上述的鎖死仍然可行，但短期鎖定的可能性要低得多。

  為了避免這種類型的死結，應用程式可以遵循「選取具有 UPDLOCK 提示的資料列，然後更新資料列」模式。

• 如果當正在使用 SNAPSHOT 隔離時在寫入中使用 UPDLOCK 提示，交易必須能夠存取最新版本的資料列。 如果最新版本不再可見，則有可能收到 Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict。 如需範例，請參閱使用快照集隔離。

獨佔鎖定

獨佔 (X) 鎖定防止並行交易存取某個資源。 運用獨佔 (X) 鎖定，沒有其他交易可修改受保護的資料；只有使用 NOLOCK 提示或 READ UNCOMMITTED 隔離等級，才能進行讀取作業。

資料修改陳述式 (例如 INSERT、UPDATE 和 DELETE) 結合了讀取和修改作業。 陳述式先執行讀取作業來取得資料，再執行必要的修改作業。 因此，資料修改陳述式通常會同時要求共用鎖定和獨佔鎖定。 例如，UPDATE 陳述式可能基於與一個資料表的聯結來修改另一個資料表的資料列。 在這個案例中，除了對已更新的資料列要求獨佔鎖定之外，UPDATE 陳述式還對聯結資料表中讀取的資料列要求共用鎖定。

意圖鎖定

資料庫引擎使用意圖鎖定來保護，把共用 (S) 鎖定或獨佔 (X) 鎖定放在鎖定階層中較低的資源上。 「意圖鎖定」的意思是指在取得更低層級的鎖定之前，需要先取得意圖鎖定，藉此表示鎖定更低層級的意圖。

意圖鎖定有兩個用途：

• 防止其他交易修改較高層級的資源，而導致較低層級的鎖定失效。
• 為了改善資料庫引擎偵測資料粒度較高層級鎖定衝突的效率。

例如，在資料表內的頁面或資料列要求共用 (S) 鎖定之前，先在資料表層級要求共用意圖鎖定。 在資料表層級上設定意圖鎖定讓另一筆交易無法後續取得包含該分頁之資料表的獨佔 (X) 鎖定。 意圖鎖定可以提昇效能，因為資料庫引擎只需要在資料表層級上檢查意圖鎖定，即可判斷交易是否可安全地取得該資料表的鎖定。 這種方式省略了必須檢查資料表的每個資料列或分頁的鎖定來判斷交易是否可以鎖定整個資料表的需求。

意圖鎖定包括意圖共用 (IS)、意圖獨佔 (IX) 與以意圖獨佔共用 (SIX)。

鎖定模式	描述
意圖共用 (IS)	保護在階層較低位置的某些 (但不是全部) 資源上要求的或取得的共用鎖定。
意圖獨佔 (IX)	保護在階層較低位置的某些 (但不是全部) 資源上要求的或取得的獨佔鎖定。 IX 是 IS 的超集，它也保護在較低層級資源要求的共用鎖定。
與意圖獨佔共用 (SIX)	保護對階層較低位置的所有資源要求的或取得的共用鎖定，以及對某些 (但非全部) 較低層級資源的意圖獨佔鎖定。 在最上層的資源中允許同時發生的 IS 鎖定。 例如，在資料表上取得 SIX 鎖定也會取得所修改頁面的意圖獨佔鎖定，以及所修改資料列的獨佔鎖定。 每個資源一次只能有一個 SIX 鎖定以防止其他的交易更新資源，雖然其他的交易可藉由取得資料表層級的 IS 鎖定來讀取階層架構中位於較低層級的資源。
意圖更新 (IU)	保護對階層中較低的所有資源要求的或取得的更新鎖定。 IU 鎖定只使用於頁面資源。 如果發生更新作業，IU 鎖定會轉換成 IX 鎖定。
共用意圖更新 (SIU)	S 和 IU 鎖定的結合，這是個別取得這些鎖定又同時保留兩種鎖定的結果。 例如，交易執行具有 PAGLOCK 提示的查詢，然後執行更新作業。 具有 PAGLOCK 提示的查詢取得 S 鎖定，而更新作業則取得 IU 鎖定。
更新意圖獨佔 (UIX)	U 和 IX 鎖定的結合，這是個別取得這些鎖定又同時保留兩種鎖定的結果。

結構描述鎖定

資料庫引擎是在資料表的資料定義語言 (DDL) 作業 (例如加入資料行或卸除資料表) 期間使用結構描述修改 (Sch-M) 鎖定。 在保留期間，Sch-M 鎖定禁止資料表的並行存取。 這表示 Sch-M 鎖定會封鎖所有外在作業，直到釋放鎖定為止。

有些資料操作語言 (DML) 作業 (例如資料表截斷) 使用 Sch-M 鎖定來防止並行作業存取受影響的資料表。

資料庫引擎在編譯並執行查詢時，將使用結構描述穩定性 (Sch-S) 鎖定。 Sch-S 鎖定並未封鎖任何交易式鎖定，包括獨佔 (X) 鎖定。 因此，其他的交易在查詢編譯期間可以繼續執行，包括對資料表使用 X 鎖定的那些交易。 然而，並行的 DDL 作業，以及取得 Sch-M 鎖定的並行 DML 作業，都會被 Sch-S 鎖定封鎖。

大量更新鎖定

大量更新 (BU) 鎖定允許多個執行緒將資料同時大量載入到相同資料表，同時禁止未大量載入資料的其他處理序存取該資料表。 資料庫引擎會在下列兩種情況都成立時使用大量更新 (BU) 鎖定。

• 當您使用 Transact-SQL BULK INSERT 陳述式或 OPENROWSET(BULK) 函數，或使用任何的大量插入 API 命令如 .NET SqlBulkCopy、OLEDB 快速載入 API 或 ODBC 大量複製 API 等，大量複製資料到資料表中。
• 當已指定 TABLOCK 提示，或者使用 sp_tableoption 設定了 table lock on bulk load 資料表選項。

索引鍵範圍鎖定

索引鍵範圍鎖定可保護由 Transact-SQL 陳述式所讀取記錄集內隱含包括的資料列範圍，並使用 SERIALIZABLE 的交易隔離等級。 索引鍵範圍鎖定可預防虛設項目讀取。 透過保護資料列之間的索引鍵範圍，也可防止某交易存取的記錄集內的虛設項目插入或刪除。

鎖定相容性

鎖定相容性可控制多筆交易是否可同時對相同的資源取得鎖定。 若資源已被其他交易鎖定，則只有在要求的鎖定模式與現有鎖定模式相容時，才能授與新的鎖定要求。 若所要求鎖定的模式與現有鎖定不相容，則要求新鎖定的交易會等候現有鎖定被釋放，或等候鎖定逾時間隔過期。 例如，沒有任何一種鎖定模式與獨佔鎖定相容。 有獨佔 (X) 鎖定存在時，其他的交易都無法取得該資源的任何一種鎖定 (共用、更新或獨佔)，直到獨佔 (X) 鎖定被釋放為止。 相反地，如果資源已套用共用 (S) 鎖定，則即使第一筆交易尚未完成，其他的交易仍可取得該資源的共用鎖定或更新 (U) 鎖定。 然而在共用鎖定尚未釋放之前，其他的交易仍然無法取得獨占鎖定。

下表顯示常見鎖定模式的相容性。

現有已授與的模式	IS	S	U	IX	SIX	X
要求的模式
意圖共用 (IS)	Yes	.是	.是	.是	是	No
共用 (S)	Yes	.是	是	無	無	No
更新 (U)	Yes	是	無	無	無	No
意圖獨佔 (IX)	是	無	無	.是	無	No
與意圖獨佔共用 (SIX)	是	無	無	無	無	No
獨佔 (X)	No	無	無	無	無	無

使用下表來判斷資料庫引擎中所有可用鎖定模式的相容性。

關鍵	描述
否	無衝突
I	Illegal
C	衝突
荷蘭	無鎖定
SCH-S	架構穩定性鎖定
SCH-M	架構修改鎖定
S	共用
U	更新
X	排除
IS	意圖共用
IU	意圖更新
IX	意圖獨佔
SIU	與意圖更新共用
SIX	與意圖獨佔共用
UIX	使用意圖獨佔更新
BU	大量更新
RS-S	共用範圍共用
RS-U	共用範圍更新
RI-N	插入 range-null
RI-S	插入範圍共用
RI-U	插入 range-update
RI-X	插入範圍獨佔
RX-S	獨佔範圍共用
RX-U	獨佔範圍更新
RX-X	獨佔範圍-獨佔

索引鍵範圍鎖定

索引鍵範圍鎖定可保護由 Transact-SQL 陳述式所讀取記錄集內隱含包括的資料列範圍，並使用 SERIALIZABLE 的交易隔離等級。 SERIALIZABLE 隔離等級要求在交易期間執行的任何查詢，在交易期間每次執行時都必須取得相同的資料列集。 索引鍵範圍鎖定藉由預防其他交易插入新資料列時，這些資料列的索引鍵落在 SERIALIZABLE 交易讀取的索引鍵範圍，來滿足此種需求。

索引鍵範圍鎖定可預防虛設項目讀取。 藉由保護資料列之間的索引鍵範圍，也可以預防虛設項目插入到交易存取的記錄集。

索引鍵範圍鎖定是放置於索引之上，指定開始和結束的索引鍵值。 因為這些動作會先在索引上取得鎖定，因此這種鎖定可封鎖任何嘗試插入、更新或刪除含有索引鍵值落入範圍的任何資料列。 例如，SERIALIZABLE 交易可能會發出 SELECT 陳述式，其會讀取索引鍵值符合 BETWEEN 'AAA' AND 'CZZ' 條件的所有資料列。 從 'AAA' 到 'CZZ' 範圍中索引鍵值的索引鍵範圍鎖定，可預防其他交易將含有索引鍵值的資料列插入到該範圍內的任何地方，例如 'ADG'、 'BBD'或 'CAL'。

索引鍵範圍鎖定模式

索引鍵範圍鎖定包括範圍以及資料列元件，以範圍-資料列的格式來指定：

• 範圍代表保護兩個連續索引項之間的範圍的鎖定模式。
• 資料列代表保護索引項的鎖定模式。
• 模式代表所使用的合併鎖定模式。 索引鍵範圍鎖定模式由兩個部份組成。 第一個部份代表用來鎖定索引鍵範圍的鎖定類型 (RangeT)，第二個部份代表用來鎖定特定索引鍵的鎖定類型 (K)。 這兩個部份使用連字號 (-) 來連接，例如 RangeT-K。

範圍	資料列	[模式]	描述
RangeS	S	RangeS-S	共用範圍，共用資源鎖定；SERIALIZABLE 範圍掃描。
RangeS	U	RangeS-U	共用範圍，更新資源鎖定；SERIALIZABLE 更新掃描。
RangeI	Null	RangeI-N	插入範圍，Null 資源鎖定；在插入新的索引鍵到索引之前用來測試範圍。
RangeX	X	RangeX-X	獨占範圍，獨占資源鎖定；在範圍內更新索引鍵時使用。

索引鍵範圍鎖定模式的相容性矩陣顯示，哪些鎖定與重疊索引鍵和範圍中取得的其他鎖定相容。

現有已授與的模式	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
要求的模式
共用 (S)	Yes	是	無	.是	.是	是	No
更新 (U)	是	無	無	.是	無	.是	No
獨佔 (X)	No	無	無	無	無	.是	無
RangeS-S	.是	是	無	.是	是	無	無
RangeS-U	.是	無	無	.是	無	無	無
RangeI-N	.是	.是	是	無	無	.是	無
RangeX-X	無	無	無	無	無	無	No

轉換鎖定

轉換鎖定是在索引鍵範圍鎖定與另一種鎖定重疊時建立。

鎖定 1	鎖定 2	轉換鎖定
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

在不同的複雜環境下，可以觀察到短期的轉換鎖定，有時是在並行的處理序執行時。

可序列化範圍掃描、單一擷取、刪除以及插入

索引鍵範圍鎖定可確保下列動作是可序列化：

• 範圍掃描查詢
• 單一擷取不存在的資料列
• 刪除動作
• 插入動作

在索引鍵範圍鎖定發生之前，必須滿足下列條件：

• 交易隔離等級必須設為 SERIALIZABLE。
• 查詢處理器必須使用索引來實作範圍篩選述詞。 例如，SELECT 陳述式中的 WHERE 子句可以使用這個述詞建立範圍條件：ColumnX BETWEEN N'AAA' AND N'CZZ'。 如果 ColumnX 涵蓋在索引鍵中，才會取得索引鍵範圍鎖定。

範例

下列資料表和索引是用來作為索引鍵範圍鎖定範例要遵循的基礎。

範圍掃描查詢

為了確保範圍掃描查詢是可序列化，相同的查詢每次在相同交易內執行時都必須傳回相同的結果。 其他的交易絕不能把新的資料列插入範圍掃描查詢內；否則這些動作將會變成虛設項目插入。 例如，以下的查詢使用上述的資料表與索引：

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

關鍵範圍鎖定會放置在對應到名稱介於值 Adam 和 Dale 之間資料列範圍的索引項目，以防止新增或刪除符合先前查詢條件的新資料列。 雖然此範圍中的名稱是 Adam，但此索引項目上 RangeS-S 模式關鍵範圍鎖定會確保以字母 A 開頭的新名稱 (例如 Abigail) 皆無法新增到 Adam 之前。 同樣的，位於 Dale 索引項目的 RangeS-S 關鍵範圍鎖定，則會確保以字母 C 開頭的新名稱 (例如 Clive) 皆無法新增至 Carlos 後面。

單一擷取不存在的資料

如果交易內的查詢嘗試選取不存在的資料列，則在同一筆交易內稍後的某一點所提交的查詢必須傳回相同的結果。 其他的任何交易皆不得插入這個不存在的資料列。 例如，給定以下的查詢：

SELECT name
FROM mytable
WHERE name = 'Bill';

將索引鍵範圍鎖定放在與名稱範圍從 Ben 到 Bing 對應的索引項，因為要把名稱 Bill 插入這兩個相鄰的索引項之間。 將 RangeS-S 模式的索引鍵範圍鎖定放在索引項 Bing 之上。 這可預防其他交易將值 (例如 Bill) 插入到索引項 Ben 和 Bing之間。

刪除作業，不使用最佳化鎖定

在交易內刪除某個資料列時，交易進行刪除動作期間不需鎖定該資料列所處之範圍。 鎖定欲刪除的索引鍵值直到交易結束，即足以維持可序列化能力。 例如，給定以下的 DELETE 陳述式：

DELETE mytable
WHERE name = 'Bob';

將獨占 (X) 鎖定放在與名稱 Bob 對應的索引項。 其他交易可以在要刪除的具有值 Bob 的資料列之前或之後插入或刪除數值。 但是，嘗試讀取、插入或是刪除與 Bob 值相符資料列的任何交易，在進行刪除動作的交易尚未認可或回復之前都會被封鎖。 (READ_COMMITTED_SNAPSHOT 資料庫選項和 SNAPSHOT 隔離等級也允許讀取自先前認可狀態的資料列版本。)

可以使用三種基本鎖定模式來執行範圍刪除：資料列、分頁或資料表鎖定。 資料列、分頁或資料表的鎖定策略是由查詢最佳化工具決定，或可由使用者透過查詢最佳化工具提示 (如 ROWLOCK、PAGLOCK 或 TABLOCK) 來指定。 使用 PAGLOCK 或 TABLOCK 時，如果所有資料列都會從此分頁刪除，資料庫引擎會立即重新配置索引頁。 相反的，若是使用 ROWLOCK，所有已刪除的資料列則僅標示為已刪除；稍後再使用背景工作將這些資料列從索引頁中移除。

刪除作業，使用最佳化鎖定

刪除交易內的資料列時，將會以累加方式取得和釋放資料列和頁面鎖定，而不會在交易期間保留。 例如，給定以下的 DELETE 陳述式：

DELETE mytable
WHERE name = 'Bob';

TID 鎖定會放在交易期間的所有已修改資料列上。 系統會在對應至值 Bob 的索引資料列 TID 上取得鎖定。 使用最佳化鎖定時，將繼續取得頁面和資料列鎖定以進行更新，但每次更新每個資料列後，就會立即釋出每個頁面和資料列鎖定。 TID 鎖定可防止資料列在交易完成之前更新。 在進行刪除動作的交易尚未認可或復原之前，系統會封鎖任何嘗試讀取、插入或是刪除含 Bob 值資料列的交易。 (READ_COMMITTED_SNAPSHOT 資料庫選項和 SNAPSHOT 隔離等級也允許讀取自先前認可狀態的資料列版本。)

否則，刪除作業的鎖定機制會與未使用最佳化鎖定相同。

插入作業且不使用最佳化鎖定

在交易內插入某個值時，交易進行插入動作期間不需鎖定該值所處之範圍。 鎖定欲插入的索引鍵值直到交易結束，即足以維持可序列化能力。 例如，給定以下的 INSERT 陳述式：

INSERT mytable VALUES ('Dan');

將 RangeI-N 模式的索引鍵範圍鎖定放在與名稱 David 對應的索引資料列來測試範圍。 如果授與鎖定，則會插入值為 Dan 的資料列，並在插入的資料列上放置獨佔 (X) 鎖定。 RangeI-N 模式的索引鍵範圍鎖定只有在測試範圍時才需要，且在交易進行插入動作期間不需持有。 其他的交易皆可在 Dan 值的插入資料列前面或後面插入或刪除值。 但是，嘗試讀取、插入或是刪除含 Dan 值資料列的任何交易在進行插入動作的交易尚未認可或回復之前都會遭封鎖。

插入作業且使用最佳化鎖定

在交易內插入某個值時，交易進行插入動作期間不需鎖定該值所處之範圍。 系統通常不會取得資料列和頁面鎖定，只有在線上索引重建進行中，或有並行 SERIALIZABLE 交易時才會取得鎖定。 如果取得資料列和頁面鎖定，這些鎖定會快速釋放，而不會在交易期間保留。 為插入的索引鍵值加上獨佔 TID 鎖定直到交易結束，即足以維持可序列化能力。 例如，給定以下的 INSERT 陳述式：

INSERT mytable VALUES ('Dan');

若使用最佳化鎖定，只有在執行個體中至少有一個使用 SERIALIZABLE 隔離等級的交易時，才會取得 RangeI-N 鎖定。 將 RangeI-N 模式的索引鍵範圍鎖定放在與名稱 David 對應的索引資料列來測試範圍。 如果授與鎖定，則會插入值為 Dan 的資料列，並在插入的資料列上放置獨佔 (X) 鎖定。 RangeI-N 模式的索引鍵範圍鎖定只有在測試範圍時才需要，且在交易進行插入動作期間不需持有。 其他的交易皆可在 Dan 值的插入資料列前面或後面插入或刪除值。 但是，嘗試讀取、插入或是刪除含 Dan 值資料列的任何交易在進行插入動作的交易尚未認可或回復之前都會遭封鎖。

鎖定擴大

鎖定擴大是將許多的細緻鎖定轉換成較少的粗略鎖定之過程，可減少系統的負擔，但會增加並行競爭的可能性。

鎖定擴大的行為會因是否啟用最佳化鎖定而有所不同。

鎖定擴大且不使用最佳化鎖定

當資料庫引擎取得低層級鎖定時，也會同時對包含更低層級物件的物件進行意圖鎖定：

• 當鎖定資料列或索引鍵範圍時，SQL Server 資料庫引擎會對包含資料列或索引鍵的分頁進行意圖鎖定。
• 當鎖定頁面時，資料庫引擎會對包含分頁的較高層級物件進行意圖鎖定。 除了對物件進行意圖鎖定之外，也會在下列物件上要求意圖頁面鎖定：
  • 非叢集索引的分葉層級頁面
  • 叢集索引的資料頁面
  • 堆積資料頁面

資料庫引擎可能會在相同的陳述式中同時進行資料列與頁面鎖定，以盡可能減少鎖定數目，並降低必須使用鎖定擴大的可能性。 例如，資料庫引擎可能會對非叢集索引進行頁面鎖定 (若索引節點中已選取足夠的連續索引鍵來滿足查詢)，並對叢集索引或堆積進行資料列鎖定。

為了擴大鎖定，資料庫引擎會試圖將資料表上意圖鎖定變更為對應的完全鎖定，例如：將意圖獨佔 (IX) 鎖定變為獨佔 (X) 鎖定，或將意圖共用 (IS) 鎖定變為共用 (S) 鎖定。 若鎖定擴大的嘗試成功而取得資料表的完全鎖定，則所有由堆積或索引上的交易所保有的 HoBT 型樹狀結構、頁面 (PAGE) 或資料列層級 (RID、KEY) 鎖定，都會被釋放。 若無法取得完全鎖定，則屆時不會發生任何鎖定擴大，但資料庫引擎會繼續試圖取得資料列、索引鍵或頁面鎖定。

資料庫引擎不會將資料列或索引鍵範圍鎖定擴大為頁面鎖定，但會直接將其擴大為資料表鎖定。 同樣地，頁面鎖定一律會擴大為資料表鎖定。 可針對相關聯資料分割將資料分割資料表的鎖定擴大到 HoBT 層級，而不是擴大到資料表鎖定。 HoBT 層級的鎖定不一定會針對此資料分割鎖定對齊的 HoBT。

若鎖定擴大的嘗試因並行交易所保有的鎖定衝突而失敗，資料庫引擎會在交易每多取得 1,250 個鎖定時重新嘗試鎖定擴大。

每個擴大事件主要會在單一 Transact-SQL 陳述式的層級上運作。 當事件開始時，資料庫引擎會試圖針對作用中陳述式所參考任何資料表中的現行交易來擴大其所擁有全部鎖定，前提是該陳述式符合擴大臨界值需求。 若擴大事件在陳述式存取資料表前即已開始，則不會嘗試擴大該資料表的鎖定。 若鎖定擴大成功，則由先前陳述式中的交易取得且在事件開始時仍保有的所有鎖定，都將會擴大，只要現行陳述式參考了該資料表，且該資料表包含在擴大事件中即可。

舉例而言，假設某個工作階段執行了下列作業：

• 開始交易。
• 更新 TableA。 此作業會在 TableA 中產生獨佔資料列鎖定，並保有鎖定到交易完成為止。
• 更新 TableB。 此作業會在 TableB 中產生獨佔資料列鎖定，並保有鎖定到交易完成為止。
• 執行將 TableA 與 TableC 聯結的 SELECT。 查詢執行計畫要求先從 TableA 擷取資料列，再從 TableC 擷取資料列。
• SELECT 陳述式會在其從 TableA 擷取資料列之際且在其存取 TableC 之前，觸發鎖定擴大。

若鎖定擴大成功，則只有 TableA 上的工作階段所保有的鎖定會擴大。 其中包括 SELECT 陳述式的共用鎖定與先前 UPDATE 陳述式的獨佔鎖定。 雖然只有工作階段在 TableA 中針對 SELECT 陳述式所取得鎖定是決定應否執行鎖定擴大的考量依據，但一旦擴大成功，則工作階段在 TableA 中所保有全部鎖定都會擴大為資料表的獨佔鎖定，而 TableA 上所有其他較低資料粒度的鎖定 (包括意圖鎖定) 都會獲得釋放。

對 TableB 則不會有任何擴大鎖定的嘗試，因為在 SELECT 陳述式中並沒有對 TableB 的使用中參考。 同樣地，對 TableC 也不會有任何擴大鎖定的嘗試，因為在發生擴大時尚未進行存取。

鎖定擴大且使用最佳化鎖定

最佳化鎖定有助於減少鎖定記憶體，因為在交易期間只會佔用非常少的鎖定。 由於資料庫引擎會取得資料列和頁面鎖定，可能會發生類似的鎖定擴大，但頻率大幅降低。 最佳化鎖定通常會可有效避免鎖定擴大，降低所需的鎖定數目和鎖定記憶體數量。

啟用最佳化鎖定時，在預設的 READ COMMITTED 隔離等級中，資料庫引擎會在資料列修改後立即釋放資料列和頁面鎖定。 除了單一交易識別碼 (TID) 鎖定之外，交易期間不會保留任何資料列和頁面鎖定。 這可降低鎖定擴大的可能性。

鎖定擴大閾值

當未使用 ALTER TABLE SET LOCK_ESCALATION 選項在資料表上停用鎖定擴大，且下列其中一個條件成立時，就會觸發鎖定擴大：

• 單一 Transact-SQL 陳述式在單一非資料分割資料表或索引上取得至少 5,000 個鎖定。
• 單一 Transact-SQL 陳述式在資料分割資料表的單一資料分割區上取得至少 5,000 個鎖定，且 ALTER TABLE SET LOCK_ESCALATION 選項設定為 AUTO。
• SQL Server 資料庫引擎執行個體中的鎖定數目超過記憶體或組態閾值。

如果因為鎖定衝突而無法擴大鎖定，資料庫引擎會在每取得 1,250 個新鎖定時，定期觸發鎖定擴大。

Transact-SQL 陳述式的擴大閾值

資料庫引擎每取得 1,250 個新鎖定時會檢查是否需要擴大，只有當 Transact-SQL 陳述式在資料表的單一參考上至少取得 5,000 鎖定時，才會發生鎖定擴大。 當 Transact-SQL 在資料表的單一參考上取得至少 5,000 個鎖定時，即會觸發鎖定擴大。 例如，若陳述式對單一索引取得 3,000 個鎖定，並且對相同資料表中的另一個索引也取得 3,000 個鎖定，則不會觸發鎖定擴大。 同樣地，若陳述式具有資料表的自我聯結，而且資料表的每個參考只取得 3,000 個資料表的鎖定，則不會觸發鎖定擴大。

只有在擴大觸發時被存取的資料表，才會發生鎖定擴大。 假設有個單一 SELECT 陳述式是按照此順序存取三個資料表的聯結：TableA、TableB 和 TableC。 此陳述式對 TableA 的叢集索引取得了 3,000 個資料列鎖定，對 TableB 的叢集索引取得了至少 5,000 個資料列鎖定，但尚未存取 TableC。 當資料庫引擎偵測到此陳述式已取得 TableB 中至少 5,000 個資料列鎖定時，即會試圖擴大 TableB 上目前交易所保留的所有鎖定。 其也會試圖擴大 TableA 中目前交易所保留的全部鎖定，但由於 TableA 的鎖定數目小於 5000，因此擴大將不會成功。 對 TableC 則不會嘗試進行鎖定擴大，因為在擴大發生時尚未存取該資料表。

資料庫引擎執行個體的擴大閾值

每當鎖定數目大於鎖定擴大的記憶體閾值時，資料庫引擎即會觸發鎖定擴大。 記憶體閾值取決於鎖定組態選項的設定：

• 若 locks 選項設為預設值 0，則在鎖定物件所用記憶體為資料庫引擎所用記憶體的 24% (AWE 記憶體除外) 時，就會達到鎖定擴大閾值。 用來表示鎖定的資料結構大約為 100 位元組的長度。 這個閾值是動態的，因為資料庫引擎會動態取得及釋放記憶體，以便針對隨時變動的工作負載進行調整。

• 若 locks 選項設為 0 以外的值，則鎖定擴大閾值為鎖定選項值的 40% (若記憶體不足則較低)。

資料庫引擎可從任何工作階段中選擇任何作用中陳述式進行擴大，且每產生 1,250 個新鎖定，就會選擇陳述式進行擴大，只要執行個體中所用的鎖定記憶體保持在閾值以上即可。

混合鎖定類型的鎖定擴大

發生鎖定擴大時，針對堆積或索引而選取的鎖定足以符合最嚴格的低層級鎖定需求。

例如，假設有某個工作階段：

• 開始交易。
• 更新含有叢集索引的資料表。
• 發出參考相同資料表的 SELECT 陳述式。

UPDATE 陳述式會取得下列鎖定：

• 對於更新後資料列的獨佔 (X) 鎖定。
• 對於含有這些資料列之叢集索引頁面的意圖獨佔 (IX) 鎖定。
• 對於叢集索引的 IX 鎖定以及對資料表的其他鎖定。

SELECT 陳述式會取得下列鎖定：

• 它所讀取之所有資料列的共用 (S) 鎖定，已由 UPDATE 陳述式的 X 鎖定所保護的資料列則除外。
• 含有這些資料列之所有叢集索引頁面的意圖共用 (IS) 鎖定，已由 IX 鎖定所保護的頁面則除外。
• 不會取得叢集索引或資料表的鎖定，因為它們已由 IX 鎖定所保護。

若 SELECT 陳述式取得足夠的鎖定可觸發鎖定擴大，且擴大成功的話，則資料表的 IX 鎖定會轉換為 X 鎖定，且會釋出所有資料列、頁面與索引鎖定。 資料表的更新與讀取都會受到 X 鎖定所保護。

減少鎖定與鎖定擴大

在大多數的情況下，資料庫引擎以其鎖定與鎖定擴大的預設值來運作時會展現最佳效能。

• 運用最佳化鎖定的優勢。

  • 最佳化鎖定可提供更完善的交易鎖定機制，可減少鎖定記憶體耗用量並阻止並行交易。 啟用最佳化鎖定時，不太容易發生鎖定擴大。
  • 請避免將資料表提示搭配最佳化鎖定使用。 資料表提示可能會降低最佳化鎖定的有效性。
  • 在資料庫中啟用 READ_COMMITTED_SNAPSHOT 選項以充分發揮最佳化鎖定的效益。 這是 Azure SQL Database 中的預設。
  • 最佳化鎖定需要在資料庫上啟用加速資料庫復原 (ADR)。

若資料庫引擎執行個體產生了大量鎖定，且經常發生鎖定擴大，請考慮透過下列策略減少鎖定的數量：

• 使用不會對讀取作業產生共用鎖定的隔離等級：

  • 當 READ_COMMITTED_SNAPSHOT 資料庫選項為 ON 時，為 READ COMMITTED 隔離等級。
  • SNAPSHOT 隔離等級。
  • READ UNCOMMITTED 隔離等級。 只有能夠採取中途讀取的系統才可使用此等級。

• 使用 PAGLOCK 或 TABLOCK 資料表提示，讓資料庫引擎使用分頁、堆積或索引鎖定，而非低等級鎖定。 然而，使用這個選項會增加使用者阻止其他使用者嘗試存取相同資料的問題，因此只應在具有較多並行使用者的系統上使用。

• 如果沒有最佳化的鎖定，對於資料分割資料表，請使用 ALTER TABLE 的 LOCK_ESCALATION 選項，將鎖升級到分割區而不是資料表，或停用資料表的鎖升級。

• 將大型的批次作業分成較小作業。 例如，假設您執行了下列查詢來從稽核資料表移除數十萬個舊資料列，而您發現這項作業造成了封鎖其他使用者的鎖定擴大：

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  藉由一次移除其中數百個資料列，您可大幅降低每次交易累積的鎖定數，並防止鎖定擴大。 例如：

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• 透過盡可能地提升查詢效率以減少查詢的鎖定磁碟使用量。 大型掃描或大量索引鍵查詢可能會增加鎖定擴大的機會；此外，這樣做也會增加死結的機會，且一般會對並行及效能造成負面影響。 在找出造成鎖定擴大的查詢後，請尋找建立新索引或將資料行新增到現有索引的機會，以移除完整索引或資料表掃描來最大化索引搜尋的效率。 請考慮使用 Database Engine Tuning Advisor 來針對查詢執行自動索引分析。 如需詳細資訊，請參閱教學課程：Database Engine Tuning Advisor。 這項最佳化的目標是讓索引搜尋盡可能地傳回較少資料列，以最小化索引鍵查詢的成本 (最大化特定查詢的索引選擇性)。 若資料庫引擎估計索引鍵查詢邏輯運算子可能會傳回許多資料列，其可能會使用 PREFETCH 來執行書籤查閱。 若資料庫引擎使用擷取進行查詢，其必須增加一部分查詢的交易隔離等級至 REPEATABLE READ。 這表示看起來與 READ COMMITTED 隔離等級 SELECT 陳述式相似的陳述式可能需要數千個索引鍵鎖定 (同時針對叢集索引和一個非叢集索引)，而這可能會造成這類查詢超過隔離擴大的閾值。 若發現擴大的鎖定是共用資料表鎖定，這便特別重要，雖然這在預設的 READ COMMITTED 隔離等級中並不常見。

  若含擷取索引鍵查閱最佳化正造成鎖定擴大，請考慮將額外的資料行新增至出現在查詢計劃中書籤查閱邏輯運算子底下索引搜尋或索引搜尋邏輯運算子中的非叢集索引。 您可能可建立涵蓋索引 (包含查詢中所用資料表中所有資料欄的索引)，或是在包含 SELECT 資料欄清單中的所有項目並不實際時，至少建立可涵蓋用於聯結準則或 WHERE 子句中資料欄的索引。 巢狀迴圈聯結也可能會使用擷取最佳化，而這會造成相同的鎖定行為。

• 若有不同 SPID 正在保有不相容的資料表鎖定，鎖定擴大便無法發生。 鎖定擴大一律會擴大至資料表鎖定，且永遠不會擴大至頁面鎖定。 此外，若鎖定擴大嘗試因另外一個 SPID 保有不相容的資料表鎖定而失敗，則嘗試該擴大的查詢便不會在等待資料表鎖定時進行封鎖。 相反地，其會繼續在其更細微的原始層級 (資料列、索引鍵或分頁) 取得鎖定，並定期進行其他擴大嘗試。 因此，其中一種防止在特定資料表上鎖定擴大的方法便是取得並保留與擴大鎖定類型不相容的不同連線鎖定。 資料表層級的意圖獨佔 (IX) 鎖定不會鎖定任何資料列或分頁，但其仍然與擴大的共用 (S) 或獨佔 (X) 資料表鎖定不相容。 例如，假設您必須執行修改 mytable 資料表中大量資料列的批次作業，而這項作業造成了因鎖定擴大而發生的封鎖。 若這項作業一律會在一個小時內完成，則可建立包含下列程式碼的 Transact-SQL 作業，並排程新作業在批次任務啟動時間的前幾分鐘開始：

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  這項查詢會取得並保有 mytable 的 IX 鎖定一個小時，其會防止在這段時間裡對資料表進行鎖定擴大。 此批次不會修改任何資料或封鎖其他查詢 (除非其他查詢使用 TABLOCK 提示來實施資料表鎖定，或系統管理員使用 mytable 索引來停用了頁面或資料列鎖定)。

• 您也可以使用追蹤旗標 1211 與 1224 來停用所有或部分的鎖定擴大。 但是，這些追蹤旗標會全域停用整個資料庫引擎執行個體的所有鎖定擴大。 鎖定擴大在資料庫引擎中非常實用，能夠使查詢效率最大化，避免因取得和釋放數千個鎖定所帶來的額外負荷而導致效率低落。 鎖定擴大也有助於將追蹤鎖定所需要的記憶體降至最低。 資料庫引擎可為鎖定結構動態配置的記憶體有限，因此若停用鎖定擴大，當鎖定記憶體增長到一定大小時，嘗試為任何查詢配置額外的鎖定便可能會失敗，並發生下列錯誤：Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  注意

  當發生 MSSQLSERVER_1204 錯誤時，其會停止處理目前的陳述式，並造成使用中交易的復原。 若重新啟動資料庫服務，則復原本身可能會封鎖使用者或導致漫長的復原時間。

  注意

  使用 ROWLOCK 等鎖定提示只會改變初始的鎖定取得。 鎖定提示不會防止鎖定擴大。

從 SQL Server 2008 (10.0.x) 開始，鎖定擴大行為已隨著 LOCK_ESCALATION 資料表選項的導入而變更。 如需詳細資訊，請參閱 ALTER TABLE的 LOCK_ESCALATION 選項。

監視鎖定擴大

請使用 lock_escalation 延伸事件來監視鎖定擴大，如下列範例所示：

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

動態鎖定

使用像資料列鎖定等低層級鎖定，可藉由降低兩個交易同時要求相同片段的資料鎖定之可能性來增加並行。 使用低層級鎖定也會增加鎖定的數目以及需要管理鎖定的資源。 使用高層級的資料表或頁面鎖定可降低額外負荷，但必須花費降低並行的成本。

資料庫引擎使用動態鎖定策略來判斷最具有效的鎖定。 資料庫引擎在執行查詢時，將依照結構描述與查詢的特性，自動決定最合適的鎖定類型。 例如，為了降低鎖定的額外負荷，最佳化工具在進行索引掃描時可能會選擇頁面的鎖定。

鎖定資料分割

對於大型電腦系統而言，鎖定經常參考的物件將會形成效能瓶頸，這是因為鎖定的取得與釋放，將會在內部鎖定資源上引發競爭問題。 鎖定資料分割可將單一鎖定資源分割成多個鎖定資源，進而增強鎖定效能。 這項功能僅適用於擁有 16 顆以上邏輯 CPU 的系統，而且它會自動啟用，而不能停用。 只有物件鎖定可以被分割。 具有子類型的物件鎖定不能被分割。 如需詳細資訊，請參閱 sys.dm_tran_locks (Transact-SQL)。

認識鎖定資料分割

鎖定工作會存取數項共用資源，其中兩項資源會因鎖定資料分割而最佳化：

• Spinlock

  這個資源控制對鎖定資源 (例如資料列或資料表) 的存取。

  若沒有鎖定資料分割，便會由單一執行緒存取鎖，管理所有鎖定要求的單一鎖定資源。 在經歷大量活動的系統上，因為有許多鎖定要求等候單一執行緒存取鎖變成可用狀態，所以會發生競爭問題。 在這種情況下，取得鎖定將會形成瓶頸，進而對效能產生負面的影響。

  為了減少單一鎖定資源上的競爭問題，鎖定資料分割會把單一鎖定資源分割成多個鎖定資源，以將負載分散到多個單一執行緒存取鎖。

• 記憶體

  這個資源用來儲存鎖定資源結構。

  取得單一執行緒存取鎖之後，鎖定結構就會儲存在記憶體中以待存取，甚至修改。 將鎖定存取分散到多個資源，有助於省去在 CPU 之間傳輸記憶體區塊的需要，而這有利於改善效能。

實作和監視鎖定資料分割

在擁有 16 顆以上 CPU 的系統中，鎖定資料分割預設會開啟。 啟用鎖定資料分割時，SQL Server 錯誤記錄檔中會記錄參考訊息。

在資料分割資源上取得鎖定時：

• 只有 NL、Sch-S、IS、IU 和 IX 鎖定模式是在單一資料分割上取得。

• 在非 NL、Sch-S、IS、IU 和 IX 的模式下，所有資料分割上都必須取得共用 (S)、獨佔 (X) 和其他鎖定，順序為先在資料分割區識別碼為 0 的資料分割上開始取得，接著再依照識別碼順序在其他資料分割上取得。 這些在資料分割資源上的鎖定將比相同模式下的非資料分割資源上的鎖定，佔用更多的記憶體，這是因為每個資料分割實際上都是一個不同的鎖定。 記憶體的增加取決於資料分割的數目。 SQL Server 鎖定效能計數器將會顯示有關資料分割鎖定和非資料分割鎖定所佔用記憶體的資訊。

啟動交易時，系統會將交易指派給資料分割。 對於交易而言，所有可以分割的鎖定要求都會使用指派給該交易的資料分割。 使用這個方法，不同交易對同一個物件的鎖定資源的存取，都會分散到不同的資料分割。

sys.dm_tran_locks 動態管理檢視中的 resource_lock_partition 資料行會提供鎖定資料分割資源的鎖定資料分割區識別碼。 如需詳細資訊，請參閱 sys.dm_tran_locks (Transact-SQL)。

使用鎖定資料分割

下列程式碼範例說明了鎖定資料分割。 在這些範例中，會在兩個不同的工作階段中各執行一個交易，以便顯示在具有 16 顆 CPU 之電腦系統上的鎖定資料分割行為。

下列 Transact-SQL 陳述式會建立稍後範例中將會使用的測試物件。

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

範例 A

工作階段 1：

SELECT 陳述式會在交易之下執行。 因為有 HOLDLOCK 鎖定提示，所以這個陳述式將在資料表上取得及保留意圖共用 (IS) 鎖定 (在這個範例中，將忽略資料列和頁面鎖定)。 只有在指派給交易的資料分割上能取得 IS 鎖定。 在這個範例中，假設是在分割區識別碼 7 上取得 IS 鎖定。

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

工作階段 2：

啟動交易，在這個交易之下執行的 SELECT 陳述式將在資料表上取得及保留共用 (S) 鎖定。 所有資料分割上都能取得 S 鎖定，而這會造成多個資料表鎖定，每一個都是針對一個資料分割。 例如在 16 個 CPU 的系統上，將會橫跨鎖定資料分割區識別碼 0-15 來發出 16 S 鎖定。 因為 S 鎖定與資料分割區識別碼 7 上由工作階段 1 中之交易所持有的 IS 鎖定相容，所以交易之間不會發生封鎖現象。

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

工作階段 1：

下列 SELECT 陳述式會在工作階段 1 中仍在使用中的交易下執行。 因為有獨佔 (X) 資料表鎖定提示，所以交易將會嘗試取得資料表上的 X 鎖定。 不過，由工作階段 2 中之交易所持有的 S 鎖定，將會封鎖資料分割區識別碼 0 的 X 鎖定。

SELECT col1
FROM TestTable
WITH (TABLOCKX);

範例 B

工作階段 1：

SELECT 陳述式會在交易之下執行。 因為有 HOLDLOCK 鎖定提示，所以這個陳述式將在資料表上取得及保留意圖共用 (IS) 鎖定 (在這個範例中，將忽略資料列和頁面鎖定)。 只有在指派給交易的資料分割上能取得 IS 鎖定。 在這個範例中，假設是在分割區識別碼 6 上取得 IS 鎖定。

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

工作階段 2：

SELECT 陳述式會在交易之下執行。 因為有 TABLOCKX 鎖定提示，所以交易會嘗試在資料表上取得獨佔 (X) 鎖定。 請記住，必須在資料分割區識別碼 0 開頭的所有資料分割上取得 X 鎖定。 從資料分割識別碼 0 到 5 的所有資料分割上都會取得 X 鎖定，但是在資料分割區識別碼 6 上取得的鎖定將會遭到 IS 鎖定封鎖。

其他交易可以在 X 鎖定還沒到達的資料分割區識別碼 7 到 15 上繼續取得鎖定。

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

資料庫引擎中的資料列版本設定式隔離等級

從 SQL Server 2005 (9.x) 開始，資料庫引擎引進了現有交易隔離等級 (READ COMMITTED) 的實作，透過使用資料列版本設定以提供陳述式層級的快照。 資料庫引擎另還引進一種交易隔離等級稱為 SNAPSHOT，同樣是使用資料列版本設定但提供交易層級的快照。

資料列版本設定是 SQL Server 中的一般架構，會在資料列遭修改或刪除的情況下叫用寫入時複製機制。 為此，當交易正在執行時，舊版本的資料列務必可供仍需要早先交易一致狀態的交易使用。 資料列版本設定用於實施下列功能：

• 在觸發程式中組建 inserted 和 deleted 資料表。 經由觸發程序修改過的任何資料列都會被建立版本。 這包括啟動觸發程序之陳述式所修改的資料列，以及觸發程序所做的任何資料修改。
• 支援 Multiple Active Result Sets (MARS)。 如果 MARS 工作階段在有作用中結果集的情況下，發出資料修改陳述式 (例如 INSERTUPDATE或 DELETE)，就會為修改陳述式所影響的資料列建立版本。
• 支援指定 ONLINE 選項的索引作業。
• 支援以資料列版本設定為基礎的交易隔離等級：
  • 新的 READ COMMITTED 隔離等級實作方式，使用資料列版本設定來提供陳述式層級的讀取一致性。
  • 新的隔離等級 (SNAPSHOT)，可以提供交易等級的讀取一致性。

資料列版本設定會儲存在版本存放區中。 如果在資料庫上啟用加速資料庫復原，則會在該資料庫中建立版本存放區。 否則，會在 tempdb 資料庫中建立版本存放區。

資料庫必須要有足夠的空間供版本存放區使用。 當版本存放庫在 tempdb，和 tempdb 資料庫已滿時，更新作業會停止產生版本並繼續執行成功，但讀取作業可能會失敗，因為所需的特定資料列版本已不存在。 這會影響到像是觸發程序、MARS 及線上檢索索引之類的作業。

當使用加速資料庫復原且版本存放庫已滿時，讀取作業會繼續成功，但產生版本 (例如 UPDATE 和 DELETE) 的寫入作業則會失敗。 如果資料庫有足夠的空間，INSERT 作業會繼續成功。

針對 READ COMMITTED 及 SNAPSHOT 交易，使用資料列版本設定的方法是兩個步驟的程序：

1. 將 READ_COMMITTED_SNAPSHOT 和 ALLOW_SNAPSHOT_ISOLATION 資料庫選項其中之一或兩者都設定為 ON。

2. 在應用程式中設定適當的交易隔離等級：

   • 當 READ_COMMITTED_SNAPSHOT 資料庫選項為 ON 時，設定讀取 READ COMMITTED 等級的交易就會使用資料列版本設定。
   • 當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項為 ON 時，交易便可設定 SNAPSHOT 隔離等級。

當 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 時，資料庫引擎會使用資料列版本設定，為每個操作資料的交易都指派一個交易序號 (XSN)。 交易會在執行 BEGIN TRANSACTION 陳述式時開始。 但是交易序號會從 BEGIN TRANSACTION 陳述式之後的第一個讀取或寫入作業開始。 每指定一個交易序號，就會累加一個號碼。

當 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 其中一個資料庫選項設為 ON 時，就會針對在資料庫中執行的所有資料修改來維護邏輯複本 (版本)。 每次特定交易修改資料列時，資料庫引擎執行個體都會在版本存放庫中儲存先前認可的資料列映像版本。 每個版本都會標示執行變更之交易的交易序號。 修改過的資料列版本會以連結清單鏈結起來。 最新的資料列值一律儲存在目前的資料庫中，並鏈結到儲存在版本存放庫中已設定版本的資料列。

資料列版本會被保存夠久的時間，可滿足以資料列版本設定為基礎之隔離等級來執行的交易需求。 資料庫引擎會追蹤最早的有用交易序號，並定期刪除交易序號低於最早有用交易序號的所有資料列版本。

當兩個資料庫選項都設為 OFF 時，就只會針對由觸發程序或 MARS 工作階段所修改的資料列，或是由線上索引作業所讀取的資料列來建立版本。 不再需要那些資料列版本時，就會將其釋出。 背景處理會移除過時的資料列版本設定。

讀取資料時的行為

在以資料列版本設定為基礎之隔離下執行的交易要讀取資料時，讀取作業不會在所讀取的資料上取得共用 (S) 鎖定，因此不會阻礙正在修改資料的交易。 此外，隨著所取得的鎖定數量減少，鎖定資源的額外負荷也會降低。 使用資料列版本設定及 SNAPSHOT 隔離的 READ COMMITTED 隔離作業，目的就是要提供已建立版本之資料的陳述式層級或交易等級讀取一致性。

所有的查詢，包括在資料列版本設定式隔離等級下執行的交易，都會在編譯和執行期間取得結構描述穩定性 (Sch-S) 鎖定。 因此，當並行交易在資料表上保有結構描述修改 (Sch-M) 鎖定時，查詢將會遭到封鎖。 例如，資料定義語言 (DDL) 作業會在修改資料表的結構描述資訊之前先取得 Sch-M 鎖定。 交易，包括在資料列版本設定式隔離等級下執行的交易，在嘗試取得 Sch-S 鎖定時都會遭到封鎖。 相反地，保有 Sch-S 鎖定的查詢將會封鎖嘗試取得 Sch-M 鎖定的並行交易。

當使用 SNAPSHOT 隔離等級的交易開始時，資料庫引擎的執行個體會記錄目前所有的使用中交易。 當 SNAPSHOT 交易讀取具有版本鏈結的資料列時，資料庫引擎會依循該鏈結，並擷取下列交易序號的資料列：

• 最接近但小於讀取該資料列之快照交易的序號。

• 當快照交易開始時，不在使用中交易清單裡的序號。

SNAPSHOT 交易所執行的讀取作業會在 SNAPSHOT 交易開始時，擷取已認可之每個資料列的最新版本。 這樣可以讓資料快照集在交易期間保持一致，就像交易一開始的資料一樣。

使用資料列版本設定的 READ COMMITTED 交易運作的方式也相當類似。 不同的地方在於，READ COMMITTED 交易在選擇資料列版本時，不會使用自己的交易序號。 每當有陳述式開始時，READ COMMITTED 交易就會讀取針對該資料庫引擎執行個體所發出的最新交易序號。 這個交易序號可用來為該陳述式選取資料列版本。 這樣可以讓 READ COMMITTED 交易看到與每個陳述式開始時相同的資料快照。

修改資料時的行為

是否啟用最佳化鎖定會大幅改變資料的寫入行為。

未使用最佳化鎖定來修改資料

在使用資料列版本設定的 READ COMMITTED 交易中，會使用封鎖掃描來選取要更新的資料列，在封鎖掃描中，當資料值被讀取時，就會在資料列上取得更新 (U) 鎖定。 這與不使用資料列版本設定的 READ COMMITTED 交易是一樣的。 如果資料列不符合更新條件，就會解除該資料列的更新鎖定，並且會鎖定及掃描下一個資料列。

在 SNAPSHOT 隔離下執行的交易會使用開放式的方法來修改資料，在修改資料之前，會在資料上取得鎖定，只強制執行條件約束。 否則，在修改資料之前，不會在資料上取得鎖定。 當資料列符合更新條件，SNAPSHOT 交易會確認資料列尚未被 SNAPSHOT 交易開始之後認可的並行交易所修改。 如果資料列已在 SNAPSHOT 交易外被修改，就會發生更新衝突，並終止 SNAPSHOT 交易。 資料庫引擎會處理更新衝突，而且沒有可以停用更新衝突偵測的方法。

使用最佳化鎖定來修改資料

在啟用最佳化鎖定以及 READ_COMMITTED_SNAPSHOT (RCSI) 資料庫選項，且使用預設 READ COMMITTED 隔離等級時，讀取器不會取得任何鎖定，而且寫入器會取得短期的低等級鎖定，而不是在交易結束時到期的鎖定。

為發揮最佳化鎖定的最高效率，建議啟用 RCSI。 若使用更嚴格的隔離等級 (例如 REPEATABLE READ 或 SERIALIZABLE)，資料庫引擎會針對讀取器和寫入器佔用資料列和頁面鎖定，直到交易結束為止，這會造成封鎖和鎖定記憶體增加。

若啟用 RCSI 並使用預設 READ COMMITTED 隔離等級，寫入器會根據資料列的最新認可版本來限定每個述詞的資料列，而無需取得 U 鎖定。 只有在資料列符合資格，且該資料列或頁面上有另一個作用中的寫入交易時，查詢才會等待。 根據最新的認可版本來限定資格，並只鎖定符合條件的資料列，可減少封鎖並增加並行。

如果在 RCSI 和預設 READ COMMITTED 隔離等級中偵測到更新衝突，系統會自動處理並重試這些衝突，而不會對客戶工作負載造成任何影響。

啟用最佳化鎖定且使用 SNAPSHOT 隔離等級時，更新衝突的行為與未啟用最佳化鎖定時相同。 應用程式必須處理並重試更新衝突。

行為摘要

下表摘要說明使用資料列版本設定的 SNAPSHOT 隔離與 READ COMMITTED 隔離差異。

屬性	使用資料列版本設定的 READ COMMITTED 隔離等級	SNAPSHOT 隔離等級
資料庫選項必須設為 ON，才能啟用必要的支援。	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
工作階段如何要求特定類型的資料列版本設定。	使用預設 READ COMMITTED 隔離等級，或執行 SET TRANSACTION ISOLATION LEVEL 陳述式來指定 READ COMMITTED 隔離等級。 您可以在交易開始後完成此作業。	需要執行 SET TRANSACTION ISOLATION LEVEL，以在交易開始之前指定 SNAPSHOT 隔離等級。
陳述式所讀取的資料版本。	在每個陳述式開始之前認可的所有資料。	在每個交易開始之前認可的所有資料。
如何處理更新。	使用最佳化鎖定：從資料列版本還原成實際的資料，以選取要更新的資料列，並且在所選取的資料列上使用更新鎖定。 在所要修改的實際資料列上取得獨佔鎖定。 無更新衝突偵測。 使用最佳化鎖定：系統會根據上次認可的版本選取資料列，而不會取得任何鎖定。 如果資料列符合更新資格，則會取得獨佔資料列或頁面鎖定。 如果偵測到更新衝突，系統會自動處理並重試這些衝突。	使用資料列版本來選取要更新的資料列。 嘗試在所要修改的實際資料列上取得獨佔鎖定，若該資料已被其他交易所修改，就會發生更新衝突，且快照交易會終止進行。
更新衝突偵測	不使用最佳化鎖定：無。 使用最佳化鎖定：如果偵測到更新衝突，系統會自動處理並重試這些衝突。	整合支援。 無法停用。

資料列版本設定資源的使用方式

資料列版本設定架構支援下列資料庫引擎功能：

• 觸發程序
• Multiple Active Result Set (MARS)
• 線上檢索索引

資料列版本設定架構也支援下列資料列版本設定式的交易隔離等級：

• 當 READ_COMMITTED_SNAPSHOT 資料庫選項為 ON 時，READ_COMMITTED 交易會使用資料列版本設定，來提供陳述式層級讀取一致性。
• 當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項為 ON 時，SNAPSHOT 交易會使用資料列版本設定，來提供交易層級讀取一致性。

資料列版本設定式的隔離等級因為不需對讀取作業使用共用鎖定，因而減少交易所取得的鎖定數。 如此可減少用來管理鎖定的資源，進而增加系統效能。 減少交易被其他交易所取得的鎖定封鎖的次數，也可以增加效能。

資料列版本設定式的隔離等級會增加資料修改所需的資源。 啟用這些選項會使資料庫的所有資料修改建立版本。 即使沒有使用資料列版本設定式隔離的作用中交易，修改前的資料副本也會儲存在版本存放庫中。 修改後的資料包括指向版本存放區中已版本化資料的指標。 對於大型物件，只有變更的部分物件會儲存在版本存放庫中。

tempdb 中使用的空間

對於每個資料庫引擎的執行個體，版本存放區必須有足夠的空間來存放資料列版本設定。 資料庫管理員必須確保 tempdb 和其他資料庫 (如果已啟用加速資料庫復原)，有足夠的空間來支援版本存放區。 此處有兩個版本存放庫類型：

• 線上索引組建版本存放區會用於線上索引組建。
• 一般版本存放區，用於所有其他資料修改作業。

資料列版本儲存的時間必須夠久，讓使用中交易可以存取它們。 背景執行緒會定期移除不再需要的資料列版本，並釋放版本存放區的空間。 長時間執行的交易如果符合下列任何條件，就可以阻止釋放版本存放區的空間。

• 它使用資料列版本設定式的隔離。
• 它使用觸發程序、MARS 或線上索引組建作業。
• 它產生資料列版本。

如果版本存放庫在 tempdb 中，當 tempdb 空間不夠時，資料庫引擎會強制版本存放區進行壓縮。 在壓縮處理期間，執行最久但尚未產生資料列版本的交易會標示為犧牲者。 在錯誤記錄檔中會針對每一筆犧牲者交易產生訊息 3967。 如果交易已標示為犧牲者，它就不能再讀取版本存放區中的資料列版本。 當它嘗試讀取資料列版本時，會產生訊息 3966 而且會回復交易。 如果壓縮處理成功，tempdb 中的空間就會變成可用。 否則，tempdb 的空間會不足，而且會發生下列情況：

• 寫入作業繼續執行，但不產生版本。 在錯誤記錄檔中會出現資訊訊息 (3959)，但寫入資料的交易不受影響。

• 嘗試存取因 tempdb 完全復原而未產生的資料列版本的交易，會以錯誤 3958 終止。

資料列中使用的空間

每個資料庫的資料列在資料列結尾可使用最多 14 個位元組，以存放資料列版本設定資訊。 資料列版本設定資訊包含認可版本之交易的交易序號，以及版本化資料列的指標。 這 14 個位元組會在第一次修改資料列或插入新的資料列時，而且符合下列任一情況時加入：

• READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 選項會設定為 ON。
• 資料表有觸發程序。
• 使用 Multiple Active Result Set (MARS)。
• 目前正在資料表執行線上索引組建作業。

如果版本存放庫在 tempdb 中，在下列這些情況下，第一次修改資料列時會從資料庫中移除這 14 個位元組：

• READ_COMMITTED_SNAPSHOT 與 ALLOW_SNAPSHOT_ISOLATION 選項會設定為 OFF。
• 觸發程序已不在資料表上。
• 不使用 MARS。
• 目前沒有執行中的線上索引建立作業。

如果不再啟用加速資料庫復原，且符合上述條件，則會在修改資料列時移除 14 個字節。

如果您使用任何資料列版本設定功能，您可能需要配置額外的磁碟空間給資料庫，以容納每個資料庫資料列的 14 個位元組。 如果目前頁面沒有足夠的可用空間，則加入資料列版本設定資訊會造成索引頁面分割或需要配置新的資料頁。 例如，如果平均資料列長度是 100 個位元組，則額外的 14 個位元組會造成現有資料表成長高達百分之 14。

降低填滿因數可能有助於防止或減少索引頁片段。 若要檢視資料表或檢視表之資料與索引的目前頁面密度資訊，您可以使用 sys.dm_db_index_physical_stats。

大型物件中使用的空間

資料庫引擎支援六種資料類型，可保留長度多達 2 十億位元組 (GB) 的大型字串，如：nvarchar(max)、varchar(max)、varbinary(max)、ntext、text 和 image。 使用這些資料類型儲存的大型資料是儲存在一系列資料片段中，而這些片段是連結到資料列。 資料列版本設定資訊是儲存在用來儲存這些大型字串的每一個片段中。 資料片段儲存在資料表中專門用於大型物件的一組頁面中。

當新的大型值加入至資料庫時，會使用每個片段最多 8040 個位元組的資料來配置它們。 舊版的資料庫引擎中，每個片段儲存最多 8080 個位元組的 ntext、text 或 image 資料。

當資料庫從舊版的 SQL Server 升級至 SQL Server 時，並不會更新現有的 ntext、text 和 image 大型物件 (LOB) 資料來提供存放資料列版本設定資訊的空間。 不過，第一次修改 LOB 資料時，它會動態升級，以啟用版本控制資訊的儲存。 即使未產生資料列版本也會發生此情況。 當 LOB 資料升級之後，每個片段儲存的最大位元組數會從 8080 個位元組降到 8040 個位元組。 升級程序相當於刪除 LOB 值及重新插入相同值。 即使只修改 1 個位元組，也會升級 LOB 資料。 每一個 ntext、text 或 image 資料行只有一次作業，但每一個作業可產生大量頁面配置和 I/O 活動，視 LOB 資料大小而定。 如果有完整記錄各項修改，則它也可能產生大量記錄活動。 如果資料庫復原模式未設為 FULL，則會為 WRITETEXT 和 UPDATETEXT 作業做最少的記錄。

應該配置足夠的磁碟空間來配合這項需求。

監視資料列版本設定和版本存放區

為了效能和問題而監視資料列版本設定、版本存放區和快照集隔離程序，資料庫引擎以動態管理檢視 (DMV) 的形式提供工具，以及提供效能計數器。

DMV

下列 DMV 提供關於 tempdb 和版本存放區的目前系統狀態，以及使用資料版本控制的交易資訊。

• sys.dm_db_file_space_usage. 傳回資料庫中每一個檔案的空間使用方式資訊。 如需詳細資訊，請參閱 sys.dm_db_file_space_usage (Transact-SQL)。

• sys.dm_db_session_space_usage. 由資料庫的工作階段傳回頁面配置和取消配置活動。 如需詳細資訊，請參閱 sys.dm_db_session_space_usage (Transact-SQL)。

• sys.dm_db_task_space_usage. 傳回資料庫工作的頁面配置及取消配置活動。 如需詳細資訊，請參閱 sys.dm_db_task_space_usage (Transact-SQL)。

• sys.dm_tran_top_version_generators. 針對產生版本存放區中大部分版本的物件，傳回一份虛擬資料表。 它按 database_id 和 rowset_id 來分組前 256 個彙總記錄長度。 使用此函數可尋找版本存放區的最大取用者。 僅適用於 tempdb 中的版本存放區。 如需詳細資訊，請參閱 sys.dm_tran_top_version_generators (Transact-SQL)。

• sys.dm_tran_version_store. 傳回虛擬資料表來顯示一般版本存放區中的所有版本記錄。 僅適用於 tempdb 中的版本存放區。 如需詳細資訊，請參閱 sys.dm_tran_version_store (Transact-SQL)。

• sys.dm_tran_version_store_space_usage. 傳回一個虛擬資料表，其中顯示每個資料庫之版本存放區記錄在 tempdb 中所使用的總空間。 僅適用於 tempdb 中的版本存放區。 如需詳細資訊，請參閱 sys.dm_tran_version_store_space_usage (Transact-SQL)。

  注意

  系統物件 sys.dm_tran_top_version_generators 和 sys.dm_tran_version_store 可能有很高的執行代價，因為兩者會查詢可能龐大的整個版本存放區。 而 tempdb 相當有效率且執行成本不高，因為該函數不會瀏覽個別的版本存放區記錄，而是會傳回每個資料庫在 sys.dm_tran_version_store_space_usage 中所使用的已彙總版本存放區空間。

• sys.dm_tran_active_snapshot_database_transactions. 在使用資料列版本設定的 SQL Server 執行個體內，傳回所有資料庫的所有使用中交易的虛擬資料表。 系統交易不會出現在這個 DMV 中。 如需詳細資訊，請參閱 sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)。

• sys.dm_tran_transactions_snapshot. 傳回虛擬資料表，以顯示每一筆交易所產生的快照集。 快照集包含使用了資料列版本設定之使用中交易的序號。 如需詳細資訊，請參閱 sys.dm_tran_transactions_snapshot (Transact-SQL)。

• sys.dm_tran_current_transaction. 傳回單一資料列，顯示目前工作階段中交易的資料列版本設定相關之狀態資訊。 如需詳細資訊，請參閱 sys.dm_tran_current_transaction (Transact-SQL)。

• sys.dm_tran_current_snapshot. 傳回虛擬資料表，以顯示目前快照集隔離交易啟動時的所有使用中交易。 如果目前的交易使用快照隔離，此函數不會傳回任何資料列。 DMV sys.dm_tran_current_snapshot 類似於 sys.dm_tran_transactions_snapshot，不過只會傳回目前快照集的作用中交易。 如需詳細資訊，請參閱 sys.dm_tran_current_snapshot (Transact-SQL)。

• sys.dm_tran_persistent_version_store_stats. 傳回啟用加速資料庫復原時所使用之每個資料庫中持續性版本存放區的統計資料。 如需詳細資訊，請參閱 sys.dm_tran_persistent_version_store_stats (Transact-SQL)。

效能計數器

下列效能計數器會監視 tempdb 鐘的版本存放區以及使用資料列版本設定的交易。 效能計數器包含在 SQLServer:Transactions 效能物件中。

• Free Space in tempdb (KB) 。 監視 tempdb 資料庫的可用空間量，以 KB 為單位。 tempdb 要有足夠的可用空間，才能處理支援快照集隔離的版本存放區。

  下列公式提供版本存放區大小的概估。 若為長時間執行的交易，則監視產生速率和清除速率以評估版本存放區的大小上限，可能會有幫助。

  [一般版本存放區的大小] = 2 * [每分鐘產生的版本存放區資料] * [交易的最長執行時間 (分鐘數)]

  交易的最長執行時間不應包括線上索引組建。 由於這些作業在非常大的資料表上會花很長的時間，線上索引組建會使用不同的版本存放區。 線上索引組建版本存放區的近似大小，等於啟動線上索引組建時資料表中修改的資料量，包括所有索引。

• Version Store Size (KB) 。 監視所有 tempdb 中版本存放區的大小，以 KB 為單位。 此資訊有助於決定版本存放區的 tempdb 資料庫所需要的空間量。 持續監視這個計數器一段時間，可對 tempdb 所需的其他空間提供有用的評估。

• Version Generation rate (KB/s)。 監視所有 tempdb 中版本存放區的版本產生速率 (以每秒 KB 數為單位)。

• Version Cleanup rate (KB/s)。 監視所有 tempdb 中版本存放區的版本清除速率 (以每秒 KB 數為單位)。

  注意

  Version Generation rate (KB/s) 和 Version Cleanup rate (KB/s) 的資訊可用來預測 tempdb 的空間需求。

• Version Store unit count。 監視版本存放區單元的計數。

• Version Store unit creation。 監視自執行個體啟動之後，為了儲存資料列版本而建立之版本存放區單元的總數。

• Version Store unit truncation。 監視自執行個體啟動之後，被截斷之版本存放區單元的總數。 當 SQL Server 判斷執行使用中交易時不再需要版本存放區單元中所儲存的任何版本資料列時，就會截斷版本存放區單元。

• Update conflict ratio。 監視在更新快照集交易總數中，發生更新衝突的更新快照集交易所佔比例。

• Longest Transaction Running Time。 監視使用資料列版本設定的任何交易的最長執行時間，以秒數為單位。 這可用來判斷是否有任何交易執行的時間量超出預期。

• Transactions。 監視使用中交易的總數。 這不包括系統交易。

• Snapshot Transactions。 監視使用中快照集交易的總數。

• Update Snapshot Transactions。 監視執行更新作業的使用中快照集交易的總數。

• NonSnapshot Version Transactions。 監視產生版本記錄的使用中非快照集交易的總數。

  注意

  Update Snapshot Transactions 和 NonSnapshot Version Transactions 的總和代表參與版本產生的交易總數。 Snapshot Transactions 和 Update Snapshot Transactions 的差異可報告唯讀快照集交易的數目。

資料列版本設定隔離等級範例

下列範例將示範 SNAPSHOT 隔離交易與使用資料列版本設定之 READ COMMITTED 交易之間的行為差異。

A. 使用 SNAPSHOT 隔離

此範例中，在 SNAPSHOT 隔離下執行的交易會讀取接著由另一個交易修改的資料。 SNAPSHOT 交易不會封鎖另一個交易執行的更新作業，而會繼續從版本控制資料列中讀取資料，忽略資料的修改。 不過，當 SNAPSHOT 交易嘗試修改意見由另一個交易修改過的資料時，SNAPSHOT 交易會產生錯誤且結束。

在工作階段 1 上：

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

在工作階段 2 上：

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

在工作階段 1 上：

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

在工作階段 2 上：

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

在工作階段 1 上：

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. 示範使用資料列版本設定的 READ COMMITTED 隔離

在此範例中，使用資料列版本設定的 READ COMMITTED 交易與另一個交易同時執行。 READ COMMITTED 交易的運作方式和 SNAPSHOT 交易不同。 與 SNAPSHOT 交易類似的是，READ COMMITTED 交易即使在其他交易修改資料後還是會讀取版本控制資料列。 不過，與 SNAPSHOT 交易不同，READ COMMITTED 交易：

• 在其他交易認可資料變更後，會讀取已修改的資料。
• 可以更新由其他交易修改的資料，但是 SNAPSHOT 交易無法做到。

在工作階段 1 上：

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

在工作階段 2 上：

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

在工作階段 1 上：

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

在工作階段 2 上：

-- Commit the transaction.
COMMIT TRANSACTION;
GO

在工作階段 1 上：

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

啟用資料列版本設定式的隔離等級

資料庫管理員可藉由在 ALTER DATABASE 陳述式中使用 READ_COMMITTED_SNAPSHOT 和 ALLOW_SNAPSHOT_ISOLATION 資料庫選項，來控制資料列版本設定的資料庫層級設定。

當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON 時，就會立即啟用用來支援此選項的機制。 設定 READ_COMMITTED_SNAPSHOT 選項時，資料庫中只會允許使用執行 ALTER DATABASE 命令的連線。 在 ALTER DATABASE 完成以前，資料庫中不可以有其他開啟的連接。 資料庫不一定要處於單一使用者模式。

下列 Transact-SQL 陳述式會啟用 READ_COMMITTED_SNAPSHOT：

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 時，資料庫引擎執行個體會等到所有在資料庫中已修改資料的作用中交易完成之後，才會為已修改的資料產生資料列版本。 如果有作用中的修改交易，資料庫引擎就會將選項的狀態設定為 PENDING_ON。 在所有修改交易完成之後，選項的狀態會變更為 ON。 在選項完全成為 ON 之前，使用者無法啟動該資料庫中的 SNAPSHOT 交易。 同樣地，當資料庫管理員將 ALLOW_SNAPSHOT_ISOLATION 選項設定為 OFF 時，資料庫就會經歷 PENDING_OFF 狀態。

下列 Transact-SQL 陳述式會啟用 ALLOW_SNAPSHOT_ISOLATION：

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

下表列出並說明 ALLOW_SNAPSHOT_ISOLATION 選項的狀態。 將 ALTER DATABASE 與 ALLOW_SNAPSHOT_ISOLATION 選項搭配使用，不會影響到目前存取資料庫資料的使用者。

現行資料庫的 SNAPSHOT 隔離狀態	描述
OFF	未啟動 SNAPSHOT 隔離交易的支援。 不允許任何 SNAPSHOT 隔離交易。
PENDING_ON	SNAPSHOT 隔離交易的支援處於轉換狀態 (從 OFF 到 ON)。 開啟的交易必須完成。 不允許任何 SNAPSHOT 隔離交易。
ON	已啟動 SNAPSHOT 隔離交易的支援。 允許 SNAPSHOT 交易。
PENDING_OFF	SNAPSHOT 隔離交易的支援處於轉換狀態 (從 ON 到 OFF)。 在此時間之後所啟動的 SNAPSHOT 交易，無法存取此資料庫。 現有的 SNAPSHOT 交易仍可存取此資料庫。 現有的寫入交易仍使用此資料庫中的版本設定。 直到資料庫 SNAPSHOT 隔離狀態為 ON 時開始的所有 SNAPSHOT 交易完成後，狀態 PENDING_OFF 才會變成 OFF。

使用 sys.databases 目錄檢視，可判定兩個資料列版本設定資料庫選項的狀態。

對使用者資料表的所有更新，以及儲存在 master 與 msdb 中的一些系統資料表，都會產生資料列版本。

在 master 與 msdb 資料庫中會自動將 ALLOW_SNAPSHOT_ISOLATION 選項設定為 ON，且無法停用。

使用者無法在 master、tempdb 或 msdb 中將 READ_COMMITTED_SNAPSHOT 選項設定為 ON。

使用資料列版本控制架構的隔離等級

一律會啟用資料列版本設定架構，並且有多個功能會使用此架構。 除了提供資料列版本設定式的隔離等級之外，它也用來支援觸發程序中所做的修改以及 Multiple Active Result Set (MARS) 工作階段中所做的修改，還支援線上索引作業的資料讀取。

資料列版本設定式的隔離等級是在資料庫層級啟用。 從已啟用之資料庫存取物件的應用程式，可以使用下列隔離等級執行查詢：

• 透過將 READ_COMMITTED_SNAPSHOT 資料庫選項設為 ON，進而使用資料列版本設定的 READ COMMITTED，如下列程式碼範例所示：

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  為資料庫啟用 READ_COMMITTED_SNAPSHOT 時，所有在 READ COMMITTED 隔離等級下執行的查詢都會使用資料列版本設定，這意謂著讀取作業不會封鎖更新作業。

• 將 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 來隔離 SNAPSHOT，如下列程式碼範例所示：

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  使用跨資料庫查詢時，在 SNAPSHOT 隔離下執行的交易可以存取 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 的資料庫中的資料表。 若要存取未將 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 的資料庫中的資料表，必須變更隔離等級。 例如，下列程式碼範例會顯示 SELECT 陳述式，該陳述式會在執行 SNAPSHOT 交易的同時聯結兩個資料表。 其中一個資料表屬於未啟用 SNAPSHOT 隔離的資料庫。 當 SELECT 陳述式在 SNAPSHOT 隔離下執行時，將無法順利執行。

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  下列程式碼範例所示的是經過修改的同一個 SELECT 陳述式，可在存取特定資料表時將交易隔離等級變更為 READ COMMITTED。 由於此項變更，就可以順利執行 SELECT 陳述式。

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

使用資料列版本設定式的隔離等級的交易限制

使用資料列版本設定式的隔離等級時，請考慮下列限制：

• 在 tempdb、msdb 或 master 中無法啟用 READ_COMMITTED_SNAPSHOT。

• 全域暫存資料表會儲存在 tempdb 中。 存取 SNAPSHOT 交易內的全域暫存資料表時，必須符合下列其中一項：

  • 在 tempdb 中，將 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON。
  • 使用隔離提示來變更陳述式的隔離等級。

• 當遇到下列情況時，SNAPSHOT 交易會失敗：

  • 啟動 SNAPSHOT 交易之後，且於 SNAPSHOT 交易存取資料庫之前，資料庫都是唯讀的。
  • 如果從多重資料庫存取物件，在 SNAPSHOT 交易啟動之後，但在 SNAPSHOT 交易存取資料庫之前，會以發生資料庫復原的方式變更資料庫狀態。 例如：資料庫設定為 OFFLINE 後又設定為 ONLINE，資料庫因 AUTO_CLOSE 選項設定為 ON 而自動關閉又重新開啟，或資料庫分離又重新連線。

• SNAPSHOT 隔離中不支援分散式交易，包括在分散式資料分割資料庫中的查詢。

• 資料庫引擎不會保留多個版本的系統中繼資料。 資料表上的資料定義語言 (DDL) 陳述式和其他資料庫物件 (索引、檢視、資料類型、預存程序和 Common Language Runtime 函數) 會變更中繼資料。 如果 DDL 陳述式修改了物件，則對 SNAPSHOT 隔離下的物件進行任何並行參考都會導致 SNAPSHOT 交易失敗。 當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON 時，READ COMMITTED 交易沒有此限制。

  例如，資料庫管理員會執行下列 ALTER INDEX 陳述式。

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  執行 ALTER INDEX 陳述式時，任何使用中的快照集交易都會出現錯誤 (如果快照集交易嘗試在執行 HumanResources.Employee 陳述式之後參考 ALTER INDEX 資料表)。 使用資料列版本設定的 READ COMMITTED 交易將不受影響。

  注意

  BULK INSERT 作業可能會造成變更目標資料表中繼資料 (例如，停用條件約束檢查時)。 如果發生這種狀況，存取大量插入資料表的並行 SNAPSHOT 隔離交易會失敗。

自訂鎖定及資料列版本設定

自訂鎖定逾時

如果當另一個交易已經擁有該資源的衝突鎖定，使得資料庫引擎執行個體無法將鎖定授與一個交易時，系統就會封鎖第一個交易，以等待現有的鎖定釋出。 根據預設，鎖定等候沒有逾時期間，因此交易可能會無限期封鎖。

LOCK_TIMEOUT 設定值可讓應用程式設定陳述式等待已封鎖之資源的時間上限。 當陳述式等待的時間超過 LOCK_TIMEOUT 設定值時，會自動取消封鎖的陳述式，然後將錯誤訊息 1222 (Lock request time-out period exceeded) 傳回。 但是，任何包含陳述式的交易都不會復原。 因此，應用程式必須具有能捕捉錯誤訊息 1222 的錯誤處理常式。 如果應用程式未設陷錯誤，應用程式可能會繼續不知道交易內的個別陳述式已取消，但交易仍保持作用中。 可能會發生錯誤，因為交易稍後的陳述式可能取決於從未執行的陳述式。

實作會捕捉錯誤訊息 1222 的錯誤處理常式，可讓應用程式處理逾時狀況並採取補救措施，例如自動重新送出先前被封鎖的陳述式，或是回復整筆交易。

若要判斷目前的 LOCK_TIMEOUT 設定，請執行 @@LOCK_TIMEOUT 函數：

SELECT @@LOCK_TIMEOUT;
GO

自訂交易隔離等級

READ COMMITTED 是 資料庫引擎的預設隔離等級。 如果應用程式必須在不同隔離等級操作，可以使用下列方法來設定隔離等級：

• 執行 SET TRANSACTION ISOLATION LEVEL 陳述式。
• 如果 ADO.NET 應用程式使用 System.Data.SqlClient 命名空間，則可以使用 SqlConnection.BeginTransaction 方法來指定 IsolationLevel 選項。
• 使用 ADO 的應用程式可以設定Autocommit Isolation Levels屬性。
• 當啟動交易時，使用 OLE DB 的應用程式可以將 isoLevel 設為所需的交易隔離等級，以呼叫 ITransactionLocal::StartTransaction。 當在自動認可模式中指定隔離等級時，使用 OLE DB 的應用程式可以將 DBPROPSET_SESSION 屬性 DBPROP_SESS_AUTOCOMMITISOLEVELS 設為所需的交易隔離等級。
• 使用 ODBC 的應用程式可以使用 SQLSetConnectAttr 來設定 SQL_COPT_SS_TXN_ISOLATION 屬性。

已指定隔離等級時，在工作階段中，所有查詢和資料操作語言 (DML) 陳述式的鎖定行為都會在此隔離等級運作。 此隔離等級會維持有效，直到工作階段結束或隔離等級設為另一個等級為止。

下列範例會設定 SERIALIZABLE 隔離等級：

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

如果有必要，藉由指定資料表層級的提示，可以覆寫個別查詢或 DML 陳述式的隔離等級。 指定資料表層級的提示不會影響到工作階段的其他陳述式。

若要判斷目前設定的交易隔離等級，可使用下例所示的 DBCC USEROPTIONS 陳述式。 結果集和您系統上的結果集可能不盡相同。

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

結果集如下所示。

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

鎖定提示

在 SELECT、INSERT、UPDATE、DELETE 以及 MERGE 陳述式中的個別資料表參考可以指定鎖定提示。 此提示會指定鎖定的類型，或是資料表資料所使用資料庫引擎執行個體的資料列版本設定。 需要取得物件的較細緻鎖定類型的控制時可以使用資料表層級的鎖定提示。 這些鎖定提示覆寫 (Override) 工作階段目前的交易隔離等級 (Isolation Level)。

如需有關特定鎖定提示及其行為的詳細資訊，請參閱資料表提示 (Transact-SQL)。

資料庫引擎在讀取中繼資料時可能必須取得鎖定，即使在處理具有鎖定提示的陳述式，而該鎖定是防止讀取資料時要求共用鎖定時也是如此。 例如，在 READ UNCOMMITTED 隔離層級執行的 SELECT 陳述式或使用 NOLOCK 提示時並不會取得共用鎖定，但在讀取系統目錄檢視時有時會要求鎖定。 這表示當並行交易修改資料表的中繼資料時，可能會封鎖這類 SELECT 陳述式。

如下列範例所顯示，如果交易隔離等級設定為 SERIALIZABLE，並使用 SELECT 陳述式來指定資料表層級的鎖定提示 NOLOCK，則不會取用通常用來維護 SERIALIZABLE 交易的索引鍵範圍鎖定。

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

所取用的鎖定中，唯一參考 HumanResources.Employee 的鎖定為結構描述穩定性 (Sch-S) 鎖定。 這種情況下不保證有序列化能力。

ALTER TABLE 的 LOCK_ESCALATION 選項可避免在鎖定擴大期間鎖定資料表，並啟用資料分割資料表上的 HoBT (分割區) 鎖定。 這個選項不是鎖定提示，而是可用來減少鎖定擴大。 如需詳細資訊，請參閱 ALTER TABLE (Transact-SQL)。

自訂索引的鎖定

在大部分的情況下，資料庫引擎的動態鎖定策略都會自動為查詢選擇最佳的鎖定資料粒度。 除非資料表或索引存取模式都容易理解且維持一致，而且存在待解決的資源競爭問題，否則我們建議您不要覆寫預設鎖定層級。 覆寫鎖定層級可能會嚴重妨礙資料表或索引的並行存取。 例如，針對使用者經常存取的大型資料表指定僅限資料表層級鎖定可能會導致效能瓶頸，因為使用者必須等候資料表層級鎖定釋放，才能存取資料表。

如果存取模式容易理解且維持一致，在少數情況下，不允許頁面或資料列鎖定可能會很有用。 例如，資料庫應用程式使用的查閱資料表以批次處理序每週更新一次。 並行讀取器會存取具有共用 (S) 鎖定的資料表，而且每週批次更新會存取具有獨佔 (X) 鎖定的資料表。 針對資料表關閉頁面和資料列鎖定會允許讀取器透過共用資料表鎖定以並行方式存取資料表，藉以減少整週的鎖定額外負荷。 當批次作業執行時，它就可以有效率地完成更新，因為它會取得獨佔資料表鎖定。

關閉頁面和資料列鎖定不一定是可接受的作法，因為每週批次更新將會封鎖並行讀取器，使其無法在更新執行時存取資料表。 如果批次作業只變更少數資料列或頁面，您就可以變更鎖定層級來允許資料列或頁面層級鎖定，進而讓其他工作階段讀取資料表而不封鎖。 如果批次作業具有大量更新，取得資料表的獨佔鎖定可能是確保批次作業有效執行的最佳方式。

在某些工作負載中，當兩個並行作業取得相同數據表上的數據列鎖定，然後彼此封鎖時，可能會發生死結類型，因為它們都需要鎖定頁面。 不允許資料列鎖定會強制其中一項作業等候，避免發生死結。 如需死結的更多相關資訊，請參閱死結指南。

您可以使用 CREATE INDEX 和 ALTER INDEX 陳述式來設定索引上所使用的鎖定資料粒度。 此外，還可以使用 CREATE TABLE 和 ALTER TABLE 陳述式來設定 PRIMARY KEY 和 UNIQUE 條件約束上的鎖定資料粒度。 為了提供回溯相容性，sp_indexoption 系統預存程序也可以設定資料粒度。 若要顯示指定之索引的目前鎖定選項，請使用 INDEXPROPERTY 函數。 分頁層級鎖定、資料列層級鎖定、或是分頁層級與資料列層級鎖定可不允許特定索引採用。

不允許的鎖定	存取索引者
分頁層級	資料列層級與資料表層級鎖定
資料列層級	分頁層級與資料表層級鎖定
分頁層級與資料列層級	資料表層級鎖定

進階交易資訊

巢狀交易

外顯交易可以是巢狀的。 其主要目的是要支援預存程序中的交易，讓交易可以被已經在交易中的處理序呼叫，或沒有動作的交易內的處理序呼叫。

下列範例顯示巢狀交易的使用。 如果 TransProc 當交易為使用中時呼叫，則 TransProc 中的巢狀交易結果是由外部交易所控制，而且其 INSERT 陳述式會根據外部交易的認可或復原來認可或復原。 如果執行 TransProc 的處理序沒有尚未處理的交易，在程序結尾的 COMMIT TRANSACTION 便會認可 INSERT 陳述式。

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

當外部交易啟用時，資料庫引擎會忽略內部交易的認可。 交易是認可或復原，取決於最外層交易在最後的認可或復原。 若是認可外部交易，則內部的巢狀交易也會被認可。 若是復原外部交易，便會復原所有的內部交易，而不論內部交易是否個別被認可。

對 COMMIT TRANSACTION 或 COMMIT WORK 發出的每個呼叫都會套用至最後一個執行的 BEGIN TRANSACTION。 如果 BEGIN TRANSACTION 陳述式位於巢狀結構中，則 COMMIT 陳述式只會套用至最後一個巢狀交易，亦即最內層的交易。 即使巢狀交易中 COMMIT TRANSACTION transaction_name 陳述式參考外部交易的交易名稱，則仍只會認可最內層的交易。

ROLLBACK TRANSACTION 陳述式的 transaction_name 參數如果參考一組具名巢狀交易的內部交易，是不允許的情況。 transaction_name 只能參考最外層交易的交易名稱。 如果使用外部交易名稱的 ROLLBACK TRANSACTION transaction_name 陳述式，是在一組巢狀交易的任何層級執行，將會回復所有的巢狀交易。 如果是在一組巢狀交易的任何層級執行不含 transaction_name 參數的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 陳述式，則會復原所有巢狀交易，包括最外層的交易。

@@TRANCOUNT 函數會記錄目前的交易巢狀層級。 每個 BEGIN TRANSACTION 陳述式會以 1 為單位來遞增 @@TRANCOUNT。 每個 COMMIT TRANSACTION 或 COMMIT WORK 陳述式會以 1 為單位來遞減 @@TRANCOUNT。 ROLLBACK WORK 或 ROLLBACK TRANSACTION 陳述式如果沒有交易名稱，就會復原所有巢狀交易並將 @@TRANCOUNT 遞減至 0。 ROLLBACK TRANSACTION 如果使用一組巢狀交易中最外層交易的交易名稱，就會復原所有巢狀交易並將 @@TRANCOUNT 遞減至 0。 若要判斷您是否已經處於交易中時，請使用 SELECT @@TRANCOUNT 來判斷是否為 1 或更大的數字。 如果 @@TRANCOUNT 是 0，則表示您未處於交易中。

使用繫結工作階段

繫結工作階段可簡化相同伺服器上多個工作階段之間動作的協調作業。 繫結工作階段允許兩個以上的工作階段共用相同的交易和鎖定，並且可以使用相同的資料，而不會發生鎖定衝突。 繫結工作階段可以從同一個應用程式中的多個工作階段來建立，也可以從擁有個別工作階段的多個應用程式來建立。

若要參與繫結工作階段，工作階段需呼叫 sp_getbindtoken 或 srv_getbindtoken (透過「開放式資料服務」) 來取得繫結 Token。 繫結 Token 是唯一識別每筆繫結交易的字元字串。 然後再將繫結 Token 傳送給其他工作階段，以便與目前的工作階段繫結。 其他工作階段藉由呼叫 sp_bindsession，並使用從第一個工作階段接收到的繫結 Token，來繫結到交易。

繫結 Token 必須從建立第一個工作階段的應用程式程式碼，傳送給其他應用程式程式碼，以供後續的應用程式將其工作階段繫結至第一個工作階段。 應用程式無法使用 Transact-SQL 陳述式或 API 函數，來取得另一個處理序所啟動交易的繫結 Token。 可用來傳送繫結 Token 的部分方法如下：

• 如果工作階段全部都是由相同的應用程式處理序來初始化，則繫結 Token 可以儲存在全域記憶體，或是作為參數傳遞至函數中。

• 如果工作階段是由個別的應用程式處理序建立，則可使用處理序間通訊 (IPC) 來傳送繫結 Token，例如遠端程序呼叫 (RPC) 或動態資料交換 (DDE)。

• 繫結 Token 可以儲存在資料庫引擎執行個體的資料表中，而這個資料表可由要繫結至第一個工作階段的處理序所讀取。

在一組繫結工作階段中，任何時間只能有一個使用中的工作階段。 如果某個工作階段正在執行個體上執行陳述式或等待執行個體的暫止結果，則繫結至相同權杖上的其他工作階段都無法存取執行個體，直到目前工作階段完成處理或取消目前陳述式為止。 如果執行個體忙著處理來自另一個繫結工作階段的陳述式，則會發生錯誤，指出交易空間正在使用中，工作階段應稍後重試。

當您繫結工作階段時，每個工作階段都會保留其隔離等級設定。 使用 SET TRANSACTION ISOLATION LEVEL 來變更一個工作階段的隔離等級設定，並不會影響與相同權杖繫結的任何其他工作階段的設定。

繫結工作階段的類型

繫結工作階段有兩種類型：本機與分散式。

• 本機繫結工作階段 允許繫結工作階段共用資料庫引擎單一執行個體中單一交易的交易空間。

• 分散式繫結工作階段 允許繫結工作階段跨二或多個執行個體共用同一筆交易，直到使用 Microsoft 分散式交易協調器 (MS DTC) 認可或復原整筆交易為止。

分散式繫結工作階段不是由字元字串的繫結 Token 識別，而是由分散式交易識別碼來識別。 如果繫結工作階段涉及本機交易，並使用 SET REMOTE_PROC_TRANSACTIONS ON 在遠端伺服器上執行 RPC，MS DTC 就會自動將本機繫結交易升階成分散式繫結交易，並啟動 MS DTC 工作階段。

使用繫結工作階段的時機

在舊版 SQL Server 中，繫結工作階段主要用於開發擴充預存程序，而這些擴充預存程序必須代表呼叫它們的處理序來執行 Transact-SQL 陳述式。 讓呼叫處理序傳入繫結 Token 作為擴充預存程序的一個參數，以允許程序加入呼叫處理序的交易空間之中，因此可整合擴充預存程序與呼叫處理序。

在資料庫引擎中，使用 CLR 撰寫的預存程序比起擴充預存程序，更安全、更具擴充性也更穩定。 CLR 預存程序會使用 SqlContext 物件 (而不是 sp_bindsession) 來聯結呼叫工作階段的內容。

繫結工作階段可以用來開發三層式應用程式，其中商務邏輯可加入個別程式，而這些程式可協同處理單筆商務交易。 這些程式必須予以編碼，以便能謹慎協調它們對資料庫的存取。 因為兩個工作階段共用相同的鎖定，所以這兩個程式絕不能同時嘗試修改相同的資料。 在交易過程的任何時候，只能有一個工作階段執行工作，不允許平行執行。 只有在妥善定義的產生點 (例如所有 DML 陳述式都已完成，且也已擷取結果時)，才能於工作階段之間切換交易。

撰寫有效率的交易

儘可能讓交易越短越好相當重要。 開始交易時，資料庫管理系統 (DBMS) 在交易結束之前必須保存許多資源，以保護交易的不可部份完成特性、一致性、隔離和持久性 (ACID) 屬性。 如果已修改資料，必須以獨佔鎖定 (防止其他交易讀取資料列) 保護修改的資料列，並且在認可或回復交易之前必須保持獨佔鎖定。 視交易隔離等級設定而定，SELECT 陳述式可能會取得在認可或復原交易之前必須保持的鎖定。 尤其是在擁有許多使用者的系統上，必須盡可能讓交易越短越好，以降低鎖定競爭並行連接之間的資源。 在少數使用者的情況下，沒有效率的長時間執行交易不會造成問題，但對擁有上千個使用者的系統來說就非常有問題。 從 SQL Server 2014 (12.x) 開始，資料庫引擎支援延遲的持久交易。 延遲的持久交易可能會改善可擴縮性和效能，但無法保證持久性。 如需詳細資訊，請參閱控制交易持久性。

程式碼指導方針

以下為撰寫有效交易的指引：

• 交易期間毋需使用者輸入。 啟動交易前，先取得必要的使用者所有輸入內容。 如果交易期間需要額外的使用者輸入，則復原目前的交易，並於使用者提供輸入之後重新啟動交易。 即使使用者立即回應，人類的反應時間還是比電腦的速度慢很多。 交易佔用所有資源的時間非常久，這是造成封鎖問題的潛在因素。 如果使用者並未回應，交易便維持作用中，並鎖定重要的資源直到使用者回應為止，這種情形可能會維持數分鐘，或甚至幾小時。

• 如果位在全部皆可行，不要在瀏覽資料時開啟交易。 交易應該等到所有的初步資料分析完成之後再啟動。

• 盡可能讓交易越短越好。 在您知道必須進行的修改之後，請啟動交易、執行修改陳述式，然後立即進行認可或回復。 除非必要，否則請勿開啟交易。

• 若要減少封鎖問題，請考慮使用以資料列版本設定為基礎的隔離層級來進行唯讀查詢。

• 善用較低的交易隔離等級。 許多應用程式都可以編碼為使用 READ COMMITTED 交易隔離等級。 有少數交易需要 SERIALIZABLE 的交易隔離等級。

• 以智慧方式使用開放式並行存取選項。 在並行更新的可能性較低的系統中，處理偶發的「有人在您讀取資料之後變更過資料」錯誤的額外負荷，可能比讀取時一直鎖定資料列的額外負荷更低。

• 在交易中儘可能存取最少的資料量。 這會減少鎖定的資料列數量，因而降低了交易之間的競爭。

• 盡可能避免封閉式鎖定提示，例如 HOLDLOCK。 像 HOLDLOCK 或 SERIALIZABLE 隔離等級的提示，即便有共用鎖定並減少並行要求，也會造成處理序等候。

• 盡可能避免使用隱含交易。 隱含交易會因本質導致無法預期的行為。 請參閱隱含交易和並行問題。

隱含交易和避免並行與資源問題

若要避免並行與資源的問題，請小心管理隱含交易。 使用隱含交易時，COMMIT 或 ROLLBACK 之後的下一個 Transact-SQL 陳述式會自動啟動一個新的交易。 這可能造成交易在應用程式瀏覽資料時，或甚至在需要使用者輸入時被開啟。 保護資料修改所需的最後一筆交易完成之後，請關閉隱含交易，直到交易再度需要保護資料修改為止。 這個程序讓資料庫引擎在應用程式瀏覽資料及取得使用者輸入時使用自動認可模式。

此外，啟用 SNAPSHOT 隔離等級時，雖然新交易不會佔用鎖定，但長時間執行的交易仍然會阻礙從版本存放庫移除舊版本。

管理長時間執行的交易

「長時間執行的交易」 是指未及時認可的使用中交易或未及時回復的交易。 例如，如果交易的開始和結束是由使用者控制，長時間執行之交易的常見原因就是使用者開始進行交易，然後在交易等候使用者回應時離開。

長時間執行的交易可能會對資料庫造成以下幾個嚴重的問題：

• 伺服器執行個體若是在作用中交易已執行許多未認可的修改之後關閉，後續重新啟動之復原階段所花費的時間，可能會遠超過recovery interval伺服器組態選項或 ALTER DATABASE ... SET TARGET_RECOVERY_TIME 選項所指定的時間。 這些選項分別控制著使用中檢查點與間接檢查點。 如需有關檢查點類型的詳細資訊，請參閱資料庫檢查點 (SQL Server)。

• 更重要的是，等候中交易儘管產生的記錄可能很少，卻會永久阻礙記錄截斷動作，而導致交易記錄逐漸增大乃至填滿。 一旦交易記錄填滿，資料庫就不再能夠執行任何寫入。 如需詳細資訊，請參閱 SQL Server 交易記錄架構與管理指南、寫滿交易記錄疑難排解 (SQL Server Error 9002) 及交易記錄。

探索長時間執行的交易

若要尋找長時間執行的交易，請使用下列其中一種方式：

• sys.dm_tran_database_transactions

  這個動態管理檢視傳回有關資料庫層級之交易的資訊。 對於長時間執行的交易，較重要的資料行包括第一筆記錄檔記錄的時間 (database_transaction_begin_time)、交易的目前狀態 (database_transaction_state) 和交易記錄之開始記錄的記錄序號 (LSN) (database_transaction_begin_lsn)。

  如需詳細資訊，請參閱 sys.dm_tran_database_transactions (Transact-SQL)。

• DBCC OPENTRAN

  此陳述式可讓您識別交易擁有者的使用者識別碼，如此就可以追蹤交易來源，以便適當地終止交易 (提交或復原)。 如需詳細資訊，請參閱 DBCC OPENTRAN (Transact-SQL)。

終止交易

若要在特定工作階段終止交易，請使用 KILL 陳述式。 但是，請小心使用此陳述式，尤其是執行重要處理序時。 如需詳細資訊，請參閱 KILL (Transact-SQL)。

死結

鎖死是一個與鎖定相關的複雜主題，但與封鎖不同。

• 如需鎖死的詳細資訊，包括監視、診斷和範例，請參閱鎖死指南。
• 如需適用於 Azure SQL 資料庫的鎖死詳細資訊，請參閱分析並防止 Azure SQL 資料庫中的鎖死。

相關內容

• 了解並解決 SQL Server 封鎖問題
• 了解並解決 Azure SQL Database 封鎖問題
• 交易相關的動態管理檢視和函數 (Transact-SQL)
• 資料列版本設定的額外負荷
• sys.dm_tran_locks (Transact-SQL) (機器翻譯)