#138 SQLの更新処理比較〜グループごとの最新レコードを安全に更新する考え方

やりたいこと

DBデータの概要

以下のようなstoryテーブルがあるとします。
テーブルの制約、および運用ルールは下記の通りです。

• story_idとversionでユニーク
• 最もversionの値が大きいレコードが、そのstory_idレコードの最新版となる
• 同じstory_idであれば、category_idの値が途中で変わることはない
• released_flgは、story_idごとの最大version以外は必ずtrueになる

No.	story_id	story_name	version	released_flg	available_flg	category_id
1	21	灰かぶり姫	1	true	false	11
2	21	シンデレラの奇縁	2	true	false	11
3	21	シンデレラ	3	false	false	11
4	50	茨姫	1	true	false	11
5	50	眠れる森の美女	2	true	false	11
6	53	雪姫物語	1	true	false	11
7	53	白雪姫	2	false	false	11
8	17	千夜一夜物語	1	true	true	22
9	70	竹取物語	1	true	true	33
10	70	かぐや姫	2	true	false	33
11	79	夕暮れ時	1	true	true	44
12	79	誰そ彼	2	false	false	44
13	35	親指姫	1	false	false	99
14	37	深海の姫と陸の王子様	1	true	false	99
15	37	人魚姫	2	false	false	99
16	45	マッチ売りの少女	1	true	true	99
17	66	悪魔と鏡の欠片	1	true	false	99
18	66	雪の女王	2	true	false	99

SQLでやりたいこと

SQLで実行したい操作は以下の通りです。

• story_idごとの最新版のレコードの内、released_flgがfalseの場合、そのレコードのavailable_flgをtrueにして更新する
• ただし、category_idが99の場合は更新対象外

なお、本記事では要件として、更新するレコードが「 story_id ごとの 最大version であること」を最も重視するとします。
そのため、それ以外の version が released_flg = false だとしても、そのレコードは更新対象外となります。

全体イメージ

ざっくりした処理イメージとしては、

1. トランザクション開始
2. 更新前の対象レコード取得（確認用）
3. 更新処理
4. 更新後の対象レコード取得（確認用）
5. トランザクション終了

のようなものを想定しているとしましょう。
SQLにすると以下のようなイメージです。
※本記事では③更新処理について後ほど比較などしていきたいので、一旦ここではコメントだけに留めています

-- トランザクション開始
BEGIN;

-- 変更前の対象レコードを取得
WITH before_update AS (
  SELECT *
  FROM story
  WHERE (story_id, version) IN (
    SELECT story_id, MAX(version)
    FROM story
    GROUP BY story_id
  )
)
SELECT 'BEFORE' AS state, *
FROM before_update
WHERE released_flg = false AND category_id != 99;

-- UPDATE処理

-- 変更後の対象レコードを取得
WITH after_update AS (
  SELECT *
  FROM story
  WHERE (story_id, version) IN (
    SELECT story_id, MAX(version)
    FROM story
    GROUP BY story_id
  )
)
SELECT 'AFTER' AS state, *
FROM after_update
WHERE released_flg = false AND category_id != 99;

-- トランザクション終了
COMMIT;

上記の内容は共通箇所となるため、以降の記載は省略させていただきます。

更新処理を比較

結論を先に言うと、パターン①、②はどちらも期待通りに更新処理を行うことができますが、
DBの運用ルールに依存せず、安全に更新処理を行えるのはパターン②の方です。
こちらを踏まえて、実際に処理を比較してみましょう。
ポイントは「released_flgとcategory_idによる絞り込みをどこで行うか」です。

パターン①：released_flg = false の中で MAX(version) を取得する

UPDATE story
SET
  available_flg = true
WHERE (story_id, version) IN (
  SELECT story_id, MAX(version)
  FROM story
  WHERE released_flg = false AND category_id != 99
  GROUP BY story_id
);

こちらのSQLでは、以下のような順番で処理が行われます。

1. story_idごとにグループ化する
2. グループごとに以下の条件を適用
   • released_flg = false
   • category_id != 99
3. ②の条件を満たしたstory_idごとのレコードの中で、最もversionの値が大きいのものを取得
4. ③のstory_idとversionの組み合わせを持つレコードに対して、available_flg：trueで更新

保証するのは「released_flg = false かつ category_id != 99であること」です。

更新されるデータ

No.	story_id	story_name	version	released_flg	available_flg	category_id
3	21	シンデレラ	3	false	false -> true	11
7	53	白雪姫	2	false	false -> true	11
12	79	誰そ彼	2	false	false -> true	44

パターン②：story_idごとの MAX(version) であることを優先する

UPDATE story
SET
  available_flg = true
WHERE (story_id, version) IN (
  SELECT story_id, MAX(version)
  FROM story
  GROUP BY story_id
)
AND released_flg = false
AND category_id != 99;

こちらのSQLでは、以下のような処理が行われます。

1. story_idごとにグループ化する
2. グループごとに最もversionの値が大きいのものを取得
3. ②のレコードに以下の条件を適用して絞り込む
   • released_flg = false
   • category_id != 99
4. available_flgをtrueにして更新する

保証するのは「story_idごとの MAX(version) であること」です。

②で取得されるデータと更新有無

No.	story_id	story_name	version	released_flg	available_flg	category_id	更新有無
3	21	シンデレラ	3	false	false -> true	11	o
7	53	白雪姫	2	false	false -> true	11	o
12	79	誰そ彼	2	false	false -> true	44	o
4	50	茨姫	1	true	false	11	x
10	70	かぐや姫	2	true	false	33	x

※条件category_id != 99については絞り込み済みと仮定

結果的に更新されるレコードが一致する理由

パターン①と②では、保証される動作と処理順序が異なるにも関わらず、結果だけを見ると更新されるレコードが一致していました。
その理由は、運用ルールに「released_flgは、story_idごとの最大version以外は必ずtrueになる」があるためです。
released_flg = falseを条件としてstory_idごとのレコードを絞り込むと、対象レコードは自然とstory_idごとの最大versionとなります。
そのため、パターン①であっても「story_idごとの最大versionであること」が保証されているような挙動になっていたんですね。

もちろん、あくまでも”たまたまどちらも期待通りにできただけ”なので、運用ルールを無視したレコードなどがあると、更新対象となるレコードがパターン①、②で変わってきます。
運用ルールを無視したNo.19のようなレコードが追加されたとして、それぞれの動作を確認してみましょう。

No.	story_id	story_name	version	released_flg	available_flg	category_id	備考
11	79	夕暮れ時	1	true	true	44	-
12	79	誰そ彼	2	false	false	44	-
19	79	宵の月	3	true	false	44	NEW!

パターン①：
released_flg = false かつ category_id != 99であるレコードを取得

No.	story_id	story_name	version	released_flg	available_flg	category_id
3	21	シンデレラ	3	false	false -> true	11
7	53	白雪姫	2	false	false -> true	11
12	79	誰そ彼	2	false	false -> true	44

→❌No.12が更新対象になったまま

パターン②：
story_idごとの MAX(version) を取得してから絞り込む

No.	story_id	story_name	version	released_flg	available_flg	category_id	更新有無
3	21	シンデレラ	3	false	false -> true	11	する
7	53	白雪姫	2	false	false -> true	11	する
19	79	宵の月	3	true	false	44	しない
4	50	茨姫	1	true	false	11	しない
10	70	かぐや姫	2	true	false	33	しない

→✅No.19が更新対象外となる
※条件category_id != 99については絞り込み済みと仮定

このようなレコードがあると、意図しないものまで更新されてしまう恐れがあることが確認できました。
以上のことから、本記事では要件を保証できるパターン②を使用することがベストでしょう。
本記事の要件に沿ったSQLであるかどうかについて、
パターン①：DBデータのルールに依存しているため、意図しない更新が行われる可能性がある
パターン②：ロジックとして明示していて安全である
と言えます。

おまけ

グループごとの最新レコードを取得するSQLとしては、分析関数を用いる方法もあるそうです。

例えば、以下はパターン①とほぼ同じ挙動をするSQLです。
→分析関数RANK()を使用して、released_flg = false かつ category_id != 99 を条件として絞り込み、降順に並べたversionの値でランク計算を行い、その最大値を取るレコードを返す

そのため、保証するのは「released_flg = false かつ category_id != 99であること」です。
こちらも用途に応じて使い分けていけると良いですね。
複雑なSQLになったとしても、どのタイミングで絞り込みしているかがポイントになっているのがわかるかと思います。

UPDATE story
SET
  available_flg = true
WHERE (story_id, version) IN (
  SELECT story_id, version
  FROM (
    SELECT story_id, version,
      RANK() OVER (PARTITION BY story_id ORDER BY version DESC) AS rnk
    FROM story
    WHERE released_flg = false AND category_id != 99
  ) subquery
  WHERE rnk = 1
);

おわりに

いかがだったでしょうか。
今回は、実施したいSQLの要件によって正となるパターンが異なることを、実際のSQLの動作を比較しながら確認しました。
本記事で設定した要件「story_id ごとの 最大version であることを重視する」場合は、ロジックとして明示されていて安全なパターン②を使用できると良いですね。
また、「たまたま期待通りに動作しただけ」という可能性も考慮しながら、動作確認などをしていく必要があるなと改めて実感できました。

本記事の内容から脱線してしまうかと思い、おまけの分析関数については実際のSQLによる紹介のみといたしました。
こちらについても、機会があれば取り上げてみたいと思います。

以上です。最後まで閲覧いただきありがとうございます。

参考

• https://tokki358.hatenablog.com/entry/2019/01/11/200349