Tag ➽ RDB

こういう感じに親子関係のあるレコードが入ったテーブルがある

id  msg  parent
===============
1   a    NULL
2   b    1
3   あ   NULL
4   A    NULL
5   B    4
6   C    5
7   い   3
8   う   7
9   c    2

または親子関係を別テーブルに分けて

id  msg
=======
1   a
2   b
3   あ
4   A
5   B
6   C
7   い
8   う
9   c

parent  child
=============
1       2
2       9
3       7
4       5
5       6
7       8

これで (1, a), (2, b), (9, c) のように親子関係のあるレコードを一度に取得したい
世代数が固定ならその数だけ JOIN させれなくもないけど 可変だと再帰的に見ていくわけで クエリ 1 つじゃ無理？

テーブル定義を変えて 親子関係をグループとして考えて 親子関係は同じグループ id を持つようにする

id  msg  gid
============
1   a    1
2   b    1
3   あ   2
4   A    3
5   B    3
6   C    3
7   い   2
8   う   2
9   c    1

親がないなら新規にグループ id を作って 親がいるならそのグループ id を引き継ぐ
どっちが子かは id が大きいほど子としてもいいし seq みたいな列を追加でもいい

親子が 1:1 ならこれで大丈夫そうだけど どちらかもしくは両方が複数になると難しそう
RDB 上のレコードの親子関係なら たいていは変更前の値などで 1:1 にしかならない気もするけど

RDB のトランザクション分離レベルをみていたときのこと
一般的には

分離レベル	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

クライアント画面では サーバからデータを受け取ればそれを初期値として 受け取らないなら新規作成として画面を作る
保存ボタン押したときは メソッドやパスは同じところに POST する
サーバ側では SQL の insert を使って on conflict do update にすることですでにある ID なら更新にする

Node.js の knex だとこういう感じで書くだけだからかんたん

knex("table1").insert(user_data).onConflict("id").merge()

ID を自動で連番にしたいなら 列定義で連番にするようにしておいて 新規作成の場合には 「id: undefined」 となるようにすれば DEFAULT 扱いになって連番で insert できる
これで新規作成と更新で処理を分ける必要がなくなってけっこう楽

on conflict do update はレコードがあるのはわかっていて 複数のレコードをまとめて更新したいというときにも使えてかなり便利

RDB をたまにしか使ってないとロックみたいな複雑なところは こういうときにどうなるんだっけ？と思って毎回調べてたりするのでメモ代わりに

UPDATE 時の WHERE で更新日時が等しいことを条件としていれば上書きされることはないはず と思ってたけど トランザクションが同時実行された場合は

1: トランザクション A が書き換えようとする
  条件に一致するので正常に書き換えできる

2: トランザクション B が書き換えようとする
  A がコミットされてないので古いバージョンが見えている
  条件に一致するので正常に書き換えできる

3: トランザクション A をコミットする
4: トランザクション B をコミットする
5: A の書き換えが消える

になるような気がする
これが困るなら同時実行されないように分離レベルとか設定必要なんだっけ？

試したほうが早いと思ってやってみると PostgreSQL のデフォルトだと特に問題なかった


B 側の UPDATE 時にロックされてて UPDATE が処理されず A 側がコミットされてから処理される
その結果 更新日時が違うので B の更新はされてない

余計な心配だったみたい