Postgresのトランザクション分離レベルについての備忘録

postgresql

はじめに

最近データベース(Postgres)について興味があり時間があれば調べていました。 トランザクション分離レベルについての理解度が浅すぎると思い、手を動かしながら確認することで理解度をあげることができたので備忘録を残します。

特に、今回はSERIALIZABLE の挙動について確認しました。

PostgreSQL: Documentation: 16: 13.2. Transaction Isolation

環境

  • docker : postgres:12.17-alpine3.19
 SELECT version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.17 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
(1 row)

準備

  • テーブルの作成
  • 初期データを作成

テーブルの作成

CREATE TABLE my_table (
    v INT
);

初期データを作成

INSERT INTO my_table (v) VALUES (3);
INSERT INTO my_table (v) VALUES (4);

SELECT * FROM my_table;
 v
---
 3
 4

確認

  • ファントムリードやノンリピータブルリードが起きないことを確認
  • シリアライゼーションアノマリーが起きないか確認

ファントムリードやノンリピータブルリードが起きないことを確認

最初に1つトランザクション(A)を開始してSERIALIZABLEにします。SHOWを利用して、トランザクション内の分離レベルを確認できます。

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW TRANSACTION ISOLATION LEVEL;
 transaction_isolation
-----------------------
 serializable

もう1つトランザクション(B)を開始します

BEGIN;

Bで新しくインサートしてコミットしてデータを確認してみます。5が挿入されています

INSERT INTO my_table (v) VALUES (5);
COMMIT;

SELECT * FROM my_table;
 v
---
 3
 4
 5

Aでデータを確認してみると、5は見えていないので、ファントムリードされないことを確認できます。

 SELECT * FROM my_table;
 v
---
 3
 4

同様にして、Bでデータを書き換えても、Aは更新されていないので、ノンリピータブルリードが起きていないことを確認できます。

UPDATE my_table SET v = 4 WHERE v = 3;
COMMIT;
SELECT * FROM my_table;
 v
---
 4
 5
 4

シリアライゼーションアノマリーの問題が起きないか確認

一番大きい値を１件取得して +1 した値をレコードに挿入するとします。
実現するには、下記のようなクエリになると思います。

INSERT INTO my_table (v) SELECT v + 1 FROM my_table ORDER BY v DESC LIMIT 1;

2つ新しくトランザクション(A,B)を開始してどちらも、SERIALIZABLEに設定してデータを挿入します。

Aのトランザクションで6が追加される

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO my_table (v) SELECT v + 1 FROM my_table ORDER BY v DESC LIMIT 1;
SELECT * FROM my_table;
 v
---
 4
 5
 4
 6

Bのトランザクションでも6が追加される

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO my_table (v) SELECT v + 1 FROM my_table ORDER BY v DESC LIMIT 1;
SELECT * FROM my_table;
 v
---
 4
 5
 4
 6

Aのトランザクションをコミット

COMMIT;
SELECT * FROM my_table;
 v
---
 4
 5
 4
 6

Bのトランザクションでコミット

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.

後（B）でコミットしたトランザクションはエラーとなりロールバックされます。 Bは、先(A)で変更されたトランザクションの状態を反映していないのでエラーとなり、データの挿入を防ぎシリアライゼーションアノマリーの問題を防ぐことができます。

詳細は下記の閲覧をおすすめします。
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE

最後に

全体的に、トランザクション分離レベルについての知識は浅く特に SERIALIZABLE に関してはほぼ何もわかっていませんでした。 手を動かして、動作を確認できて理解をあげることができたと思います。