一般的には
| 分離レベル | Dirty Read | Nonrepeatable Read | Phantom Read |
| Read uncommitted | ◯ | ◯ | ◯ |
| Read committed | ✕ | ◯ | ◯ |
| Repeatable read | ✕ | ✕ | ◯ |
| Serializable | ✕ | ✕ | ✕ |
◯は安全という意味ではなくて その現象が発生する可能性があるという意味
✕が多いほど安全
PostgreSQL だと Read uncommitted の Dirty Read と Repeatable read の Phantom Read は発生しないみたい
つまり
| 分離レベル | Dirty Read | Nonrepeatable Read | Phantom Read |
| Read uncommitted | ✕ | ◯ | ◯ |
| Read committed | ✕ | ◯ | ◯ |
| Repeatable read | ✕ | ✕ | ✕ |
| Serializable | ✕ | ✕ | ✕ |
じゃあ 上 2 つと 下 2 つは一緒で実質 2 種類だけかと思ったら
ドキュメント的には Read uncommitted は Read committed と一緒と書いてるけど Repeatable read と Serializable は違いがあるみたい
ドキュメントの表ではもうひとつ Serialization Anomaly (直列化異常) という列があってこれが違うらしい
| 分離レベル | Dirty Read | Nonrepeatable Read | Phantom Read | Serialization Anomaly |
| Read uncommitted | ✕ | ◯ | ◯ | ◯ |
| Read committed | ✕ | ◯ | ◯ | ◯ |
| Repeatable read | ✕ | ✕ | ✕ | ◯ |
| Serializable | ✕ | ✕ | ✕ | ✕ |
Serialization Anomaly って何？と思ってドキュメントを見ると
トランザクションのグループのコミットしたときに そのコミット順が入れ替わると結果が変わる場合が Serialization Anomaly ということみたい
詳細はドキュメントに詳しく書いてるけど長すぎて読む気がしないのでとりあえず実際に試してみる
テーブル用意
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (name text);
INSERT INTO t1 VALUES ('first');
psql を 2 つ同時に実行して それぞれの操作を A と B とする
まずは Serialization Anomaly が発生しない Serializable
-- A
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- B
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- A
INSERT INTO t1 VALUES ('A1');
-- B
INSERT INTO t1 VALUES ('B1');
-- A
COMMIT;
-- B
COMMIT;
postgres=# SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)
並行して INSERT してコミットしたけど問題なし
SELECT が入ると
-- A
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- B
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- A
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)
INSERT INTO t1 VALUES ('A2');
-- B
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)
INSERT INTO t1 VALUES ('B2');
-- A
COMMIT;
-- 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.
postgres=# SELECT * FROM t1;
name
-------
first
A1
B1
A2
(4 rows)
あとからコミットする B でエラーになる
SELECT で今の値を取得してる以上 取得したものを使って INSERT されたものとして扱われて A がコミットされてから B を開始していたら別の結果になったかもしれないと考えられる
B が A のコミット前の状態をもとに更新してるという扱いでエラーのよう
A,B 問わずに数字を連番にしてたとして最後が A10 だとする
A → B の順で並列させずに実行していたら A 側では A11 を INSERT して B 側では A11 があるので B12 を INSERT することになるはず
B → A の順で並列させずに実行していたら B 側では B11 を INSERT して A 側では B11 があるので A12 を INSERT することになるはず
A,B を並列で実行させていたら どちらも SELECT 時は A10 が最後にあるので A11 と B11 を INSERT することになるはず
こういう順番で結果が変わりうる場合にあとからコミットされたほうがエラーになるのが Serializable のはず
これを気にしなくていいなら Repeatable read
-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- B
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- A
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)
INSERT INTO t1 VALUES ('A3');
-- B
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)
INSERT INTO t1 VALUES ('B3');
-- A
COMMIT;
-- B
COMMIT;
postgres=# SELECT * FROM t1;
name
-------
first
A1
B1
A2
A3
B3
(6 rows)
SELECT があっても A,B の両方がコミットに成功してる
ドキュメントの URL
https://www.postgresql.org/docs/13/transaction-iso.html
https://www.postgresql.jp/document/13/html/transaction-iso.html
