巴貝魚的事務隔離級別 - Amazon Aurora
交易隔離層次概觀設定交易隔離層級啟用或停用交易隔離層級巴貝魚和 SQL 服務器隔離級別之間的差異

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

巴貝魚的事務隔離級別

Babelfish 支持事務隔離級別讀取未提交，讀取提交和快照。從 Babelfish 3.4 版本開始，支持額外的隔離級別可重複讀取和序列化。在巴貝爾魚的所有隔離級別與 PostgreSQL 相應的隔離級別的行為支持。SQL Server 和 Babelfish 使用不同的基礎機制來實現事務隔離級別（阻止並發訪問，由事務持有的鎖，錯誤處理等）。而且，對於不同的工作負載，並行存取的運作方式也有一些細微的差異。如需有關此 PostgreSQL 行為的詳細資訊，請參閱交易隔離。

交易隔離層次概觀

原始 SQL Server 交易隔離層級是以悲觀鎖定為基礎，其中只有一個資料副本存在，而且查詢必須先鎖定資源 (例如資料列)，才能存取這些資源。後來，引入了「讀取認可隔離等級」的變體。這可讓使用資料列版本，使用非封鎖存取，在讀取器和寫入器之間提供更佳的並行性。此外，也可使用稱為快照的新隔離層級。它還使用行版本，通過避免對保留到交易結束的讀取數據的讀取數據共享鎖定來提供比可重複讀取隔離級別更好的並發性。

與 SQL Server 不同，巴貝魚中的所有事務隔離級別都是基於樂觀鎖定（MVCC）。無論基礎資料的目前狀態為何，每個交易都會在陳述式的開頭 (讀取已提交) 或交易開頭 (可重複讀取、序列化) 看到資料的快照。因此，巴貝魚中並行交易的執行行為可能與 SQL Server 不同。

例如，假設具有隔離層級序列化的交易，該交易最初在 SQL Server 中被封鎖，但稍後會成功。由於與讀取或更新相同行的並發事務的序列化衝突，最終可能會在 Babelfish 中失敗。在某些情況下，執行多個並發事務會產生與 SQL Server 相比，Babel 魚中的最終結果不同的情況。使用隔離等級的應用程式，應徹底測試並行案例。

SQL 伺服器中的隔離層級 巴貝魚隔離等級 PostgreSQL 等級 說明

讀取未提交

讀取未提交

讀取未提交

讀取未提交的與嬰兒魚/PostgreSQL 中的「讀取已提交」相同

閱讀承諾

閱讀承諾

閱讀承諾

SQL Server 讀取提交是基於悲觀的鎖定，Babelfish 讀取提交是基於快照（MVCC）。

讀取已提交快照

閱讀承諾

閱讀承諾

兩者都是基於快照（MVCC），但不完全相同。

快照

快照

可重複讀取

完全一樣。

可重複讀取

可重複讀取

可重複讀取

SQL Server 可重複讀取是基於悲觀的鎖定，巴貝爾魚可重複讀取是基於快照（MVCC）。

可序列化

可序列化

可序列化

SQL 服務器序列化是悲觀的隔離，巴貝爾魚序列化是基於快照（MVCC）。
注意

目前不支援資料表提示，且其行為是透過使用 Babelfish 預先定義的逸出剖面線來控制。escape_hatch_table_hints

設定交易隔離層級

使用下列命令來設定交易隔離層級：


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

啟用或停用交易隔離層級

事務隔離級別可重複讀取和可序列化在 Babelfish 中默認禁用，並且您必須通過將babelfishpg_tsql.isolation_level_serializable或babelfishpg_tsql.isolation_level_repeatable_read轉義填充設置為使用來明確啟用它們。pg_isolation sp_babelfish_configure如需詳細資訊，請參閱使用逃生艙管理 Babelfish 錯誤處理。

以下是透過設定各自的逸出剖面線，在目前工作階段中啟用或停用使用可重複讀取和序列化的範例。選擇性地包含server參數，以設定目前作業階段以及所有後續新工作階段的逸出剖面線。

啟用 SET 事務隔離級別可重複讀取僅在當前會話中使用。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'

在當前會話和所有後續的新會話中啟用 SET 事務隔離級別可重複讀取的使用。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation', 'server'

若要在目前的工作階段和隨後的新工作階段中停用 SET 交易隔離等級可重複讀取的使用。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'off', 'server'

僅在當前會話中啟用 SET 事務隔離級別可序列化的使用。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'

在目前的工作階段和所有後續的新工作階段中啟用 SET 事務隔離等級可序列化的使用。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation', 'server'

若要在目前的工作階段和隨後的新工作階段中停用 SET 交易隔離等級可序列化的使用。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'off', 'server'

巴貝魚和 SQL 服務器隔離級別之間的差異

下面是關於 SQL Server 和巴貝魚如何實現 ANSI 隔離級別的細微差別的幾個例子。

注意

  • 隔離級別可重複讀取和快照在巴貝爾魚是相同的。

  • 隔離級別讀取未提交和讀取提交在 Babelfish 中是相同的。

下面的例子演示了如何創建下面提到的所有例子的基表：

CREATE TABLE employee (
    id sys.INT NOT NULL PRIMARY KEY,
    name sys.VARCHAR(255)NOT NULL,
    age sys.INT NOT NULL
);
INSERT INTO employee (id, name, age) VALUES (1, 'A', 10);
INSERT INTO employee (id, name, age) VALUES (2, 'B', 20);
INSERT INTO employee (id, name, age) VALUES (3, 'C', 30);

巴貝爾魚讀取未提交與 SQL 服務器讀取未提交的隔離級別

在 SQL 服務器中讀取臟
交易一 交易二 SQL 伺服器讀取未提交 巴貝爾魚閱讀未承諾

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

更新員工設置年齡 = 0;

更新成功。

更新成功。

插入員工價值觀（4，'D'，40）;

插入成功。

插入成功。

從員工中選擇 *;

交易 1 可以查看來自交易 2 的未認可變更。

與在巴貝爾魚中讀取承諾相同。「交易 1」看不到「交易 2」中的未確認變更。

COMMIT

從員工中選擇 *;

查看交易 2 所提交的變更。

查看交易 2 所提交的變更。

巴貝爾魚讀取提交與 SQL 服務器讀取提交的隔離級別

讀-寫阻止
交易一 交易二 SQL 伺服器讀取已提交 巴貝爾魚閱讀致力

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

從員工中選擇 *;

更新員工設置年齡 = 100 其中 ID = 1;

更新成功。

更新成功。

更新員工設置年齡 = 0，其中年齡在（從員工中選擇最大（年齡））;

步驟被阻止，直到交易 2 提交。

交易 2 變更尚不可見。更新與 ID = 3 的行。

COMMIT

交易 2 已成功提交。交易 1 現在已解除封鎖，並會看到「交易 2」的更新。

交易 2 已成功提交。

從員工中選擇 *;

事務 1 更新 ID = 1 的行。

事務 1 更新 ID = 3 的行。

巴貝爾魚讀取提交與 SQL 服務器讀取提交的快照隔離級別

阻止新插入行的行為
交易一 交易二 SQL 伺服器讀取認可的快照 巴貝爾魚閱讀致力

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

插入員工價值觀（4，'D'，40）;

更新員工設置年齡 = 99;

步驟被阻止，直到事務 1 提交。插入的資料列會由交易 1 鎖定。

更新了三行。新插入的列尚不可見。

COMMIT

提交成功。交易 2 現在已解除封鎖。

提交成功。

從員工中選擇 *;

所有 4 行的年齡 = 99。

ID = 4 的資料列具有年齡值 40，因為在更新查詢期間，交易 2 不可見。其他資料列會更新為年齡 = 99。

巴貝爾魚可重複讀取與 SQL 服務器可重複讀取隔離級別

讀/寫阻止行為
交易一 交易二 SQL 伺服器可重複讀取 巴貝爾魚可重複閱讀

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可重複讀取;

設置事務隔離級別可重複讀取;

從員工中選擇 *;

更新員工設置名稱 = 'A_txn1'，其中 ID = 1;

選擇 * 從員工哪裡 ID！ = 1；

從員工中選擇 *;

交易 2 會遭到封鎖，直到交易 1 認可為止。

交易 2 正常進行。

COMMIT

從員工中選擇 *;

「從交易 1 更新」是可見的。

看不到「從交易 1 更新」。

COMMIT

從員工中選擇 *;

從「交易 1」中看到更新。

從「交易 1」中看到更新。
寫入/寫入封鎖行為
交易一 交易二 SQL 伺服器可重複讀取 巴貝爾魚可重複閱讀

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可重複讀取;

設置事務隔離級別可重複讀取;

更新員工設置名稱 = 'A_txn1'，其中 ID = 1;

更新員工設置名稱 = 'A_txn2' 其中 ID = 1;

交易 2 已封鎖。

交易 2 已封鎖。

COMMIT

提交成功且交易 2 已解除封鎖。

提交成功，交易 2 失敗，並且由於並行更新而導致錯誤無法序列化訪問。

COMMIT

提交成功。

交易 2 已中止。

從員工中選擇 *;

具有 ID = 1 的行具有名稱 = 'A_TX2'。

具有 id = 1 的行具有名稱 = 'A_TX1'。
幻影讀取
交易一 交易二 SQL 伺服器可重複讀取 巴貝爾魚可重複閱讀

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可重複讀取;

設置事務隔離級別可重複讀取;

從員工中選擇 *;

插入員工價值觀（4，NewRowName'，20）;

事務 2 繼續沒有任何阻塞。

事務 2 繼續沒有任何阻塞。

從員工中選擇 *;

新插入的列是可見的。

新插入的列是可見的。

COMMIT

從員工中選擇 *;

由交易 2 插入的新資料列是可見的。

不可見由交易 2 插入的新資料列。

COMMIT

從員工中選擇 *;

新插入的列是可見的。

新插入的列是可見的。
不同的最終結果
交易一 交易二 SQL 伺服器可重複讀取 巴貝爾魚可重複閱讀

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可重複讀取;

設置事務隔離級別可重複讀取;

更新員工設置年齡 = 100，其中年齡在（從員工中選擇最小（年齡））;

交易 1 會使用識別碼 1 更新資料列。

交易 1 會使用識別碼 1 更新資料列。

更新員工設置年齡 = 0，其中年齡在（從員工中選擇最大（年齡））;

因為 SELECT 陳述式會嘗試讀取交易 1 中 UPDATE 查詢鎖定的資料列，因此交易 2 會遭到封鎖。

事務 2 繼續沒有任何阻塞，因為讀取永遠不會被阻止，SELECT 語句執行，最後行 ID = 3 更新，因為事務 1 更改尚不可見。

從員工中選擇 *;

這個步驟是在事務 1 已經提交後執行。ID = 1 的行是由事務 2 在前面的步驟更新，並在這裡可見。

識別碼為 3 的資料列會由交易 2 更新。

COMMIT

交易 2 現在已解除封鎖。

提交成功。

COMMIT

從員工中選擇 *;

這兩個事務在 ID = 1 的行上執行更新。

不同的資料列會由交易 1 和 2 更新。

巴貝爾魚可序列化與 SQL 服務器可序列化的隔離級別

SQL 服務器中的範圍鎖定
交易一 交易二 SQL 服務器可序列化 巴贝尔鱼可序列化

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可串聯;

設置事務隔離級別可串聯;

從員工中選擇 *;

插入員工價值觀（4，'D'，35）;

交易 2 會遭到封鎖，直到交易 1 認可為止。

事務 2 繼續沒有任何阻塞。

從員工中選擇 *;

COMMIT

交易 1 已成功提交。交易 2 現在已解除封鎖。

交易 1 已成功提交。

COMMIT

從員工中選擇 *;

新插入的列是可見的。

新插入的列是可見的。
不同的最終結果
交易一 交易二 SQL 服務器可序列化 巴贝尔鱼可序列化

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可串聯;

設置事務隔離級別可串聯;

插入員工價值觀（4，'D'，40）;

更新員工設置年齡 = 99 其中 ID = 4;

交易 1 會遭到封鎖，直到交易 2 認可為止。

事務 1 進行沒有任何阻塞。

COMMIT

交易 2 已成功提交。交易 1 現在已解除封鎖。

交易 2 已成功提交。

COMMIT

從員工中選擇 *;

新插入的行是可見的，年齡值 = 99。

新插入的行是可見的，年齡值 = 40。
插入到具有唯一約束的表
交易一 交易二 SQL 服務器可序列化 巴贝尔鱼可序列化

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可串聯;

設置事務隔離級別可串聯;

插入員工價值觀（4，'D'，40）;

插入員工值觀（（從員工中選擇最大（ID）+1），'E'，50）;

交易 1 會遭到封鎖，直到交易 2 認可為止。

交易 1 會遭到封鎖，直到交易 2 認可為止。

COMMIT

交易 2 已成功提交。交易 1 現在已解除封鎖。

交易 2 已成功提交。事務 1 中止，錯誤重複鍵值違反唯一約束。

COMMIT

交易 1 已成功提交。

事務 1 提交失敗，無法序列化訪問，由於事務之間的讀/寫依賴關係。

從員工中選擇 *;

行（5，'E'，50）被插入。

只有 4 行存在。

在 Babelfish 中，如果這些事務的執行與這些交易的所有可能串行（一次一個）執行不一致，則以隔離級序列化可序列化異常運行的並發事務將失敗並發生序列化異常錯誤。

序列化異常
交易一 交易二 SQL 服務器可序列化 巴贝尔鱼可序列化

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可串聯;

設置事務隔離級別可串聯;

從員工中選擇 *;

更新員工設置年齡 = 5 其中年齡 = 10;

從員工中選擇 *;

交易 2 會遭到封鎖，直到交易 1 認可為止。

事務 2 繼續沒有任何阻塞。

更新員工設置年齡 = 35 歲其中年齡 = 30;

COMMIT

交易 1 已成功提交。

事務 1 首先提交並能夠成功提交。

COMMIT

交易 2 已成功提交。

事務 2 提交失敗，並出現序列化錯誤，整個事務已回滾。重試交易 2。

從員工中選擇 *;

兩個交易的變更都是可見的。

交易 2 已倒回。只會看到交易 1 的變更。

在 Babelfish 中，序列化異常只有在所有並發事務都在隔離級序列化中執行時才可能執行。例如，讓我們採取上面的例子，但設置事務 2 隔離級別可重複讀取代替。

交易一 交易二 SQL 伺服器隔離層級 巴貝魚隔離等級

BEGIN TRANSACTION

BEGIN TRANSACTION

設置事務隔離級別可串聯;

設置事務隔離級別可重複讀取;

從員工中選擇 *;

更新員工設置年齡 = 5 其中年齡 = 10;

從員工中選擇 *;

交易 2 會被封鎖，直到交易 1 認可。

事務 2 繼續沒有任何阻塞。

更新員工設置年齡 = 35 歲其中年齡 = 30;

COMMIT

交易 1 已成功提交。

交易 1 已成功提交。

COMMIT

交易 2 已成功提交。

交易 2 已成功提交。

從員工中選擇 *;

兩個交易的變更都是可見的。

兩個交易的變更都是可見的。