SELECT FOR UPDATEの話

Slide 1

Slide 1 text

SELECT FOR UPDATEの話 2024/03/13 Keita Kagurazaka

Slide 2

Slide 2 text

2 Context ● 神楽坂といいます ● 8年くらいAndroidアプリ開発 ● ここ1年ちょっとくらいGo/ReactでWebアプリ開発 ● SQL初心者目線でLTします ○ SELECT FOR UPDATEが便利で感動したので ○ 弊社ではDBは全部PostgreSQLなので、内容はそれに準じます ● チームの開発合宿中につきリモートで失礼します

Slide 3

Slide 3 text

3 SELECT FOR UPDATE とは

Slide 4

Slide 4 text

4 SELECTの公式ドキュメントによると https://www.postgresql.jp/document/15/html/sql-select.html

Slide 5

Slide 5 text

5 SELECTの公式ドキュメントによると https://www.postgresql.jp/document/15/html/sql-select.html コレ

Slide 6

Slide 6 text

6 何ができるの？ ● FOR UPDATE付きのSELECT文は、結果行の行ロックを獲得する ○ めっちゃ厳密にいうとRowShareLockのテーブルロックも獲得するが略 ● ロックは掛けたトランザクションが終了するまで継続 ● 他のトランザクションでロックが掛かった行にUPDATE・DELETE・SELECT FOR UPDATEをしようとすると、ロック解除待ちになる ○ = ロックかけたトランザクション終了まで待機 ● ロックを掛けたトランザクションが行を更新していた場合、ロック解除待ちのSQL文は更新後の行を見ることになる

Slide 7

Slide 7 text

7 例: 悲観ロック

Slide 8

Slide 8 text

8 よくあるWebサービスのAPI実装 (PUT系) 1. EntityをRepositoryからFindByIDしてとってくる 2. Entityのメソッドを呼び出し、内部状態を変更する 3. RepositoryのStoreでEntityを永続化する

Slide 9

Slide 9 text

9 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える

Slide 10

Slide 10 text

10 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) APIコールβ

Slide 11

Slide 11 text

11 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) APIコールβ 1. FindByID (= SELECT)

Slide 12

Slide 12 text

12 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT)

Slide 13

Slide 13 text

13 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 行の値 = 初期状態+差分A

Slide 14

Slide 14 text

14 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分B 3. Store (= UPDATE)

Slide 15

Slide 15 text

15 Race Condition ほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT) 2. メモリ上の状態更新→差分B 3. Store (= UPDATE) 行の値 = 初期状態+差分B

Slide 16

Slide 16 text

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が消失！

Slide 17

Slide 17 text

17 行ロックを使ってデータ不整合を回避するほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα APIコールβ

Slide 18

Slide 18 text

18 行ロックを使ってデータ不整合を回避するほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) APIコールβ

Slide 19

Slide 19 text

19 行ロックを使ってデータ不整合を回避するほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. ロック解除待ち

Slide 20

Slide 20 text

20 行ロックを使ってデータ不整合を回避するほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. ロック解除待ち

Slide 21

Slide 21 text

21 行ロックを使ってデータ不整合を回避するほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. ロックが解除！行の値 = 初期状態+差分A

Slide 22

Slide 22 text

22 行ロックを使ってデータ不整合を回避するほぼ同時に同じEntityを更新するAPIを呼び出した場合を考える APIコールα 1. FindByID (= SELECT FOR UPDATE) 2. メモリ上の状態更新→差分A 3. Store (= UPDATE) APIコールβ 1. FindByID (= SELECT FOR UPDATE) a. 初期状態+差分Aが返る

Slide 23

Slide 23 text

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)

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

25 ポイント ● 行ロックなので、対象の行が被らない限りロック解除待ちなどは発生しない ● ロックはトランザクション終了までかかりっぱなしな点には注意 ○ トランザクション内でめっちゃ時間かかる処理しちゃうとかは問題になるかも

Slide 26

Slide 26 text

26 SELECT FOR UPDATE SKIP LOCKEDとは

Slide 27

Slide 27 text

27 公式ドキュメントによると https://www.postgresql.jp/document/15/html/sql-select.html コレ

Slide 28

Slide 28 text

28 何ができるの？ ● FOR UPDATEと同じく行ロックを獲得する ● FOR UPDATEと異なり、選択行のロックが獲得できない場合、ロック解除待ちをする代わりにその行を選択からスキップする ○ 対象行すべてに行ロックがかかっている場合、0行の結果になる

Slide 29

Slide 29 text

29 例2: キュー

Slide 30

Slide 30 text

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);

Slide 31

Slide 31 text

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;

Slide 32

Slide 32 text

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; 未完了なジョブのうち

Slide 33

Slide 33 text

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件だけ取得

Slide 34

Slide 34 text

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; 取り出したジョブに行ロック

Slide 35

Slide 35 text

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; すでに行ロックが獲得されている行は解除待ちせずに飛ばす

Slide 36

Slide 36 text

36 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α Worker β 未完了jobテーブル ● Job1 ● Job2 ● Job3

Slide 37

Slide 37 text

37 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN Worker β 1. BEGIN 未完了jobテーブル ● Job1 ● Job2 ● Job3

Slide 38

Slide 38 text

38 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 未完了jobテーブル ● Job1 ● Job2 ● Job3

Slide 39

Slide 39 text

39 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT 未完了jobテーブル ● Job1 ● Job2 ● Job3

Slide 40

Slide 40 text

40 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT 未完了jobテーブル ● Job1 ● Job2 ● Job3 既にロックが獲得されてるな

Slide 41

Slide 41 text

41 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT 未完了jobテーブル ● Job1 ● Job2 ● Job3 SKIPして次をみよう

Slide 42

Slide 42 text

42 つまりどう動くのか複数のWorkerがジョブキューにたまったジョブを取り出して処理することを考える Worker α 1. BEGIN 2. SELECT a. ロック獲得 Worker β 1. BEGIN 2. SELECT a. ロック獲得未完了jobテーブル ● Job1 ● Job2 ● Job3

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

45 利点 ● ロック解除待ちをさせずに、複数の消費者がキューから値をパラレルにpopする処理を実現できる ● 優先順位をつけることもORDER BY使えば簡単 ○ 今回の例だと作成日時順でしたが、別途priorityカラムを作って第1ソートキーに ● 他のユースケースは思いつかないので、知見のある方教えてください！ ○ 公式ドキュメントにも「行のロックをスキップすると、一貫性のないデータが見えることになるので、一般的な目的の作業のためには適しませんが、複数の消費者がキューのようなテーブルにアクセスするときのロック競合の回避などに利用できます。」と記載

Slide 46

Slide 46 text

46 私のチームで使ったところ ● Cloud Pub/Sub利用時のOutboxパターン ○ イベント発生時にメッセージ送信予約を同じトランザクションでDBに書く ○ ポーリングでそのテーブルを見て、予約があったらそのメッセージを送信する ■ この予約テーブルがジョブキューに該当 ● 我々のユースケースだとメッセージの送信順は発生順と揃ってなくて良かったので、メッセージ送信は並列処理させている ○ これがロック待ちなしでできるのが嬉しさ

Slide 47

Slide 47 text

47 まとめ ● データの競合を防ぐにはちゃんとロックしよう ○ SELECT FOR UPDATEの行ロックは便利だよ ● キューを作りたくなったらSKIP LOCKEDを思い出そう

Slide 48

Slide 48 text

No content