PostgreSQLのシリアライザブルとコミット／ロールバックと遅延可能な読み取り専用トランザクションの関係

はじめに

PostgreSQLのトランザクション分離レベルにはシリアライザブル(Serializable)があります。ドキュメントのシリアライザブル分離レベルの説明には以下の記載があります。

異常を防止するためにシリアライザブルトランザクションを使用するのであれば、恒久的なユーザテーブルから読み取られたいかなるデータも、それを読んだトランザクションがコミットされるまで有効とは認められない点は重要です。このことは読み取り専用トランザクションにも当てはまりますが、遅延可能な読み取り専用トランザクション内で読み込まれたデータは例外で、読み込まれてすぐに有効とみなされます。なぜなら、遅延可能なトランザクションはすべてのデータを読み込む前にこのような問題がないことを保証されているスナップショットを取得できるまで待機するからです。それ以外の全ての場合において、後に中止されたトランザクション内で読み込まれた結果をアプリケーションは信用してはならず、アプリケーションはトランザクションが成功するまで再試行すべきです。
13.2. トランザクションの分離

この説明を実例で確認し、トランザクション設計で注意すべきポイントを探ります。

検証環境・シナリオ

PostgreSQL 10.3 で検証を行いました。

PostgreSQLに Serializable Snapshot Isolation (SSI) が実装されたのは 9.1 からです。よって、9.1 以降であればほぼ同様の結果が得られると思われます。

検証SQL（データ含む）は PostgreSQL wiki の SSI 解説記事（以下、記事）を参照しました。
SSI - PostgreSQL wiki
2.4.1 Deposit Report を参照ください。

記事に従い、postgresql.confのパラメータを以下の設定とします。

default_transaction_isolation = 'serializable'

初期セットアップとして、以下のSQLを実行します。

create table control
  (
    deposit_no int not null
  );
insert into control values (1);
create table receipt
  (
    receipt_no serial primary key,
    deposit_no int not null,
    payee text not null,
    amount money not null
  );
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Crosby', '100');
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Stills', '200');
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Nash', '300');

検証環境のロケールによっては金額表示に円記号"￥"が用いられますが、ここでは記事に沿ってドル記号"$"で表記しています。

検証シナリオ概説

銀行の預金管理を想定しています。（もしかすると、記事では小切手を想定しているかもしれないのですが、シナリオの本質には影響しないので、本エントリでは預金の想定で進めます）。

control テーブルと receipt テーブルがあります。

◆ control テーブル

列名	説明
deposit_no	預金番号。日次バッチ処理の単位となり、締め処理が行われるとインクリメントされ、翌営業日扱いとなります。

◆ receipt テーブル

列名	説明
receipt_no	受領番号。シリアルな値です。
deposit_no	預金番号。control テーブルの deposit_no を参照します。
payee	預金者。
amount	預金額。

シナリオでは以下の3つのトランザクションがほぼ同時に起こります。

トランザクション	説明
T1	預金の預け入れ。
T2	預金の預け入れの締め処理。続けてT3の処理を行う。
T3	T2で締めた預け入れ分の預金の一覧出力。

この時、それぞれのトランザクションがどのように扱われるかを確認します。

実験結果・解説

T1完了前にT2, T3が進行する

記事のオリジナルのケースです。
当日の預金の預け入れ処理(T1)が完了する前に締め処理(T2, T3)が進行します。

BEGIN; -- T1

INSERT INTO receipt
  (deposit_no, payee, amount)
  VALUES
  (
    (SELECT deposit_no FROM control),
    'Young', '100'
  );

SELECT * FROM receipt;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

BEGIN; -- T2

SELECT deposit_no FROM control;

deposit_no
1

-- T2

UPDATE control SET deposit_no = 2;

COMMIT; -- コミット処理が成功する。

BEGIN; -- T3

SELECT * FROM receipt WHERE deposit_no = 1;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00

-- T1

COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

-- T3

COMMIT; -- コミット処理が成功する。

トランザクション間に T1 ⇒ T2 ⇒ T3 ⇒ T1 という順序関係が発生したため、シリアライズすることができず、T1 のコミットに失敗しました。

トランザクションの順序関係に矛盾が生じていることは、T1とT3のSELECT結果が異なることからわかります。 T1 ⇒ T3 が成り立つのであれば、T3にはT1のINSERTが反映されているはずですが、実際には反映されていません。

T2とT3の間にT1がコミットする

オリジナルではT3の SELECT 実行後にT1がコミットを実行（そして失敗）していましたが、T1のコミットがT3の前であればどうであったかを確認します。

BEGIN; -- T1

INSERT INTO receipt
  (deposit_no, payee, amount)
  VALUES
  (
    (SELECT deposit_no FROM control),
    'Young', '100'
  );

SELECT * FROM receipt;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

BEGIN; -- T2

SELECT deposit_no FROM control;

deposit_no
1

-- T2

UPDATE control SET deposit_no = 2;

COMMIT; -- コミット処理が成功する。

-- T1

COMMIT; -- コミット処理が成功する。

BEGIN;  -- T3

SELECT * FROM receipt WHERE deposit_no = 1;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

-- T3

COMMIT; -- コミット処理が成功する。

トランザクションを T1 ⇒ T2 ⇒ T3 という順序関係で解決することができるため、いずれもコミット処理が成功しました。

T1のコミット前にT3がコミットする

オリジナルではT3のコミットが一番最後でしたが、もしT1のコミット前にT3がコミットしたらどうなるかを確認します。

BEGIN; -- T1

INSERT INTO receipt
  (deposit_no, payee, amount)
  VALUES
  (
    (SELECT deposit_no FROM control),
    'Young', '100'
  );

SELECT * FROM receipt;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

BEGIN; -- T2

SELECT deposit_no FROM control;

deposit_no
1

-- T2

UPDATE control SET deposit_no = 2;

COMMIT; -- コミット処理が成功する。

BEGIN;  -- T3

SELECT * FROM receipt WHERE deposit_no = 1;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00

-- T3

COMMIT; -- コミット処理が成功する。

-- T1

COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

T3のコミット処理は成功し、T1のコミット処理を失敗させることで、T2 ⇒ T3 となりました。T3のコミット処理を失敗させて、T1のコミット処理を成功させることで、T1 ⇒ T2 にすることもできたはずですが、そうなりませんでした。

T1のコミット前にT3がロールバックする

前の例ではT1のコミット前にT3がコミットすることで、T1のコミットが失敗しました。ではT3がロールバックすればT3はなかったことになり、T1のコミットは成功するのでしょうか？

BEGIN; -- T1

INSERT INTO receipt
  (deposit_no, payee, amount)
  VALUES
  (
    (SELECT deposit_no FROM control),
    'Young', '100'
  );

SELECT * FROM receipt;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

BEGIN; -- T2

SELECT deposit_no FROM control;

deposit_no
1

-- T2

UPDATE control SET deposit_no = 2;

COMMIT; -- コミット処理が成功する。

BEGIN;  -- T3

SELECT * FROM receipt WHERE deposit_no = 1;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00

-- T3

ROLLBACK; -- ロールバック処理が成功する。

-- T1

COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

T3がロールバックした後にT1がコミットしても、T1のコミットは結局失敗しました。不思議に思えますが、全体でみればT2のみ実行されたことになり、シリアライズは成立しています。推測となりますが、このケースで本来は成功してもよいT1のコミットが失敗するのは PostgreSQL の SSI の設計もしくは実装上の制約と思われます。

T3を遅延可能な読み取り専用トランザクションで実行する

処理時間が長く、途中での失敗を避けたい処理（例：締め処理）であれば、「遅延可能な読み取り専用トランザクション」内で実行することが一つの手法となります。
マニュアルから該当の記述を引用します。

DEFERRABLEトランザクション属性は、トランザクションがSERIALIZABLEかつREAD ONLYである場合のみ効果があります。あるトランザクションでこれら３つの属性がすべて選択されている場合、最初にスナップショットを獲得する時にブロックされる可能性があります。その後、そのトランザクションをSERIALIZABLEトランザクションの通常のオーバーヘッドを伴わず、またシリアライズ処理の失敗を引き起こす恐れやシリアライズ処理の失敗によりキャンセルされる恐れもなく実行することができます。これは時間がかかるレポート処理やバックアップによく適しています。
SET TRANSACTION

BEGIN; -- T1

INSERT INTO receipt
  (deposit_no, payee, amount)
  VALUES
  (
    (SELECT deposit_no FROM control),
    'Young', '100'
  );

SELECT * FROM receipt;

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

BEGIN; -- T2

SELECT deposit_no FROM control;

deposit_no
1

-- T2

UPDATE control SET deposit_no = 2;

COMMIT; -- コミット処理が成功する。

BEGIN;  -- T3

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; -- 遅延可能な読み取り専用トランザクション

SELECT * FROM receipt WHERE deposit_no = 1;
-- レスポンスが保留される。

-- T1

COMMIT; -- コミット処理が成功する。

ここでT3のレスポンスが返ってきます。

receipt_no	deposit_no	payee	amount
1	1	Crosby	$100.00
2	1	Stills	$200.00
3	1	Nash	$300.00
4	1	Young	$100.00

-- T3

COMMIT; -- コミット処理が成功する。

T3のSELECTのレスポンスがT1のコミット処理を待ち合わせたことで、T1 ⇒ T2 ⇒ T3 の順序関係となり、全てのトランザクションが成功しました。

まとめ

シリアライザブル分離レベルではトランザクションがシリアライズに実行されることを保証しています。ただ、PostgreSQLがその保証を満たすためにどのトランザクションを失敗させるかを事前に知ることは困難です。また、トランザクションをロールバックしても、後続の処理に影響を与える場合があります。実行する処理の特性をふまえ、遅延可能な読み取り専用トランザクションで実行するなどの対応が必要です。

ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中