SET TRANSACTION ISOLATION LEVEL 詳解

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

Transact-SQL 文法約定

 文法

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

 參數

READ UNCOMMITTED

指定語句可以讀取已由其他事務修改但尚未提交的行。

在 READ UNCOMMITTED 層級啟動並執行事務，不會發出共用鎖定來防止其他事務修改當前事務讀取的資料。READ UNCOMMITTED 事務也不會被獨佔鎖定阻塞，獨佔鎖定會禁止當前事務讀取其他事務已修改但尚未提交的行。設定此選項之後，可以讀取未提交的修改，這種讀取稱為髒讀。在事務結束之前，可以更改資料中的值，行也可以出現在資料集中或從資料集中消失。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 NOLOCK 相同。這是隔離等級中限制最少的層級。

在 SQL Server 中，您還可以使用下列任意一種方法，在保護事務不髒讀未提交的資料修改的同時盡量減少鎖定爭用：

• READ COMMITTED 隔離等級，並將 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON。
• SNAPSHOT 隔離等級。

READ COMMITTED

指定語句不能讀取已由其他事務修改但尚未提交的資料。這樣可以避免髒讀。其他事務可以在當前事務的各個語句之間更改資料，從而產生不可重複讀取和幻像資料。該選項是 SQL Server 的預設設定。

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

• 如果將 READ_COMMITTED_SNAPSHOT 設定為 OFF（預設設定），則資料庫引擎會使用共用鎖定防止其他事務在當前事務執行讀取操作期間修改行。共用鎖定還會阻止語句在其他事務完成之前讀取由這些事務修改的行。共用鎖定類型確定它將於何時釋放。行鎖在處理下一行之前釋放。頁鎖在讀取下一頁時釋放，表鎖在陳述式完成時釋放。

  注意：
  在 SQL Server 2008 R2 中，對於 FILESTREAM 資料的檔案系統訪問遵循 READ_COMMITTED_SNAPSHOT 隔離資料庫設定。 如果將 READ_COMMITTED_SNAPSHOT 設定為 ON，則資料庫引擎會使用資料列版本設定為每個語句提供一個在事務上一致的資料快照，因為該資料在語句開始時就存在。不使用鎖來防止其他事務更新資料。

當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON 時，您可以使用 READCOMMITTEDLOCK 表提示為 READ COMMITTED 隔離等級上啟動並執行事務中的各語句請求共用鎖定，而不是資料列版本設定。

注意：
設定 READ_COMMITTED_SNAPSHOT 選項時，資料庫中僅允許存在執行 ALTER DATABASE 命令的串連。在 ALTER DATABASE 完成之前，資料庫中不允許有其他開啟的串連。資料庫不必處於單一使用者模式。

REPEATABLE READ

指定語句不能讀取已由其他事務修改但尚未提交的行，並且指定，其他任何事務都不能在當前事務完成之前修改由當前事務讀取的資料。

對事務中的每個語句所讀取的全部資料都設定了共用鎖定，並且該共用鎖定一直保持到事務完成為止。這樣可以防止其他事務修改當前事務讀取的任何行。其他事務可以插入與當前事務所發出語句的搜尋條件相匹配的新行。如果當前事務隨後重試執行該語句，它會檢索新行，從而產生幻讀。由於共用鎖定一直保持到事務結束，而不是在每個語句結束時釋放，因此並發層級低於預設的 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 資料顯示在事務（而非 Transact-SQL 陳述式）的開始。

SERIALIZABLE

請指定下列內容：

• 語句不能讀取已由其他事務修改但尚未提交的資料。
• 任何其他事務都不能在當前事務完成之前修改由當前事務讀取的資料。
• 在當前事務完成之前，其他事務不能使用當前事務中任何語句讀取的索引值插入新行。

範圍鎖處於與事務中執行的每個語句的搜尋條件相匹配的索引值範圍之內。這樣可以阻止其他事務更新或插入任何行，從而限定當前事務所執行的任何語句。這意味著如果再次執行事務中的任何語句，則這些語句便會讀取同一組行。在事務完成之前將一直保持範圍鎖。這是限制最多的隔離等級，因為它鎖定了鍵的整個範圍，並在事務完成之前一直保持範圍鎖。因為並發層級較低，所以應只在必要時才使用該選項。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 HOLDLOCK 相同。

 注釋

一次只能設定一個隔離等級選項，而且設定的選項將一直對那個串連始終有效，直到顯式更改該選項為止。事務中執行的所有讀取操作都會在指定的隔離等級的規則下運行，除非語句的 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

相反，運行在這些隔離等級下面的查詢阻塞了針對堆的最佳化大容量負載操作。有關大量載入操作的詳細資料，請參閱關於大容量匯入和大容量匯出操作和最佳化大容量匯入效能。

已啟用 FILESTREAM 的資料庫支援下列交易隔離等級。

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

 樣本

以下樣本為會話設定了 TRANSACTION ISOLATION LEVEL。對於每個後續 Transact-SQL 陳述式，SQL Server 將所有共用鎖定一直保持到事務結束為止。

複製代碼

USE AdventureWorks2008R2;GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRANSACTION;GOSELECT *    FROM HumanResources.EmployeePayHistory;GOSELECT *    FROM HumanResources.Department;GOCOMMIT TRANSACTION;GO