https://zenn.dev/mpyw/articles/rdb-advisory-locks

背景

以前， Qiita で以下の記事を投稿した。今回の議題に直接的な関係はないが，関連している部分があるため引用する。

MySQL/Postgres とも，

• MVCC アーキテクチャの恩恵で， SELECT と UPDATE は基本的には競合しない。
• 単一レコードのシンプルな UPDATE でも排他ロックされ，排他ロック中のレコードへの UPDATE での変更操作は トランザクション分離レベルによらず ブロックされる。UPDATE 文に含まれる WHERE 句での検索もブロックされ，これはブロックされない SELECT による検索とは別扱いになる。
• 但し UPDATE 文の WHERE 句上で，更新対象をサブクエリの SELECT から自己参照している場合は例外。トランザクション分離レベルを REPEATABLE READ 以上にして，競合エラーからの復帰処理を書かなければならない。

Postgres に関しては，

• REPEATABLE READ 以上では， MySQL よりも積極的・予防的に競合エラーを起こすようになっている。上記のように WHERE 句に含まれるサブクエリの SELECT から自己参照が発生しない場合， READ COMMITTED にしておくのが最適解。

両データベースとも，書き込み処理競合時， REPEATABLE READ ではデッドロックを含むエラーが発生する前提の設計になっており，リトライすることが求められる。一方でエラーを絶対に回避したい場合は，

分離レベルを下げ、ギャップロックを無効化することでデッドロックを回避できたものの、 SELECT...FOR UPDATE 句の取得結果が NULL であった場合にロックがかけられない（ロックする行がない）

とあるように， 更新時は READ COMMITTED でロッキングリードしておくことで対応できるものの， 新規作成時には（先行者のコミット完了まで）ロックする行が存在しない ことで後続者が素通りしてしまう問題がある。

そこで，新規作成を考慮しなければならない操作対象のリソースの代わりに，存在が保証されている別のリソースをロックするルールにしよう，という戦略を取ることができる。これは アドバイザリーロック（勧告的ロック） と呼ばれている。

アドバイザリーロックの実装手段

引用した記事では， users というユーザ情報を格納する汎用的なテーブルをアドバイザリーロックのために使用していた。ところがコメント欄でも指摘があるように，汎用的なテーブルをアドバイザリーロックに流用すると，アプリケーション実装者の

「とりあえず users テーブルをロックしておこう！」

という愚行により， 「特定ユーザに関連してはいるものの内容的には全く関係のない処理」を無駄に待機させてしまう ことが起こるかもしれない。そのため，以下のような対応を取らなければならない。

• ロック専用テーブルを予め作っておく
• 新規作成してもアドバイザリーロックできる何らかの手段を使う

また，アドバイザリーロックのライフサイクルとして，以下の 2 つの場合があり得る。

• 単一のセッション・トランザクション内で完結する場合
• 複数のセッション・トランザクション上にまたがる場合

これらに着目しながら，各方式を検討していく。