Babelfish でのトランザクション分離レベル

Babelfish は、トランザクション分離レベル READ UNCOMMITTED、READ COMMITTED、SNAPSHOT をサポートしています。Babelfish 3.4 バージョン以降、追加の分離レベル REPEATABLE READ と SERIALIZABLE がサポートされています。Babelfish のすべての分離レベルは、PostgreSQL の対応する分離レベルの動作でサポートされています。SQL Server と Babelfish は、トランザクション分離レベル (同時アクセスのブロック、トランザクションによって保持されるロック、エラー処理など) を実装するための基盤となるさまざまなメカニズムを使用します。また、同時アクセスがワークロードによってどのように機能するかには微妙な違いがいくつかあります。この PostgreSQL の動作の詳細については、「トランザクション分離」を参照してください。

トランザクション分離レベルの概要。

元の SQL Server トランザクション分離レベルは、データのコピーが 1 つしか存在せず、クエリがデータにアクセスする前に行などのリソースをロックする必要がある悲観的ロックに基づいています。後に、Read Committed 分離レベルのバリエーションが導入されました。これにより、行バージョンを使用することで、ノンブロッキングアクセスを使用するリーダーとライター間の同時実行性が向上します。さらに、スナップショットと呼ばれる新しい分離レベルも利用できるようになりました。これも行バージョンを使用して、トランザクションの終了まで保持される読み取りデータの共有ロックを回避することで、REPEATABLE READ 分離レベルよりも優れた同時実行性を実現します。

SQL Server とは異なり、Babelfish のすべてのトランザクション分離レベルは楽観的ロック (MVCC) に基づいています。各トランザクションは、基になるデータの現在の状態に関係なく、ステートメントの先頭 (READ COMMITTED) またはトランザクションの先頭 (REPEATABLE READ、SERIALIZABLE) にデータのスナップショットを表示します。したがって、Babelfish での同時トランザクションの実行動作は SQL Server とは異なる場合があります。

例えば、分離レベル SERIALIZABLE のトランザクションのうち、最初に SQL Server でブロックされ、後に成功したトランザクションがあるとします。同じ行を読み取るまたは更新する同時トランザクションとのシリアル化の競合により、Babelfish で失敗する可能性があります。また、複数の同時トランザクションを実行すると、SQL Server と比較して Babelfish の最終結果が異なる場合もあります。分離レベルを使用するアプリケーションは、同時実行シナリオについて徹底的にテストする必要があります。

SQL Server の分離レベル	Babelfish 分離レベル	PostgreSQL 分離レベル	コメント
READ UNCOMMITTED	READ UNCOMMITTED	READ UNCOMMITTED	Read Uncommitted は、Babelfish/PostgreSQL で Read Committed と同じです
READ COMMITTED	READ COMMITTED	READ COMMITTED	SQL Server Read Committed は悲観的ロックベースで、Babelfish Read Committed はスナップショット (MVCC) ベースです。
READ COMMITTED SNAPSHOT	READ COMMITTED	READ COMMITTED	どちらもスナップショット (MVCC) ベースですが、まったく同じではありません。
SNAPSHOT	SNAPSHOT	REPEATABLE READ	まったく同じです。
REPEATABLE READ	REPEATABLE READ	REPEATABLE READ	SQL Server Repeatable Read は悲観的ロックベースで、Babelfish Repeatable Read はスナップショット (MVCC) ベースです。
SERIALIZABLE	SERIALIZABLE	SERIALIZABLE	SQL Server Serializable は悲観的分離であり、Babelfish Serializable はスナップショット (MVCC) ベースです。

注記

テーブルヒントは現在サポートされておらず、その動作は Babelfish の定義済みエスケープハッチ escape_hatch_table_hints を使用して制御されます。

トランザクション分離レベルのセットアップ

次のコマンドを使用して、トランザクション分離レベルを設定します。

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

トランザクション分離レベルの有効化または無効化

Babelfish では、トランザクション分離レベル REPEATABLE READ と SERIALIZABLE はデフォルトで無効になっているため、sp_babelfish_configure を使用して babelfishpg_tsql.isolation_level_serializable または babelfishpg_tsql.isolation_level_repeatable_read エスケープハッチを pg_isolation に設定して明示的に有効にする必要があります。詳細については、「エスケープハッチ処理時の Babelfish のエラー処理の管理」を参照してください。

以下は、それぞれのエスケープハッチを設定して、現在のセッションで REPEATABLE READ と SERIALIZABLE の使用を有効または無効にする例です。オプションで、現在のセッションとそれ以降のすべての新しいセッションのエスケープハッチを設定する server パラメータを含めます。

現在のセッションでのみ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ の使用を有効にする場合。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'
	        

現在のセッションおよびすべての後続の新しいセッションで SET TRANSACTION ISOLATION LEVEL REPEATABLE READ の使用を有効にする場合。

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

現在のセッションおよび後続の新しいセッションで SET TRANSACTION ISOLATION LEVEL REPEATABLE READ の使用を無効にする場合。

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

現在のセッションでのみ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE の使用を有効にする場合。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'
	        

現在のセッションおよびすべての後続の新しいセッションで SET TRANSACTION ISOLATION LEVEL SERIALIZABLE の使用を有効にする場合。

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

現在のセッションおよび後続の新しいセッションで SET TRANSACTION ISOLATION LEVEL SERIALIZABLE の使用を無効にする場合。

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

Babelfish と SQL Server の分離レベルの違い

以下は、SQL Server と Babelfish が ANSI 分離レベルを実装する方法に関するニュアンスの例です。

注記

• Babelfish では、分離レベル Repeatable Read とスナップショットは同じです。

• Babelfish では、Read Uncommitted と Read Committed は同じです。

次の例は、以下で説明するすべての例においてベーステーブルを作成する方法を示しています。

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);

トピック

• BABELFISH READ UNCOMMITTED と SQL SERVER READ UNCOMMITTED 分離レベルの比較
• BABELFISH READ COMMITTED と SQL SERVER READ COMMITTED 分離レベルの比較
• BABELFISH READ COMMITTED と SQL SERVER READ COMMITTED スナップショット分離レベルの比較
• BABELFISH REPEATABLE READ と SQL SERVER REPEATABLE READ 分離レベルの比較
• BABELFISH SERIALIZABLE と SQL SERVER SERIALIZABLE 分離レベルの比較

BABELFISH READ UNCOMMITTED と SQL SERVER READ UNCOMMITTED 分離レベルの比較

SQL SERVER の DIRTY READS
トランザクション 1	トランザクション 2	SQL Server Read Uncommitted	Babelfish Read Uncommitted
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	UPDATE employee SET age=0;	更新に成功。	更新に成功。
	INSERT INTO employee VALUES (4, 'D', 40);	挿入に成功。	挿入に成功。
SELECT * FROM employee;		トランザクション 1 では、トランザクション 2 からのコミットされていない変更が表示されます。	Babelfish での Read Committed と同じです。トランザクション 2 からのコミットされていない変更は、トランザクション 1 には表示されません。
	COMMIT
SELECT * FROM employee;		トランザクション 2 によってコミットされた変更が表示されます。	トランザクション 2 によってコミットされた変更が表示されます。

BABELFISH READ COMMITTED と SQL SERVER READ COMMITTED 分離レベルの比較

READ - WRITE BLOCKING
トランザクション 1	トランザクション 2	SQL Server Read Committed	Babelfish Read Committed
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM employee;
	UPDATE employee SET age=100 WHERE id = 1;	更新に成功。	更新に成功。
UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);		トランザクション 2 がコミットされるまでステップがブロックされます。	トランザクション 2 の変更はまだ表示されていません。Updates row with id=3.
	COMMIT	トランザクション 2 は正常にコミットされます。トランザクション 1 はブロック解除され、トランザクション 2 からの更新が表示されます。	トランザクション 2 は正常にコミットされます。
SELECT * FROM employee;		トランザクション 1 は id = 1 で行を更新します。	トランザクション 1 は id = 3 で行を更新します。

BABELFISH READ COMMITTED と SQL SERVER READ COMMITTED スナップショット分離レベルの比較

新しく挿入された行のブロック動作
トランザクション 1	トランザクション 2	SQL Server Read Committed スナップショット	Babelfish Read Committed
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO employee VALUES (4, 'D', 40);
	UPDATE employee SET age = 99;	トランザクション 1 がコミットされるまでステップがブロックされます。挿入された行はトランザクション 1 によってロックされます。	3 行を更新しました。新しく挿入された行はまだ表示されません。
COMMIT		コミットは成功しました。トランザクション 2 のブロックが解除されました。	コミットは成功しました。
	SELECT * FROM employee;	All 4 rows have age=99.	id = 4 の行は、更新クエリ中にトランザクション 2 に表示されなかったため、保持時間の値は 40 です。その他の行は age=99 に更新されます。

BABELFISH REPEATABLE READ と SQL SERVER REPEATABLE READ 分離レベルの比較

読み取り/書き込みブロック動作
トランザクション 1	トランザクション 2	SQL Server Repeatable Read	Babelfish Repeatable Read
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employee;
UPDATE employee SET name='A_TXN1' WHERE id=1;
	SELECT * FROM employee WHERE id != 1;
	SELECT * FROM employee;	トランザクション 2 はトランザクション 1 がコミットされるまでブロックされます。	トランザクション 2 は正常に進みます。
COMMIT
	SELECT * FROM employee;	トランザクション 1 からの更新が表示されます。	トランザクション 1 からの更新が表示されません。
COMMIT
	SELECT * FROM employee;	トランザクション 1 からの更新が表示されます。	トランザクション 1 からの更新が表示されます。

書き込み/書き込みブロック動作
トランザクション 1	トランザクション 2	SQL Server Repeatable Read	Babelfish Repeatable Read
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE employee SET name='A_TXN1' WHERE id=1;
	UPDATE employee SET name='A_TXN2' WHERE id=1;	トランザクション 2 がブロックされました。	トランザクション 2 がブロックされました。
COMMIT		コミットが成功し、トランザクション 2 のブロックが解除されました。	コミットが成功し、トランザクション 2 がエラーで失敗し、同時更新によりアクセスをシリアル化できませんでした。
	COMMIT	コミットは成功しました。	トランザクション 2 はすでに中止されています。
	SELECT * FROM employee;	id=1 の行に name='A_TX2' があります。	id=1 の行に name='A_TX1' があります。

PHANTOM READ
トランザクション 1	トランザクション 2	SQL Server Repeatable Read	Babelfish Repeatable Read
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employee;
	INSERT INTO employee VALUES (4, 'NewRowName', 20);	トランザクション 2 はブロッキングなしで進行します。	トランザクション 2 はブロッキングなしで進行します。
	SELECT * FROM employee;	新しく挿入された行が表示されます。	新しく挿入された行が表示されます。
	COMMIT
SELECT * FROM employee;		トランザクション 2 によって挿入された新しい行が表示されます。	トランザクション 2 によって挿入された新しい行が表示されません。
COMMIT
SELECT * FROM employee;		新しく挿入された行が表示されます。	新しく挿入された行が表示されます。

さまざまな最終結果
トランザクション 1	トランザクション 2	SQL Server Repeatable Read	Babelfish Repeatable Read
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE employee SET age = 100 WHERE age IN (SELECT MIN(age) FROM employee);		トランザクション 1 は id 1 で行を更新します。	トランザクション 1 は id 1 で行を更新します。
	UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);	SELECT ステートメントはトランザクション 1 の UPDATE クエリによってロックされた行の読み取りを試みるため、トランザクション 2 はブロックされます。	読み取りがブロックされないため、トランザクション 2 はブロックなしで進行します。SELECT ステートメントが実行され、トランザクション 1 の変更がまだ表示されないため、id = 3 の行が最終的に更新されます。
	SELECT * FROM employee;	このステップは、トランザクション 1 がコミットされた後に実行されます。id = 1 の行は、前のステップでトランザクション 2 によって更新され、ここに表示されます。	id = 3 の行はトランザクション 2 によって更新されます。
COMMIT		トランザクション 2 のブロックが解除されました。	コミットは成功しました。
	COMMIT
SELECT * FROM employee;		どちらのトランザクションも、id = 1 の行で更新されます。	トランザクション 1 と 2 によって異なる行が更新されます。

BABELFISH SERIALIZABLE と SQL SERVER SERIALIZABLE 分離レベルの比較

SQL SERVER の範囲ロック
トランザクション 1	トランザクション 2	SQL Server Serializable	Babelfish Serializable
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;	SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;
SELECT * FROM employee;
	INSERT INTO employee VALUES (4, 'D', 35);	トランザクション 2 はトランザクション 1 がコミットされるまでブロックされます。	トランザクション 2 はブロッキングなしで進行します。
	SELECT * FROM employee;
COMMIT		トランザクション 1 は正常にコミットされます。トランザクション 2 のブロックが解除されました。	トランザクション 1 は正常にコミットされます。
	COMMIT
SELECT * FROM employee;		新しく挿入された行が表示されます。	新しく挿入された行が表示されます。

さまざまな最終結果
トランザクション 1	トランザクション 2	SQL Server Serializable	Babelfish Serializable
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;	SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;
	INSERT INTO employee VALUES (4, 'D', 40);
UPDATE employee SET age =99 WHERE id = 4;		トランザクション 1 はトランザクション 2 がコミットされるまでブロックされます。	トランザクション 1 はブロッキングなしで進行します。
	COMMIT	トランザクション 2 は正常にコミットされます。トランザクション 1 のブロックが解除されました。	トランザクション 2 は正常にコミットされます。
COMMIT
SELECT * FROM employee;		新しく挿入された行は、保持時間の値 = 99 で表示されます。	新しく挿入された行は、保持時間の値 = 40 で表示されます。

一意の制約でのテーブルへの挿入
トランザクション 1	トランザクション 2	SQL Server Serializable	Babelfish Serializable
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;	SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;
	INSERT INTO employee VALUES (4, 'D', 40);
INSERT INTO employee VALUES ((SELECT MAX(id)+1 FROM employee), 'E', 50);		トランザクション 1 はトランザクション 2 がコミットされるまでブロックされます。	トランザクション 1 はトランザクション 2 がコミットされるまでブロックされます。
	COMMIT	トランザクション 2 は正常にコミットされます。トランザクション 1 のブロックが解除されました。	トランザクション 2 は正常にコミットされます。エラー重複キー値で中止されたトランザクション 1 は、一意の制約に違反します。
COMMIT		トランザクション 1 は正常にコミットされます。	トランザクション 1 のコミットは失敗し、トランザクション間の読み取り/書き込み依存関係によりアクセスをシリアル化できませんでした。
SELECT * FROM employee;		行 (5, 'E', 50) が挿入されます。	4 行のみ存在します。

Babelfish では、分離レベルでシリアル化可能な同時トランザクションは、これらのトランザクションの実行がそれらのトランザクションの可能なすべてのシリアル (一度に 1 つ) 実行と一致しない場合、シリアル化異常エラーで失敗します。

シリアル化異常
トランザクション 1	トランザクション 2	SQL Server Serializable	Babelfish Serializable
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;	SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;
SELECT * FROM employee;
UPDATE employee SET age=5 WHERE age=10;
	SELECT * FROM employee;	トランザクション 2 はトランザクション 1 がコミットされるまでブロックされます。	トランザクション 2 はブロッキングなしで進行します。
	UPDATE employee SET age=35 WHERE age=30;
COMMIT		トランザクション 1 は正常にコミットされます。	トランザクション 1 は最初にコミットされ、正常にコミットできます。
	COMMIT	トランザクション 2 は正常にコミットされます。	トランザクション 2 のコミットはシリアル化エラーで失敗し、トランザクション全体がロールバックされました。トランザクション 2 を再試行します。
SELECT * FROM employee;		両方のトランザクションからの変更が表示されます。	トランザクション 2 はロールバックされました。トランザクション 1 の変更のみが表示されます。

Babelfish では、すべての同時トランザクションが分離レベル SERIALIZABLE で実行されている場合にのみ、シリアル化異常が発生する可能性があります。例えば、上記の例で、代わりにトランザクション 2 を分離レベル REPEATABLE READ に設定するとします。

トランザクション 1	トランザクション 2	SQL Server 分離レベル	Babelfish 分離レベル
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employee;
UPDATE employee SET age=5 WHERE age=10;
	SELECT * FROM employee;	トランザクション 2 はトランザクション 1 がコミットされるまでブロックされます。	トランザクション 2 はブロッキングなしで進行します。
	UPDATE employee SET age=35 WHERE age=30;
COMMIT		トランザクション 1 は正常にコミットされます。	トランザクション 1 は正常にコミットされます。
	COMMIT	トランザクション 2 は正常にコミットされます。	トランザクション 2 は正常にコミットされます。
SELECT * FROM employee;		両方のトランザクションからの変更が表示されます。	両方のトランザクションからの変更が表示されます。