[PostgreSQL] ユニーク制約が作れなくても DB レベルで重複登録を防ぎたくて検討したこと

はじめに

2024/06/05 追記

こちらの記事の内容をベースにした内容でスライドも公開しています。よろしければご覧ください。

https://speakerdeck.com/kmtym1998/duo-zhong-deng-lu-wofang-yu-surutamenonouhau

少し前にユニーク制約を使用できない状況でデータの重複をどう防ぐかについて悩み、いろいろと検討したことがありました。「重複登録を防ぐ」という観点ではまとまった知見があまり見つからなかったので記事を書くことにしました。同じような境遇の方の参考になれば幸いです。

Web アプリケーションにおいて、データの整合性を担保することは重要です。一般的にデータの一意性を保証するのに RDB のユニーク制約がよく使われています。ですが、ユニーク制約の作成が難しい場合があります。設計上どうしても難しかったり、機能のリリース後に一意性を保証する必要が生じたりなど、それなりの理由があると思います。データをモデリングし直したり、データクレンジングを行ったりして、ユニーク制約が設定できるのであれば良いですが、そんなにサクッと済まないケースも多いでしょう。

この記事ではユニーク制約を使わずにデータの一意性を保証する方法について紹介します。コードは SQL しか出てこないので、特定の言語に依存した実装パターンみたいなものはないと思っています。また、ここでは PostgreSQL の使用を前提に説明しています。

説明のための例

まずは、以下のような簡単なタスクテーブルを例にします。

CREATE TABLE public.tasks (
  id serial NOT NULL,
  title varchar(255) NOT NULL,
  detail text,
  progress_status varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

このタスクのデータには以下のような制約があります。

• タスクは進捗ステータスを持ち、取り得る値は TODO, DOING, DONE の 3 値のみ
• タスクの新規作成時には、その進捗ステータスは必ず TODO になる
• TODO なタスクは同時に 1 件しか存在してはいけない

問題となったこと

ある日、ダブルサブミットによって TODO なタスクを多重登録できる状態であったことが問題になりました。もともとフロントエンドでダブルサブミット対策を施していましたが、端末によってうまく動作しないケースがあるなど、挙動が不安定でした。フロントエンドでさらに対策を講じたとしても、それはあくまで保険的なものです。悪意のあるユーザが直接 API を叩くこと、同一ユーザによる別のタブからの操作、複数のユーザによる同時サブミットするなど、すり抜ける方法はいくつか考えられます。サーバ側でこのような重複を防ぐためのバリデーションが行われていることが理想的です。

この問題はユニーク制約を設けることでは解決できない問題であったため、バックエンドの実装に以下のようなロジックを追加しました。

-- ① 重複したタスクがないかどうかチェック
SELECT id FROM tasks WHERE progress_status = 'TODO' LIMIT 1;

-- ② 重複がなければ新規タスク作成
INSERT INTO tasks (title, progress_status) VALUES ('タスクのタイトル', 'TODO');

しかしこれでは重複を完全に防げません。① の SELECT 文を叩いてから ② の INSERT 文の処理が完了するまでに若干のインターバルが存在するためです。このインターバルの間に別のリクエストの処理が並列していると、重複チェックのロジックをすり抜けてしまいます。

今回のようなシンプルな例の場合、このインターバルは小さいので重複が発生する可能性は低いかもしれません。ですが、業務で扱うような複雑なアプリケーションでは重複チェックのために複雑なクエリを実行する必要があったり、① と ② の間に重厚なロジックや外部通信が挟まったりします。そうなるとインターバルは長くなり、サブミットのボタン連打などによって意図しない多重登録が発生する可能性は高くなります。

問題となっているのは並列したリクエストがの処理の中の DB へのアクセス部分が直列になっていないことです。タスク追加前の重複チェックは、直前のタスク追加よりも必ず後にならなければなりません (上図の例だと、重複チェックA → 新規作成A → 重複チェックB → 新規作成B となってほしい)。

検討した解決方法

トランザクション分離レベル シリアライザブルを使う

このような問題の対策として、直列に処理されることが担保されたトランザクションを使うことが有効です。PostgreSQL (および 標準 SQL) ではトランザクション分離レベルは「リードアンコミッティド」「リードコミッティド」「リピータブルリード」「シリアライザブル」の 4 つが実装されています。また、PostgreSQL 公式ドキュメントには 2 つ以上のセッションでデータ操作が競合するときに発生しうる問題と、トランザクション分離レベルの関係性が記述されています。

https://www.postgresql.jp/docs/15/transaction-iso.html

分離レベル	ダーティリード	反復不能読み取り	ファントムリード	直列化異常
リードアンコミッティド	許容されるが、PostgreSQL では発生しない	可能性あり	可能性あり	可能性あり
リードコミッティド	安全	可能性あり	可能性あり	可能性あり
リピータブルリード	安全	安全	許容されるが、PostgreSQL では発生しない	可能性あり
シリアライザブル	安全	安全	安全	安全

それぞれのトランザクション分離レベルで具体的にどういう挙動をするのかについては、こちらの記事でわかりやすく解説されています。

https://zenn.dev/bellwood4486/articles/postgresql-trans-iso-level

上記の表の通り、直列性を担保するにはトランザクション分離レベルにシリアライザブルを使います。先程の重複チェックのための SELECT と INSERT をシリアライザブルのトランザクションで括ることで、複数のトランザクションがあっても直列処理されることが担保されます。

-- ① シリアライザブルでトランザクション開始
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- ② 重複したタスクがないかどうかチェック
SELECT id FROM tasks WHERE progress_status = 'TODO' LIMIT 1;

-- ③ 重複がなければ新規タスク作成
INSERT INTO tasks (title, progress_status) VALUES ('タスクのタイトル', 'TODO');

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

並列に (直列でない状態で) トランザクションが走った場合は ③ または ④ で直列化失敗のエラー (could not serialize access...) が発生します。一方のトランザクションがコミットされた時点で、他方のトランザクション内で INSERT が完了していればその次の ④ の COMMIT でエラーが発生します。他方のトランザクション内で INSERT が完了していなければ ③ の INSERT でエラーが返ります。下図の処理フローの場合は前者にあたるので COMMIT でエラーが返ってくれるので、多重登録は発生しません。

このパターンで実装した場合、直列化失敗で発生したエラーのハンドリングに気を配っておく必要があります。サーバエラーとするか、クライアントエラーとするか、正常レスポンスとするか、自動リトライするか、ユーザにリトライさせるかなど、いろいろと検討の余地があります。同時に書き込みされる頻度が高いと、直列化失敗エラーも頻発してしまいます。安全性が高いからといってなんでもシリアライザブルで実装すると、不要な直列化失敗エラーを引き起こして UX を下げてしまうので注意が必要です。また、シリアライザブルは安全性が高いぶん他の分離レベルと比べて相対的に性能は低いとされています。

排他ロックを使う

直列性を担保したトランザクションは排他ロックを使うことによっても実現可能です。

今回やりたいのはレコードの多重に作成されることを防ぐことですが、PostgreSQL では (作成予定の) 存在しない行に対しての行ロックはできないようです。特定の行に対してロックを行えない場合は、その範囲を広げると有効な場合があります。テーブルごとロックしてしまえば、SELECT した結果をもとに INSERT するような今回のケースでも、直列性を担保できるようになります。

-- ① READ COMMITTED または READ UNCOMMITTED でトランザクション開始
 BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- ② tasks テーブルをまるごとロック
LOCK TABLE tasks;

-- ③ 重複したタスクがないかどうかチェック
SELECT id FROM tasks WHERE progress_status = 'TODO' LIMIT 1;

-- ④ 重複がなければ新規タスク作成
INSERT INTO tasks (title, progress_status) VALUES ('タスクのタイトル', 'TODO');

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

上記のように実装すると テーブルにロックをかけるときに他のロックがかかっていた場合、それが解放されるのを待ってくれます。トランザクションが終了するとそのロックが解放されるため、重複チェックのための SELECT の実行が直前の tasks への INSERT より後になることが保証されます。ロックをかけるトランザクションの分離レベルは「リードアンコミッティド」または「リードコミッティド」を選択する必要があります。これはトランザクション内から、並列して発生した他のトランザクションで作成されたレコードを読み取る必要があるためです。「リピータブルリード」や「シリアライザブル」だと SELECT 時そのトランザクションを開始した時点のレコードしか読み取れないので、③ の重複チェックが機能しなくなってしまいます。

また、テーブルロックにはロックモードというオプションが存在します。この種類が多く記事では紹介しきれないので、詳しくは公式ドキュメントや他の記事をご覧ください。

https://www.postgresql.jp/docs/15/sql-lock.html

https://www.postgresql.jp/document/15/html/explicit-locking.html

明示的にロックをかける際は、デッドロックにも気を配る必要があります。デッドロックとは、2 つ以上のトランザクションが互いのロック解放を待つ状態となってしまい、いずれの処理も止まってしまう状態のことをいいます。lock_timeout を設定してロック解放の待ち時間に上限を設けるなどしておくと安心です。

https://qiita.com/mkyz08/items/9321fb8662c7ee09e14a

まとめ

DB でユニーク制約を設定できない場合にデータの重複を防ぐために検討した実装パターンを紹介しました。銀の弾丸的な方法はなく、どのパターンでも注意すべきポイントが生じることに注意が必要です。なかなか考慮が漏れやすい部分かもしれませんが、プロダクトが扱うデータの特性によってはシビアに考えないといけないケースがあるかと思います。

今回はバックエンドでの多重登録防止にフォーカスを当てましたが、フロントエンドでの対応も含めたダブルサブミット対策もやっておくと望ましいと思います。ダブルサブミット対策についてはいくつか参考になる記事があるので合わせてご覧ください。

https://qiita.com/syobochim/items/120109315f671918f28d

https://qiita.com/unmelt/items/fb1eff5a2d9bc4da1f7e

参考資料

https://speakerdeck.com/saiya_moebius/rdbms-in-action?slide=9

https://speakerdeck.com/mpyw/postgres-niokerutoranzakusiyonfen-li-reberu

https://qiita.com/behiron/items/571562ea33b8212a4c32