SELECT FOR UPDATEの話

2024/03/13に開催された「リンケージ×enechain toBシステム開発勉強会 ~ PostgreSQLからReactまで」の発表スライドです。

Transcript

1. SELECT FOR UPDATEの話 2024/03/13 Keita Kagurazaka
2. 2 Context • 神楽坂といいます • 8年くらいAndroidアプリ開発 • ここ1年ちょっとくらいGo/ReactでWebアプリ開発 • SQL初心者目線でLTします ◦ SELECT FOR UPDATEが便利で感動したので ◦ 弊社ではDBは全部PostgreSQLなので、内容はそれに準じます • チームの開発合宿中につきリモートで失礼します
3. 3 SELECT FOR UPDATE とは
4. 4 SELECTの公式ドキュメントによると https://www.postgresql.jp/document/15/html/sql-select.html
5. 5 SELECTの公式ドキュメントによると https://www.postgresql.jp/document/15/html/sql-select.html コレ
6. 6 何ができるの？ • FOR UPDATE付きのSELECT文は、結果行の行ロックを獲得する ◦ めっちゃ厳密にいうとRowShareLockのテーブルロックも獲得するが略 • ロックは掛けたトランザクションが終了するまで継続 • 他のトランザクションでロックが掛かった行にUPDATE・DELETE・SELECT FOR UPDATEを しようとすると、ロック解除待ちになる ◦ = ロックかけたトランザクション終了まで待機 • ロックを掛けたトランザクションが行を更新していた場合、ロック解除待ちのSQL文は更新後の 行を見ることになる
7. 7 例: 悲観ロック
8. 8 よくあるWebサービスのAPI実装 (PUT系) 1. EntityをRepositoryからFindByIDしてとってくる 2. Entityのメソッドを呼び出し、内部状態を変更する 3. RepositoryのStoreでEntityを永続化する
9. 9 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える
10. 10 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) APIコールβ
11. 11 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) APIコールβ 1. FindByID (= SELECT)
12. 12 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT)
13. 13 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 行の値 = 初期状態+差分A
14. 14 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分B 3. Store (= UPDATE)
15. 15 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分B 3. Store (= UPDATE) 行の値 = 初期状態+差分B
16. 16 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分B 3. Store (= UPDATE) 行の値 = 初期状態+差分B 差分Aが消失！
17. 17 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα APIコールβ
18. 18 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) APIコールβ
19. 19 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. ロック解除待ち
20. 20 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. ロック解除待ち
21. 21 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. ロックが解除！ 行の値 = 初期状態+差分A
22. 22 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. 初期状態+差分Aが返る
23. 23 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. 初期状態+差分Aが返る 2. メモリ上の状態更新→差分B 3. Store (= UPDATE)
24. 24 行ロックを使ってデータ不整合を回避する ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. 初期状態+差分Aが返る 2. メモリ上の状態更新→差分B 3. Store (= UPDATE) 行の値 = 初期状態+差分A+差分B
25. 25 ポイント • 行ロックなので、対象の行が被らない限りロック解除待ちなどは発生しない • ロックはトランザクション終了までかかりっぱなしな点には注意 ◦ トランザクション内でめっちゃ時間かかる処理しちゃうとかは問題になるかも
26. 26 SELECT FOR UPDATE SKIP LOCKEDとは
27. 27 公式ドキュメントによると https://www.postgresql.jp/document/15/html/sql-select.html コレ
28. 28 何ができるの？ • FOR UPDATEと同じく行ロックを獲得する • FOR UPDATEと異なり、選択行のロックが獲得できない場合、ロック解除待ちをする代わりに その行を選択からスキップする ◦ 対象行すべてに行ロックがかかっている場合、0行の結果になる
29. 29 例2: キュー
30. 30 ジョブのテーブルを考える CREATE TABLE job ( id BIGSERIAL PRIMARY KEY, message TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMPTZ NULL ); CREATE INDEX idx_job_created_at ON job (created_at); CREATE INDEX idx_job_completed_at ON job (completed_at);
31. 31 ジョブの取り出し created_at の昇順で取り出していく仕様とする BEGIN; SELECT * FROM job WHERE completed_at is NULL ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED; UPDATE job SET completed_at = $1 WHERE id = $2; COMMIT;
32. 32 ジョブの取り出し created_at の昇順で取り出していく仕様とする BEGIN; SELECT * FROM job WHERE completed_at is NULL ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED; UPDATE job SET completed_at = $1 WHERE id = $2; COMMIT; 未完了なジョブのうち
33. 33 ジョブの取り出し created_at の昇順で取り出していく仕様とする BEGIN; SELECT * FROM job WHERE completed_at is NULL ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED; UPDATE job SET completed_at = $1 WHERE id = $2; COMMIT; 作成時昇順で1件だけ取得
34. 34 ジョブの取り出し created_at の昇順で取り出していく仕様とする BEGIN; SELECT * FROM job WHERE completed_at is NULL ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED; UPDATE job SET completed_at = $1 WHERE id = $2; COMMIT; 取り出したジョブに行ロック
35. 35 ジョブの取り出し created_at の昇順で取り出していく仕様とする BEGIN; SELECT * FROM job WHERE completed_at is NULL ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED; UPDATE job SET completed_at = $1 WHERE id = $2; COMMIT; すでに行ロックが獲得されている行は 解除待ちせずに飛ばす
36. 36 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α Worker β 未完了jobテーブル • Job1 • Job2 • Job3
37. 37 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN Worker β 1. BEGIN 未完了jobテーブル • Job1 • Job2 • Job3
38. 38 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 未完了jobテーブル • Job1 • Job2 • Job3
39. 39 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT 未完了jobテーブル • Job1 • Job2 • Job3
40. 40 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT 未完了jobテーブル • Job1 • Job2 • Job3 既にロックが 獲得されてるな
41. 41 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT 未完了jobテーブル • Job1 • Job2 • Job3 SKIPして 次をみよう
42. 42 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT a. ロック獲得 未完了jobテーブル • Job1 • Job2 • Job3
43. 43 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 3. 処理 4. UPDATE completed_at Worker β 1. BEGIN 2. SELECT a. ロック獲得 3. 処理 4. UPDATE completed_at 未完了jobテーブル • Job1 • Job2 • Job3
44. 44 つまりどう動くのか 複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 3. 処理 4. UPDATE completed_at 5. COMMIT Worker β 1. BEGIN 2. SELECT a. ロック獲得 3. 処理 4. UPDATE completed_at 5. COMMIT 未完了jobテーブル • Job3
45. 45 利点 • ロック解除待ちをさせずに、複数の消費者がキューから値をパラレルにpopする処理を実現で きる • 優先順位をつけることもORDER BY使えば簡単 ◦ 今回の例だと作成日時順でしたが、別途priorityカラムを作って第1ソートキーに • 他のユースケースは思いつかないので、知見のある方教えてください！ ◦ 公式ドキュメントにも「行のロックをスキップすると、一貫性のないデータが見えることにな るので、一般的な目的の作業のためには適しませんが、複数の消費者がキューのような テーブルにアクセスするときのロック競合の回避などに利用できます。」と記載
46. 46 私のチームで使ったところ • Cloud Pub/Sub利用時のOutboxパターン ◦ イベント発生時にメッセージ送信予約を同じトランザクションでDBに書く ◦ ポーリングでそのテーブルを見て、予約があったらそのメッセージを送信する ▪ この予約テーブルがジョブキューに該当 • 我々のユースケースだとメッセージの送信順は発生順と揃ってなくて良かったので、メッセージ 送信は並列処理させている ◦ これがロック待ちなしでできるのが嬉しさ
47. 47 まとめ • データの競合を防ぐにはちゃんとロックしよう ◦ SELECT FOR UPDATEの行ロックは便利だよ • キューを作りたくなったらSKIP LOCKEDを思い出そう