トランザクション分離レベルについての教養があったほうがこの記事の内容を理解しやすいため，必要に応じてまず以下を参照されたい。

https://zenn.dev/mpyw/articles/rdb-transaction-isolations

背景

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

https://qiita.com/mpyw/items/14925c499b689a0cbc59

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 ではデッドロックを含むエラーが発生する前提の設計になっており，リトライすることが求められる。一方でエラーを絶対に回避したい場合は，

https://zenn.dev/shuntagami/articles/ea44a20911b817

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

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

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

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

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

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

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

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

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

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

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

データベース組み込みのアドバイザリーロック関数

これが今回最もおすすめしたい方法。

Postgres の場合

https://www.postgresql.jp/document/13/html/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

pg_try_advisory_xact_lock は，トランザクション開始直後に使用し，解放はトランザクションの終了に任せるという使い方をする。

BEGIN;-- ロックの可否を取得SELECT pg_try_advisory_xact_lock(...);-- true で成功したときだけ処理を続行-- ...-- ...COMMIT;

以下の理由から，この中ではpg_try_advisory_xact_lock が最も使い勝手がよいと考えられる。

• トランザクションがコミットまたはロールバックされて消滅したとき，自動的にロックが解放されるのはありがたい。
• ロックを待機し続けるよりも，ロックに失敗したときに潔く諦めてエラーを返すほうが負荷がかかりにくい。

pg_try_advisory_xact_lock(key bigint):booleanpg_try_advisory_xact_lock(key1 integer, key2 integer):boolean

https://stackoverflow.com/questions/29353845/how-do-i-use-string-as-a-key-to-postgresql-advisory-lock

こちらの回答に従って， 2 つの使い方を示す。

SELECT pg_try_advisory_xact_lock(hashtext('任意の文字列'));SELECT pg_try_advisory_xact_lock('テーブル名'::regclass::integer, 主キーの整数);

• hashtext() で任意文字列に対応する符号付き 64 ビット整数 が取得できるため，必要な情報を全て文字列内に含ませてからハッシュ化した結果を引数として取ることができる。
  • 汎用性が高いが，衝突が発生する可能性がある。しかし，衝突が発生してもロック取得に失敗する程度であるため，エラー対応処理が書かれていればさほど問題はない。
• ::regclass::integer でテーブルに対応する一意な符号なし 32 ビット整数 が取得できるため，これをそのまま第 1 引数に取ることができる。

総評: 唯一，セッション・トランザクション内でしかロックを維持できないという欠点を持つが，その範囲の実装でよければ最も優れた選択肢となる。

MySQL の場合

https://dev.mysql.com/doc/refman/8.0/ja/locking-functions.html#function_get-lock

Postgres の特権だと思われたが，なんと MySQL にも存在していた。バージョン 5.7 から使えるようだ。

// 成功時に 1, タイムアウトで 0, エラーで NULLGET_LOCK(str text, timeout integer):?integer

最初から文字列を受け取れるぶん Postgres より利便性は高い。しかし，トランザクション終了時に自動解放する手段が無い ため，RELEASE_LOCK() による明示的なロック開放処理を忘れてはならない。また文字列長が64 文字 に制限されていることに注意。

Redis

排他制御といえば Redis！

…筆者もそう思っていたが，ここにきて認識が揺らいでいる。今まで Redis が落ちない前提でSET を使った処理を書いていたが，どうやらそこまで安全とは言えないようだ。

まず最もオーソドックスな，シンプルにSET 命令を使う方法を解説する。既に紹介したデータベースのアドバイザリーロック関数を使う方法と異なり，有効期限を設定してセッションを超えた継続 が可能になっている点がポイント。

https://redis.io/commands/set/

// 新規ロック取得時SET キー"所有者"NXEX タイムアウト秒数// 復帰時 （取得後， 所有者が自分かどうか確認する）GET キー// 終了時DEL キー

https://christina04.hatenablog.com/entry/redis-distributed-locking

対処法としてRedLock という，複数ノードを用いた分散ロックアルゴリズムが紹介されている。自前で書くのは骨が折れるため，ライブラリを使いたいところ。

総評: 汎用性が高いが，厳密に安全性を考えると RedLock などのアルゴリズムに基づいた冗長化が必要になり，ややコストが高い。但し，AWS の場合は ElastiCache の代わりに MemoryDB を使えば解決する。また，将来的に RedisRaft が正式導入されればオープンソースな Redis でも解決できるようになる可能性がある。

OS のファイルロック

古くからよく使われていた手法であるが，ファイルを 1 箇所で管理する都合上，クライアントを分散できないため，あまり商業案件での実用性は無い。個人の VPS で分散させずに小規模に運用する場合には依然として使える場合もある。

• Man page of FLOCK
• PHP: flock - Manual

ファイルのロックに使うflock 関数はまさにアドバイザリーロックを提供するものであり，ファイルの編集以外にも流用することができる。更に，ファイルの更新日時やファイルの中身を追加の情報ソースとして利用でき，有効期限等も設定することができるため，自由度は高い部分もある。但し，ダウンタイム無しのファイルのクリーンアップ処理が難しい という大きな欠点を持つ。そのため，クリーンアップ処理が不要な，特定の ID に依存しない排他制御にしか適用が困難である。

総評: 過去の遺物

ロック専用テーブル

CREATETABLE mutex(keyvarchar(64)PRIMARYKEY);

-- ユーザ 1 人ごとに， 注文操作は重複してできないようにするINSERTINTO users(id, name)VALUES('U1','Bob'),('U2','Alice');INSERTINTO mutex(key)VALUES('user:U1:order'),('user:U2:order');

このように準備しておいた上で，以下のようにSELECT ... FOR UPDATE を利用する。この際ロックタイムアウトを適切に設定するか，あるいは即時失敗で十分な場合は以下のようにNOWAIT を設定するとよい。

BEGIN;-- ロックの可否を取得SELECT*FROM mutexWHEREkey='user:U1:order'FORUPDATE NOWAIT;-- ロックレコードを取得成功したときだけ処理を続行-- ...-- ...COMMIT;

-- PostgresCREATETABLE mutex(keyvarchar(64)PRIMARYKEY,    ownervarchar(64)NOTNULL,-- 所有者    expires_at TIMESTAMPTZNOTNULLDEFAULT'1970-01-01 00:00:00'-- ロックの有効期限);-- MySQLCREATETABLE mutex(`key`varchar(64)PRIMARYKEY,    ownervarchar(64)NOTNULL,-- 所有者    expires_atdatetimeNOTNULLDEFAULT'1970-01-01 00:00:00'-- ロックの有効期限);

BEGIN;-- ロックの可否を取得-- 取得できた場合， expires_at を更新WITH mAS(SELECTkeyFROM mutexWHEREkey='user:U1:order'AND(        owner='所有者'-- 所有者自身による引き継ぎOR expires_at<NOW()-- ロックの有効期限が切れた)FORUPDATE NOWAIT)UPDATE mutexSET owner='所有者', expires_at=...WHEREkey=(SELECTkeyFROM m);-- UPDATE が作用したときだけ処理を続行-- ...-- ...COMMIT;

応用集

Postgres でロックタイムアウトを利用する

Postgres は MySQL のようにアドバイザリーロック関数上で直接ロックタイムアウトを設定する手段を提供していないが，以下のようなラッパー関数を作ることで対応可能となる。lock_timeout という汎用的な設定値があるが，これはアドバイザリーロックのタイムアウト制御にも作用する。

CREATEORREPLACEFUNCTION pg_try_advisory_lock_with_timeout(keybigint, lock_timeout_valuetext)RETURNSbooleanSET lock_timeoutFROMCURRENTAS $$BEGINEXECUTEformat('SET lock_timeout TO %L;', lock_timeout_value);  PERFORM pg_advisory_lock(key);RETURNtrue;EXCEPTIONWHEN lock_not_availableOR deadlock_detectedTHENRETURNfalse;END;$$LANGUAGE plpgsql;

CREATEORREPLACEFUNCTION pg_try_advisory_xact_lock_with_timeout(keybigint, lock_timeout_valuetext)RETURNSbooleanSET lock_timeoutFROMCURRENTAS $$BEGINEXECUTEformat('SET LOCAL lock_timeout TO %L;', lock_timeout_value);  PERFORM pg_advisory_xact_lock(key);RETURNtrue;EXCEPTIONWHEN lock_not_availableOR deadlock_detectedTHENRETURNfalse;END;$$LANGUAGE plpgsql;

なおプログラム上から一時的な関数として作成したい場合は，このようにpg_temp スキーマ上に作るとよい。

アドバイザリーロック関数 + テーブルでのロック情報保持

• アドバイザリーロック関数の「セッション越えを出来ない」という弱点
• ロック専用テーブルの「予めレコードを用意しておく必要がある」という弱点

を両方とも克服する手法。 以下に Postgres を想定して記述するが， MySQL でもほぼ同様である。

BEGIN;-- 空振りを防ぐためのアドバイザリーロック-- 成功したときだけ続行SELECT pg_try_advisory_xact_lock('user:U1:order');WITHm_allAS(SELECT*FROM mutexWHEREkey='user:U1:order'FORUPDATE NOWAIT-- アドバイザリーロックをしているので必須ではないが，一応付けておく),-- 「古いロック」または「引き継ぎ可能なロック」に該当するものm_staleAS(SELECT*FROM m_allWHERE(        owner='所有者'-- 所有者自身による引き継ぎOR expires_at<NOW()-- ロックの有効期限が切れた))INSERT mutex(key, owner, expires_at)SELECT'user:U1:order','所有者',...WHERENOTEXISTS(SELECT*FROM m_all)-- ロックが存在せず新規作成される場合OREXISTS(SELECT*FROM m_stale)-- 古いロックまたは引き継ぎ可能なロックが残っている場合ON CONFLICT(key)DOUPDATESET owner= EXCLUDED.owner, expires_at= EXCLUDED.expires_atRETURNING*;-- INSERT または UPDATE が作用したときだけ処理を続行-- ...-- ...COMMIT;

空打ちでエラー回避できるINSERT + テーブルでのロック情報保持

-- PostgresINSERTINTO mutex(`key`, owner, expires_at)VALUES('user:U1:order','所有者',...)ON CONFLICT(key)DO NOTHING;-- MySQLINSERTINTO mutex(`key`, owner, expires_at)VALUES('user:U1:order','所有者',...)ONDUPLICATEKEYUPDATE`key`=VALUES(`key`);

https://www.slideshare.net/ichirin2501/insert-51938787

こちらで紹介されているテクニック。一見 MySQL 専用かと思いきや， Postgres はINSERT IGNORE よりもお行儀のいいON CONFLICT(key) DO NOTHING を備えているため，バッドノウハウっぽい書き方をしなくても達成できる。

BEGIN;-- ON DUPLICATE KEY UPDATE で意味のない更新を書くバッドノウハウINSERTINTO mutex(`key`, owner, expires_at)VALUES('user:U1:order','所有者',...)ONDUPLICATEKEYUPDATE`key`=VALUES(`key`);-- ロックの可否を取得-- 取得できた場合， expires_at を更新WITH mAS(SELECT`key`FROM mutexWHERE`key`='user:U1:order'AND(        owner='所有者'-- 所有者自身による引き継ぎOR expires_at<NOW()-- ロックの有効期限が切れた)FORUPDATE)UPDATE mutexSET owner='所有者', expires_at=...WHERE`key`=(SELECT`key`FROM m);-- UPDATE が作用したときだけ処理を続行-- ...-- ...COMMIT;

まとめ

基本はアドバイザリーロック関数で済ませるとよい

それぞれの関数には癖があるので，特性を再確認しておく。

-- PostgresSELECT pg_try_advisory_xact_lock(hashtext('任意の文字列'));-- MySQLSELECT GET_LOCK('任意の文字列', タイムアウト);

セッションを越えてロックを保持したければ，ロック用テーブルを使う

まだレコードが存在していないときの空振り対策として，以下のいずれかを選択する。

• あらかじめレコードを埋めておく
• アドバイザリーロック関数を併用する

https://aws.amazon.com/jp/memorydb/

従来の Redis では満たせなかった強整合性・耐障害性を担保できているため，予算さえ許せば優秀な選択肢であると考えられる。