Redshiftのトランザクション分離レベルを動かしながら確認してみる

はじめに

Redshiftのデフォルトのトランザクション分離レベルは「SERIALIZABLE」です。※注意
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_serial_isolation.html

※追記 2024/7/26

• 2024年5月22日より、プロビジョンド版の新規または復元したクラスタは、スナップショット分離がデフォルトとなりました。
  • https://aws.amazon.com/jp/about-aws/whats-new/2024/05/amazon-redshift-snapshot-isolation-provisioned-clusters/
  • 本記事は、「SERIALIZABLE」を前提とした記事となっています。

また、Redshiftのロック単位はテーブルです。

まず、私はこの手の話に疎いです。

• トランザクション1が更新系
• トランザクション2が参照系

であった場合、他の方の記事の知識を借りて、「Serializableなら更新途中のデータを参照してしまうことは無いんだな」というところまではわかります。

しかし、以下のように更新系同士のケースを考えてみると、少し分からなくなります。

• トランザクション1(以後、T1)が更新系
• トランザクション2(以後、T2)が更新系

公式を見ると一応説明は書いてあるのですが、直感的に分かりづらく腑に落ちません。

Amazon Redshift では、同時書き込みオペレーションはテーブルの書き込みロックと直列化分離を利用して安全にサポートされます。直列化分離では、テーブルに対して実行されるトランザクションは、そのテーブルに対して実行される唯一のトランザクションであるという錯覚が守られます。

「錯覚が守られる」とは？

例えば、T1 と T2 という 2 つの同時実行トランザクションで次の少なくとも 1 つとして同じ結果が生成されます。
　　T1 と T2 がこの順序で連続して実行されます。
　　T2 と T1 がこの順序で連続して実行されます。

日本語でおk。よしこんなときは英語版だ。

For example, two concurrently running transactions, T1 and T2, must produce the same results as at least one of the following:
　　T1 and T2 run serially in that order.
　　T2 and T1 run serially in that order.

うん、英語版でもわからない！

同時トランザクションの任意の直列実行において、同時実行と同じ結果が生成された場合は、そのトランザクションは「直列化可能」とみなされるので、安全に実行できます。

ここも突如「同時実行」という未定義の概念が登場しわけがわからなくなります。
ここでいう「同時」とは？　同一テーブルへのトランザクションが重複すること？　それとも（並列CPUだとして）時間的に狂いなく同時にコミットすること？

そのようなトランザクションの直列実行で、同じ結果が生成されない場合、直列化可能性を阻害する可能性のあるステートメントを実行するトランザクションが中断され、ロールバックされます。

トランザクション2は、正常実行されるのか、それともロールバックされるのか？　なにやら条件で変わるようですがその肝心の条件がよくわかりません。

ということで、よくわからないので試してみることにしました。

検証

１．後続トランザクションが成功するケース(その1)

• 以下のようなケースをテストします。
  • トランザクション1
    • UPDATEのみ実施
  • トランザクション2
    • UPDATEのみ実施
      • トランザクション1と異なるレコードの更新

まずテスト用のテーブルを作成します。

CREATE TABLE table1(id int primary key, name varchar(16));
INSERT INTO table1 (id, name) VALUES(1, 'boy');
INSERT INTO table1 (id, name) VALUES(2, 'girl');

1つ目のSQLセッションでトランザクションを開始し、"boy"を"man"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。
この場合、UPDATE行まで正常に完了します。

BEGIN;
UPDATE table1 SET name='man' WHERE id=1;

--COMMIT;

確認用に別のセッションでテーブルの中身を見ると、まだ更新されていないことが確認できます。

select * from table1 order by id;

id	name
1	boy
2	girl

コミットしていないわけですから、これは当然の結果です。

今度は2つ目のSQLセッションでトランザクションを開始し、"girl"を"woman"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。

BEGIN;
UPDATE table1 SET name='woman' WHERE id=2;

--COMMIT;

この場合、セッション1のSQL文がテーブルをロックしたままになるため、セッション2のUPDATE行は正常完了せず、待ち状態になります。

Redshiftはデフォルトのクエリタイムアウトが0(無し)となっているため、(おそらく)永遠に待ちが発生します。

確認用のセッションでテーブルの中身を見ると、まだ更新されていないことが確認できます。

id	name
1	boy
2	girl

これは、公式の以下の状態にあたります。

各同時トランザクションにより、トランザクションの始めにデータベースのスナップショットが作成されます。

セッション1のトランザクションでスナップショットが作成されたことにより、セッション2は、セッション1がUPDATEなどの処理をかける前の状態のスナップショットデータを参照しているためだと思われます。

では1つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='man' WHERE id=1;

COMMIT;

1つ目のSQLセッションでCOMMITした瞬間、2つ目のSQLセッションのUPDATE文の待ちが解消され、UPDATE文が正常に完了します。(2つ目のセッションでは未コミット状態）

確認用のセッションでテーブルの中身を見ると、セッション1の更新が反映されていることが確認できます。

id	name
1	man
2	girl

2つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='woman' WHERE id=2;

COMMIT;

確認用のセッションでテーブルの中身を見ると、セッション2の更新が反映されていることが確認できます。

id	name
1	man
2	woman

• 理屈はおいておいて、以下の処理であれば後続(T2)が正常実行されることは確認できました。
  • トランザクション1による「boy -> man」への更新
  • トランザクション2による「girl -> woman」への更新

テストが終わったので、テーブルをいったんドロップします。

drop table table1;

２．後続トランザクションが成功するケース(その2)

今度は先程と異なり、同じレコードを更新してみます。

• 以下のようなケースをテストします。
  • トランザクション1
    • UPDATEのみ実施
  • トランザクション2
    • UPDATEのみ実施
      • トランザクション1と同じレコードの更新

まずテスト用のテーブルを作り直します。

DROP TABLE table1;
CREATE TABLE table1(id int primary key, name varchar(16));
INSERT INTO table1 (id, name) VALUES(1, 'boy');
INSERT INTO table1 (id, name) VALUES(2, 'girl');

1つ目のSQLセッションでトランザクションを開始し、"boy"を"man"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。
この場合、UPDATE行まで正常に完了します。

BEGIN;
UPDATE table1 SET name='man' WHERE id=1;

--COMMIT;

今度は2つ目のSQLセッションでトランザクションを開始し、id=1の行(boy or man)を"oldman"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。

BEGIN;
UPDATE table1 SET name='oldman' WHERE id=1;
--COMMIT;

セッション1のSQL文がテーブルをロックしたままになるため、セッション2のUPDATE行は正常完了せず、待ち状態になります。

では1つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='man' WHERE id=1;

COMMIT;

1つ目のSQLセッションでCOMMITした瞬間、2つ目のSQLセッションのUPDATE文の待ちが解消され、UPDATE文が正常に完了します。

てっきりトランザクション2はロールバックされるものだと思いこんでいたため、この結果は個人的に予想外でした。

確認用のセッションでテーブルの中身を見ると、セッション1の更新が反映されていることが確認できます。

2つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='oldman' WHERE id=1;

COMMIT;

確認用のセッションでテーブルの中身を見ると、セッション2の更新が反映されていることが確認できます。

• つまり今回は以下の順番で、同一レコードの同時更新を実施しました。
  • T1 ([id1]-> man) が先にトランザクションを開始した。
  • T2 ([id1]-> oldman) が次にトランザクションを開始した。

これは公式の以下の表現にしたがえば、「安全に実行できない」（ロールバックする）ように思えます。

同時トランザクションの任意の直列実行において、同時実行と同じ結果が生成された場合は、そのトランザクションは「直列化可能」とみなされるので、安全に実行できます。

「同時実行」の定義がやはりよくわかりませんが、T1とT2が「同時実行」された場合、結果がmanになるかoldmanになるかは不定のはずです。

私の解釈力が足りていないせいなのかわかりませんが、公式の表現にあてはめて考えるよりも、検証結果の動きを純粋に理解するほかなさそうです。

３．後続トランザクションが成功するケース(その3)

今度は、トランザクション2に複数のDML文を入れてみます。

• 以下のようなケースをテストします。
  • トランザクション1
    • UPDATEのみ実施
  • トランザクション2
    • UPDATEを実施
    • SELECTを実施

トランザクション2では、UPDATEの後にSELECTを実行しています。

まずテスト用のテーブルを作り直します。

DROP TABLE table1;
CREATE TABLE table1(id int primary key, name varchar(16));
INSERT INTO table1 (id, name) VALUES(1, 'boy');
INSERT INTO table1 (id, name) VALUES(2, 'girl');

1つ目のSQLセッションでトランザクションを開始し、"boy"を"man"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。
この場合、UPDATE行まで正常に完了します。

BEGIN;
UPDATE table1 SET name='man' WHERE id=1;

--COMMIT;

今度は2つ目のSQLセッションでトランザクションを開始し、"girl"を"woman"にUPDATEしたうえで、 対象のテーブルをSELECTします。
このとき、COMMITはコメントアウトしておきます。

BEGIN;
UPDATE table1 SET name='woman' WHERE id=2;
SELECT * from table1;

--COMMIT;

セッション1のSQL文がテーブルをロックしたままになるため、セッション2のUPDATE行は正常完了せず、待ち状態になります。

では1つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='man' WHERE id=1;

COMMIT;

1つ目のSQLセッションでCOMMITした瞬間、2つ目のSQLセッションのUPDATE文の待ちが解消され、UPDATE文が正常に完了します。

2つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='woman' WHERE id=2;
-- SELECT * from table1;

COMMIT;

確認用のセッションでテーブルの中身を見ると、セッション2の更新が反映されていることが確認できます。

つまり、UPDATEのあとにSELECTする場合では、正常に実行できるようです。

４．後続トランザクションがロールバックするケース

今度は、トランザクション2のUPDATE , SELECTの順番を入れ替えてみます。

• 以下のようなケースをテストします。
  • トランザクション1
    • UPDATEのみ実施
  • トランザクション2
    • SELECTを実施
    • UPDATEを実施

先程と違い、トランザクション2では、UPDATEの前にSELECTを実行しています。

まずテスト用のテーブルを作り直します。

DROP TABLE table1;
CREATE TABLE table1(id int primary key, name varchar(16));
INSERT INTO table1 (id, name) VALUES(1, 'boy');
INSERT INTO table1 (id, name) VALUES(2, 'girl');

1つ目のSQLセッションでトランザクションを開始し、"boy"を"man"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。
この場合、UPDATE行まで正常に完了します。

BEGIN;
UPDATE table1 SET name='man' WHERE id=1;

--COMMIT;

確認用に別のセッションでテーブルの中身を見ると、まだ更新されていないことが確認できます。

select * from table1 order by id;

id	name
1	boy
2	girl

今度は2つ目のSQLセッションでトランザクションを開始し、対象のテーブルをSELECTしたうえで、 "girl"を"woman"にUPDATEします。
このとき、COMMITはコメントアウトしておきます。

BEGIN;
SELECT * from table1;
UPDATE table1 SET name='woman' WHERE id=2;

--COMMIT;

このセッションのSELECT文では、まだセッション1の更新が反映されていない結果が返ります。

id	name
1	boy
2	girl

1つ目のSQLセッションに戻り、COMMITを実行します。
COMMITは正常に終了します。

-- BEGIN;
-- UPDATE table1 SET name='man' WHERE id=1;

COMMIT;

1つ目のSQLセッションでCOMMITした瞬間、2つ目のSQLセッションのUPDATE文の待ちが解消されます。
しかしUPDATE文はさきほどの例と違って失敗します。

ERROR: 1023 Detail: Serializable isolation violation on table

この結果は、以下の状態に該当するものと思われます。

そのようなトランザクションの直列実行で、同じ結果が生成されない場合、直列化可能性を阻害する可能性のあるステートメントを実行するトランザクションが中断され、ロールバックされます。

UPDATEの前にSELECTを置いたことで、(実際にそういう処理にしていないとしても)SELECTの結果によりUPDATEの内容が変わる可能性をRedshiftが検知し、「直列化可能性を阻害する可能性のあるステートメント」と判断し、ロールバックされたようです。

このままセッション2でCOMMITをします。
COMMIT行自体は正常終了します。

-- BEGIN;
-- SELECT * from table1;
-- UPDATE table1 SET name='woman' WHERE id=2;

COMMIT;

別のセッションで中身を見てみると、セッション1の処理は反映されていますが、セッション2の処理は反映されていません。

id	name
1	man
2	girl

T2はロールバックしたので、当然の結果といえます。

まとめ

今回の検証結果は以下の通りでした。

項番	T1	T2	(T1コミット後の) T2の結果
1	更新	更新(T1と異なるレコード)	実行される
2	更新	更新(T1と同じレコード)	実行される
3	更新	更新+参照	実行される
4	更新	参照+更新	ロールバックされる

やはり実際に試してみないとわからないですね。
トランザクション分離レベルが同じ「SERIALIZABLE」でも、データベースエンジンによって細かい挙動は異なるようです。
公式の説明がいまだに正しく解釈できないのはモヤモヤしますが、動きがわかったのでよしとします。

参考情報

• Redshiftトランザクション関連の参考情報

  • トランザクション関連
    • https://repost.aws/ja/knowledge-center/prevent-locks-blocking-queries-redshift
      • Amazon Redshift でロックを検出し、解除する方法を教えてください
    • https://repost.aws/ja/knowledge-center/redshift-serializable-isolation
      • Redshift のテーブルでの直列化可能分離違反) エラーを解決するにはどうすればよいですか?
  • タイムアウト関連
    • statement_timeout (default : 0 )
      • セッションまたはユーザ単位、パラメータグループ単位での制御
      • WLMでのキュー待ち時間と実行時間の両方を含む
        • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_statement_timeout.html
    • query_execution_time, query_queue_time, query_cpu_time (default : 0 )
      • WLMのQMR単位での制御
      • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/cm-c-defining-query-queues.html#wlm-timeout
      • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html#cm-c-wlm-query-monitoring-metrics
  • デッドロック関連
    • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_write_readwrite.html#c_write_readwrite-potential-deadlock
    • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/queries-troubleshooting.html#queries-troubleshooting-query-hangs

• 一般的なトランザクション関連の参考情報

  • https://qiita.com/dodonki1223/items/69350c46602f51ca56fd
  • https://ja.wikipedia.org/wiki/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB

• その他のDWHのトランザクション関連の参考情報

  • Vertica(9系)
    • デフォルトのトランザクション分離レベルは「Read Commited」
      • https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConceptsGuide/Other/Transactions.htm
      • http://vertica-tech.ashisuto.co.jp/transaction/
    • ロックモード
      • http://vertica-tech.ashisuto.co.jp/lock_mode/
      • https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConceptsGuide/Components/Locks/LockModes.htm
    • ロックタイムアウト(クエリタイムアウトではない点に注意)
      • http://vertica-tech.ashisuto.co.jp/lock-wf/
  • Netezza(7系)
    • デフォルトのトランザクション分離レベルは「serializable」
      • https://www.ibm.com/docs/ja/psfa/7.1.0?topic=control-isolation-level
    • クエリタイムアウト
      • https://manualzz.com/doc/o/lw1rr/ibm-netezza-system-administrator-s-guide-setting-user-and-group-limits

以上です。