• 發行項

Database Engine 中的隔離等級

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

交易隔離等級控制：

  • 在讀取資料時是否採用鎖定，以及要求哪一類型的鎖定。

  • 保留讀取鎖定的時間長度。

  • 讀取作業是否參考另一個交易修改的資料列：

    • 封鎖資料列上的獨佔鎖定直到釋放它為止。

    • 擷取在啟動陳述式或交易時即存在的資料列認可版本。

    • 讀取未認可的資料修改。

選擇交易隔離等級並不會影響為保護資料修改所取得的鎖定。交易永遠都會取得它所修改之資料的獨佔鎖定，並保留該鎖定直到交易完成為止，不論為該交易所設定的隔離等級為何皆同。對於讀取作業，交易隔離等級主要是定義對於其他交易所做修改之影響的保謢等級。

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

Database Engine 隔離等級

ISO 標準會定義以下隔離等級，SQL Server Database Engine 支援所有的這些隔離等級：

  • 讀取未認可 (隔離交易的最低等級，僅能確保不會讀取實體上已損毀的資料)。

  • 讀取認可 (「Database Engine」的預設層級)

  • 可重複讀取

  • 可序列化 (最高的等級，使交易完全與其他交易隔離)

重要事項重要事項

當您要求可序列化隔離等級時，複寫資料表上的 DDL 作業和交易可能會失敗。這是因為複寫查詢所使用的提示可能與可序列化隔離等級不相容。

SQL Server 也支援兩個使用資料列版本控制的交易隔離等級。其中一個是讀取認可隔離的新實作，而另一個則是新的交易隔離等級，也就是快照。

  • 當 READ_COMMITTED_SNAPSHOT 資料庫選項設為 ON 時，讀取認可隔離會使用資料列版本控制以提供陳述式層級的讀取一致性。讀取作業只需要 SCH-S 資料表層級的鎖定，並不需要頁面或資料列的鎖定。當 READ_COMMITTED_SNAPSHOT 資料庫選項設為預設值 OFF 時，讀取認可隔離的運作方式會與舊版的 SQL Server 之運作方式相同。這兩種實作都符合讀取認可隔離的 ANSI 定義。

  • 快照隔離等級使用資料列版本控制來提供交易層級的讀取一致性。讀取作業並不需要頁面或資料列的鎖定，只需要 SCH-S 資料表鎖定。當讀取其他交易所修改的資料列時，它們會擷取在啟動交易時就已經存在的資料列版本。只有當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 時，您才能針對資料庫使用快照集隔離。根據預設，使用者資料庫的此選項為 OFF。

[!附註]

SQL Server 不支援中繼資料的版本控制。因此，哪些 DDL 作業可以在快照隔離之下執行的明確交易中執行會有一些限制。快照集隔離之下的 BEGIN TRANSACTION 陳述式之後不允許有下列 DDL 陳述式：ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME 或是任何 Common Language Runtime (CLR) DDL 陳述式。當您在隱含交易內使用快照隔離時，便允許這些陳述式。就定義而言，隱含交易是一種單一陳述式，可強制使用快照隔離的語意 (即使是 DDL 陳述式)。違反這個原則可能會造成錯誤 3961：「資料庫 '%.*ls' 中的快照集隔離交易失敗，因為這個交易啟動之後，另一個並行交易的 DDL 陳述式修改了此陳述式存取的物件。這是不允許的，因為中繼資料並未建立版本。如果在快照集隔離下並行更新中繼資料，將會造成不一致的問題。」

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

隔離等級

中途讀取 (Dirty read)

非可重複讀取

幽靈 (Phantom)

讀取未認可

是

是

是

讀取認可

否

是

是

可重複讀取

否

否

是

快照集

否

否

否

可序列化

否

否

否

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