設定交易隔離等級 （Transact-SQL）

適用於：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics分析平台系統（PDW）Microsoft Fabric 中的 SQL 資料庫

控制 SQL Server 連線發出之 Transact-SQL 陳述式的鎖定和資料列版本設定行為。

Transact-SQL 語法慣例

Syntax

Microsoft Fabric 中 SQL Server、Azure SQL Database 和 SQL Database 的語法。

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Azure Synapse Analytics 和平行資料倉儲的語法。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

注意

Azure Synapse Analytics 會實作 ACID 交易。 預設隔離層次為 READ UNCOMMITTED。 您可以在連線到READ COMMITTED SNAPSHOT ISOLATION資料庫時，透過轉動ONREAD_COMMITTED_SNAPSHOT使用者資料庫的資料庫選項來將其變更為master。 啟用之後，此資料庫中的所有交易都會在下 READ COMMITTED SNAPSHOT ISOLATION 執行，且不會接受工作階段層級的設定 READ UNCOMMITTED 。 如需相關資訊，請參閱 ALTER DATABASE SET 選項 （Transact-SQL）。

引數

讀取未提交

指定陳述式可以讀取其他交易已修改但尚未確定的資料列。

在層級 READ UNCOMMITTED 執行的交易不會發出共用鎖定，以防止其他交易修改目前交易讀取的資料。 READ UNCOMMITTED 交易也不會被排他鎖阻止，這些鎖會阻止當前交易讀取其他交易已修改但未提交的數據列。 設定此選項時，可以讀取未認可的修改，這稱為髒讀。 在交易結束之前，資料中的值可以變更，資料列也可以在資料集中出現或消失。 此選項的效果與在交易中所有NOLOCK陳述式中的所有表格上設定SELECT的效果相同。 這是隔離等級中限制最少的一種。

在 SQL Server 中，您也可以利用下列兩個等級之一，防止交易讀到尚未認可的資料修改 (中途讀取)，同時將鎖定競爭的情況降到最低：

• READ COMMITTED資料庫選項設定為 READ_COMMITTED_SNAPSHOT的隔離層次ON。

• SNAPSHOT隔離等級。 如需快照隔離的詳細資訊，請參閱 SQL Server 中的快照隔離 。

已提交讀取

指定陳述式無法讀取已修改但未由其他交易認可的資料。 這個選項可避免中途讀取。 目前交易內個別陳述式之間的其他交易可以變更資料，這會產生不可重複的讀取或虛設項目資料。 這個選項是 SQL Server 的預設值。

的 READ COMMITTED 行為取決於資料庫選項的 READ_COMMITTED_SNAPSHOT 設定：

• 如果設定為 READ_COMMITTED_SNAPSHOT （SQL Server 上的預設值），資料庫OFF引擎會使用共用鎖定來防止其他交易在目前交易執行讀取作業時修改資料列。 共用鎖定也會封鎖陳述式，使它們在其他交易完成之前，無法讀取其他交易所修改的資料列。 共用鎖定類型會決定何時釋放。 資料列鎖定會在處理下一個資料列之前釋放。 頁面鎖定會在讀取下一個頁面時釋放，而資料表鎖定會在陳述式完成時釋放。

• 如果設定為 READ_COMMITTED_SNAPSHOT，資料庫ON引擎會使用資料列版本設定，將資料交易一致的快照集呈現在陳述式開頭所存在的資料。 鎖定不會用來保護資料免受其他交易的更新。

  • READ_COMMITTED_SNAPSHOT ON 是 Azure SQL 資料庫及 Microsoft Fabric SQL 資料庫的預設。

重要

選擇交易隔離層級不會影響為保護資料修改而取得的鎖定。 交易永遠都會取得它所修改之資料的獨佔鎖定，並保留該鎖定直到交易完成為止，不論為該交易所設定的隔離等級為何皆同。 此外，在隔離層級進行 READ COMMITTED 的更新會使用所選資料列的更新鎖定，而在隔離層級進行 SNAPSHOT 的更新會使用列版本來選取要更新的資料列。 對於讀取作業，交易隔離等級主要是定義對於其他交易所做修改之影響的保謢等級。 如需詳細資訊，請參閱 交易鎖定和資料列版本控制指南。

快照集隔離支援 FILESTREAM 資料。 在快照集隔離模式下，交易中任何陳述式讀取的 FILESTREAM 資料是交易開始時存在之資料的交易一致版本。

當 READ_COMMITTED_SNAPSHOT 資料庫選項為 ON時，您可以使用 READCOMMITTEDLOCK 表格提示來要求共用鎖定，而不是在隔離層次執行 READ COMMITTED 之交易中個別陳述式的資料列版本設定。

注意

當您設定選項 READ_COMMITTED_SNAPSHOT 時，資料庫中只容許執行指令的 ALTER DATABASE 連線。 在完成之前 ALTER DATABASE ，資料庫中不得有其他開啟的連線。 資料庫不必處於單一使用者模式。

可重複讀取

指定陳述式無法讀取已修改但尚未由其他交易認可的資料，而且在目前交易完成之前，其他交易都無法修改目前交易所讀取的資料。

交易中每個陳述式所讀取的所有資料都會設定共用鎖定，直到交易完成為止。 這可防止其他交易修改目前交易讀取的任何資料列。 其他交易仍可以插入新資料列，但必須符合目前交易所發出的陳述式搜尋條件。 如果現行交易接著重試陳述式，它會擷取新資料列，這會導致虛擬讀取。 因為共用鎖定會保留到交易結束，而不是在每個陳述式結束時釋放，所以並行會低於預設 READ COMMITTED 隔離層級。 請只在必要時，才使用這個選項。

SNAPSHOT

指定交易中任何陳述式讀取的資料是交易開始時存在之資料的交易一致版本。 交易只能辨識交易開始之前所認可的資料修改。 其他交易在目前交易開始之後所做的資料修改，對於目前交易中執行的陳述式來說是看不到的。 效果就如同交易中的陳述式會取得認可資料的快照集，因為這項資料於交易開始時就存在。

除非正在復原 SNAPSHOT 資料庫，否則交易在讀取資料時不會要求鎖定。 SNAPSHOT 讀取資料的交易不會阻止其他交易寫入資料。 寫入資料的交易不會阻止 SNAPSHOT 交易讀取資料。

在資料庫回復的回復階段期間， SNAPSHOT 如果嘗試讀取另一個正在回復的交易所鎖定的資料，則交易會要求鎖定。 SNAPSHOT交易會遭到封鎖，直到該交易復原為止。 鎖定會在授與鎖定之後立即釋放。

ALLOW_SNAPSHOT_ISOLATION必須先將資料庫選項設為 ON ，才能啟動使用SNAPSHOT隔離層次的交易。 如果使用隔離層次的交易SNAPSHOT存取多個資料庫中的資料，ALLOW_SNAPSHOT_ISOLATION則必須在每個資料庫中設定為 。ON

交易無法設定為 SNAPSHOT 以另一個隔離層級開始的隔離層級;這樣做會導致交易中止。 如果交易在隔離層次中 SNAPSHOT 啟動，您可以將其變更為另一個隔離層次，然後再變更回 SNAPSHOT。 交易會在第一次存取資料時啟動。

在隔離層級下 SNAPSHOT 執行的交易可以檢視該交易所做的變更。 例如，如果交易在資料表上執行 ， UPDATE 然後針對相同資料表發出 SELECT 陳述式，則修改後的資料會包含在結果集中。

注意

在快照集隔離模式下，交易中任何陳述式讀取的 FILESTREAM 資料是交易開頭存在之資料的交易一致版本，而不是陳述式開頭。

SERIALIZABLE

指定下列條件：

• 陳述式無法讀取已修改但尚未由其他交易認可的資料。

• 在現行交易完成之前，沒有其他交易可以修改現行交易讀取的資料。

• 其他交易無法插入索引鍵值的新資料列，這些索引鍵值將落在目前交易中任何陳述式讀取的索引鍵範圍內，直到目前交易完成為止。

範圍鎖定會放在符合交易所執行的每個陳述式之搜尋條件的索引鍵值範圍中。 這會封鎖其他交易，以免它們更新或插入符合目前交易執行之任何陳述式的所有資料列。 這意味著如果第二次執行事務中的任何語句，它們會讀取同一組行。 範圍鎖定會一直保留到交易完成。 這是隔離等級最嚴格的限制，因為它鎖定了索引鍵的整個範圍，且會將鎖定保留到交易完成。 由於並行發生的可能性較低，因此，請只在必要時，才使用這個選項。 此選項的效果與在交易中所有HOLDLOCK陳述式中的所有表格上設定SELECT的效果相同。

備註

一次只能設定其中一個隔離層級選項，而且會為該連線設定，直到明確變更為止。 在交易內執行的所有讀取作業都會在指定隔離層級的規則下運作，除非陳述式子句中的 FROM 資料表提示指定資料表的不同鎖定或版本設定行為。

交易隔離等級會定義讀取作業上取得的鎖定類型。 取得 READ COMMITTED 的共用鎖定或 REPEATABLE READ 通常是列鎖定，不過如果讀取參照頁面或表格中的大量列，則可以將列鎖定呈報為頁面或表格鎖定。 如果交易在讀取後修改了該行，則該事務會取得一個獨佔鎖來保護該行，並且該獨佔鎖將保留到事務完成為止。 例如，如果交易在資料列上具有共用鎖定，而交易接著修改該資料列，則 REPEATABLE READ 共用資料列鎖定會轉換成專用資料列鎖定。

但是有一個例外：您可以在交易期間隨時切換隔離等級。 從任何隔離層次變更為 SNAPSHOT 隔離時，會發生例外狀況。 這樣做將導致交易失敗並回復。 不過，您可以將隔離啟動 SNAPSHOT 的交易變更為任何其他隔離層級。

當您變更交易的隔離等級時，會根據新等級的規則來保護變更後讀取的資源。 變更前讀取的資源則會繼續根據前一等級的規則來受到保護。 例如，如果交易從 READ COMMITTED 變更為 SERIALIZABLE，則變更後取得的共用鎖定現在會保留到交易結束為止。

如果您在預存程序或觸發程式中發出 SET TRANSACTION ISOLATION LEVEL ，當物件傳回控制權時，隔離層次會重設為叫用物件時有效的層次。 例如，如果您在批次中設定 REPEATABLE READ ，而批次接著呼叫預存程序，將隔離層級 SERIALIZABLE設定為 ，當預存程序將控制權傳回批次時，隔離層級設定會還原為 REPEATABLE READ 。

注意

使用者定義函式和共用語言執行階段 （CLR） 使用者定義類型無法執行 SET TRANSACTION ISOLATION LEVEL。 不過，您也可以使用資料表提示來覆寫隔離等級。 如需詳細資訊，請參閱資料表提示 (Transact-SQL)。

當您用來 sp_bindsession 繫結兩個階段作業時，每個階段作業都會保留其隔離層次設定。 用於 SET TRANSACTION ISOLATION LEVEL 變更一個工作階段的隔離層級設定，不會影響繫結至該工作階段的任何其他工作階段的設定。

SET TRANSACTION ISOLATION LEVEL 在執行或執行階段生效，而不是在剖析時生效。

堆積上的最佳化大量載入作業會封鎖在下列隔離等級之下執行的查詢：

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED 使用資料列版本設定

相反地，在這些隔離等級之下執行的查詢也會封鎖堆積上的最佳化大量載入作業。 如需大量載入作業的詳細資訊，請參閱資料的大量匯入及匯出 (SQL Server)。

具 FILESTREAM 功能的資料庫支援下列交易隔離等級。

隔離等級	Transact-SQL 訪問	檔案系統存取
讀取未提交的	SQL Server	不支援
已提交的讀取	SQL Server	SQL Server
可重複讀取	SQL Server	不支援
可序列化	SQL Server	不支援
讀取已提交的快照	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

範例

下列範例會設定工作階段的 TRANSACTION ISOLATION LEVEL。 SQL Server 會保留後來每個 Transact-SQL 陳述式的所有共用鎖定，直到交易完成為止。

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

相關內容

• ALTER DATABASE （Transact-SQL）
• DBCC 使用者選項 （Transact-SQL）
• 選擇 （Transact-SQL）
• SET 陳述式 (Transact-SQL)
• 表格提示 （Transact-SQL）