# PostgreSQL Advisory Lock 実践：FOR UPDATE では防げない check-then-insert の競合を pg_advisory_xact_lock で直列化する > 「存在しなければINSERT」が同時リクエストで二重に通るTOCTOU競合を、PostgreSQLのAdvisory Lockで直列化する方法を解説。FOR UPDATEとの違い、session/transactionレベル、PgBouncer・RDS Proxyでの注意、UUIDから64bitキーへの変換、lock_timeout、pg_locksでの観測、制約との役割分担まで、PostgreSQL 18で実行検証したPython（SQLAlchemy 2.x）コードで示します。 - 公開日: 2026-10-04 - 著者: 友田 陽大 - タグ: PostgreSQL, データベース, 信頼性, Python, アーキテクチャ設計 - URL: https://tomodahinata.com/blog/postgresql-advisory-lock-pg-advisory-xact-lock-race-condition-guide - カテゴリ: 信頼性・非同期・リアルタイム - 総合ガイド: https://tomodahinata.com/blog/transactional-outbox-pattern-reliable-event-publishing-guide ## 要点 - 「存在チェック → INSERT」は、2つのトランザクションが同時にチェックを通過すると両方INSERTされる（TOCTOU）。READ COMMITTEDでは素朴なコードで再現する - FOR UPDATE は既存の行しかロックできない。チェック対象の行がまだ無いので、何もロックされずに競合はそのまま残る - Advisory Lock はアプリが定義した論理リソース（例：ユーザーID）をロックできる。pg_advisory_xact_lock なら COMMIT/ROLLBACK で自動解放され、PgBouncerのtransactionモードやRDS Proxyでも安全に使える - 不変条件が一意制約で書けるなら、まず制約（部分一意インデックス）を張る。Advisory Lock は『N件まで』『合計額まで』のように制約で書けない規則を直列化する道具で、最後の防衛線は制約に残す - ロック区間に外部API呼び出しを入れない。COMMIT後も続く『所有権』が必要なら、行ロックでもAdvisory Lockでもなく、テーブルに永続化したleaseを使う --- **結論から書きます。**「同じユーザーの未完了レコードがあれば拒否、無ければINSERT」という処理は、2つのリクエストが同時に来ると両方とも通ります。`SELECT ... FOR UPDATE` を足しても直りません。チェックしている行がまだ存在しないので、ロックする対象が無いからです。PostgreSQLの **Advisory Lock** は「ユーザーID」のような**アプリケーションが定義した論理リソース**をロックできるので、この check-then-insert を直列化できます。実務では `pg_advisory_xact_lock` を使い、ロックはトランザクションの終了で自動解放させます。ただし、不変条件を一意制約で書けるなら**まず制約を張る**のが先です。 この記事では、競合を実際に再現し、壊れる理由を確認したうえで、Advisory Lock による修正、接続プーラーとの関係、キー設計、タイムアウト、観測までを順に扱います。コードは **PostgreSQL 18.6 と SQLAlchemy 2.1 / psycopg 3.3 で実行し、2本の独立した接続から同時に呼ぶテストで検証済み**です（2026年10月時点）。 > **前提**：PostgreSQLの仕様は[公式ドキュメント（PostgreSQL 18）](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS)に基づきます。「〜すべき」という設計判断は筆者の実務上の判断で、公式仕様とは区別して書きます。 --- ## 1. 何が壊れるのか：1ユーザー1件の不変条件が破れる 題材は次の要件です。 > 1ユーザーにつき、**未完了（`pending`）の設定リクエストは1件まで**。未完了があるうちに新しいリクエストが来たら 409 を返す。 テーブルはこうです。 ```sql CREATE TABLE setup_requests ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid NOT NULL, status text NOT NULL CHECK (status IN ('pending', 'completed', 'cancelled')), created_at timestamptz NOT NULL DEFAULT now() ); ``` ほとんどの人が最初に書くのは、次のコードです。 ```python # ❌ 壊れた例：check-then-insert（TOCTOU）。コピーしないでください def create_setup_broken(session: Session, user_id: uuid.UUID) -> uuid.UUID: with session.begin(): exists = session.execute( text("SELECT 1 FROM setup_requests WHERE user_id = :u AND status = 'pending'"), {"u": user_id}, ).first() if exists: raise PendingSetupExists(str(user_id)) return session.execute( text("INSERT INTO setup_requests (user_id, status) VALUES (:u, 'pending') RETURNING id"), {"u": user_id}, ).scalar_one() ``` トランザクションで囲んでいるので安全に見えます。しかし、ダブルクリックやモバイル回線の再送で同じユーザーのリクエストが2本同時に届くと、次のタイムラインになります。 ```text 時刻 Request A（接続1） Request B（接続2） t1 BEGIN BEGIN t2 SELECT ... status='pending' → 0行 t3 SELECT ... status='pending' → 0行 t4 INSERT (pending) t5 INSERT (pending) t6 COMMIT COMMIT → pending が2件。不変条件が破れる ``` これが **TOCTOU（Time Of Check to Time Of Use）競合**です。チェックした時点の事実（未完了は0件）が、使う時点（INSERT）ではもう真ではありません。PostgreSQLの既定の分離レベルである READ COMMITTED では、各文は**その文の開始時点でコミット済みのデータ**を見ます。B の `SELECT` の時点で A の INSERT はまだコミットされていないので、B からは見えません。 筆者の検証では、チェックとINSERTの間に 0.3 秒の待ちを入れて競合窓を広げ、2本の接続から同時に呼ぶと**毎回 pending が2件**になりました。本番では待ちを入れなくても、負荷が高いほど同じことが起きます。 --- ## 2. なぜ FOR UPDATE では直らないのか 最初に思いつく修正は「チェックの `SELECT` に `FOR UPDATE` を付ける」です。 ```python # ❌ 壊れた例：FOR UPDATE は「まだ無い行」をロックできない exists = session.execute( text("SELECT 1 FROM setup_requests WHERE user_id = :u AND status = 'pending' FOR UPDATE"), {"u": user_id}, ).first() ``` **`FOR UPDATE` は、`SELECT` が返した既存の行にだけ行ロックを掛けます。** チェックの結果が0行なら、ロックされる行も0行です。A も B も何もロックしないまま INSERT に進むので、結果は1章と同じく2件になります（検証でも同じ結果でした）。 ここが Advisory Lock の出番になる核心です。 > **Advisory Lock と FOR UPDATE の違い**：`FOR UPDATE` はテーブルに**既にある行**をロックする。Advisory Lock はアプリケーションが決めた**任意の整数キー**をロックするので、「ユーザーXの設定リクエスト」のような**まだ行が存在しない論理リソース**を直列化できる。 なお、`FOR UPDATE` が役に立つ場面もあります。親テーブル（`users`）の行を `FOR UPDATE` でロックしてから子をチェックする方法です。ユーザー行は必ず存在するので、これでも直列化できます。ただし、ユーザー行への他の更新（プロフィール編集など）までこのロックで待たされます。ロックの粒度を「設定リクエストの作成」だけに絞れる点が Advisory Lock の利点です。 --- ## 3. Advisory Lock の基礎（公式仕様） PostgreSQLの公式ドキュメントは Advisory Lock を「アプリケーションが定義した意味を持つロック」と説明しています。システムは使い方を強制しないので advisory（勧告的）と呼ばれます。 ### 3.1 キー：64bit 整数1つ、または 32bit 整数2つ ロックの対象は**数値**です。`bigint` 1つか、`integer` 2つで指定します。公式ドキュメントにある通り、**この2つのキー空間は重ならない**ので、`pg_advisory_lock(1)` と `pg_advisory_lock(0, 1)` は別のロックです。 ### 3.2 セッションレベルとトランザクションレベル | | セッションレベル | トランザクションレベル | | --- | --- | --- | | 取得 | `pg_advisory_lock(key)` | `pg_advisory_xact_lock(key)` | | 待たない版 | `pg_try_advisory_lock(key)` | `pg_try_advisory_xact_lock(key)` | | 解放 | `pg_advisory_unlock(key)` またはセッション終了 | **トランザクション終了で自動**（手動解放の手段は無い） | | ROLLBACK した場合 | **解放されない** | 解放される | | 同じキーを複数回取得 | 取得回数分の unlock が必要（スタックする） | トランザクション終了でまとめて解放 | 公式ドキュメントの重要な一文を要約します。**セッションレベルのロックはトランザクションのセマンティクスに従いません。** ROLLBACK されたトランザクションの中で取ったロックも、ROLLBACK 後に保持されたままです。筆者の検証でも、`BEGIN; SELECT pg_advisory_lock(k); ROLLBACK;` の後、別の接続からの `pg_try_advisory_lock(k)` は `false` を返しました。 ### 3.3 共有ロックと排他ロック `_shared` が付く関数（`pg_advisory_xact_lock_shared` など）は共有ロックです。共有ロック同士は衝突せず、排他ロックとだけ衝突します。「設定の読み取りは並行してよいが、作り直しの間は全員を待たせる」といった読み書きロックとして使えます。 ### 3.4 数の上限 Advisory Lock は通常のロックと同じ共有メモリ領域に保存されます。領域の大きさは `max_locks_per_transaction` と `max_connections` で決まり、公式ドキュメントは「使い切るとサーバーはロックを一切付与できなくなる」と警告しています。1トランザクションで何万ものキーをロックする設計は避けます。 --- ## 4. 正しい実装：pg_advisory_xact_lock で直列化する 修正版です。ロックを取ってからチェックし、INSERTし、COMMITで解放します。 ```python import hashlib import uuid from sqlalchemy import text from sqlalchemy.exc import OperationalError from sqlalchemy.orm import Session class PendingSetupExists(Exception): """業務エラー：上限まで未完了がある（HTTP 409 に対応させる）。""" class LockBusy(Exception): """lock_timeout 内にロックを取れなかった（HTTP 409/503 で再試行を促す）。""" def advisory_key(namespace: str, resource_id: uuid.UUID | str) -> int: """論理リソースを PostgreSQL の bigint ロックキーへ決定的に写像する。""" digest = hashlib.sha256(f"{namespace}:{resource_id}".encode()).digest() return int.from_bytes(digest[:8], byteorder="big", signed=True) def create_setup( session: Session, user_id: uuid.UUID, *, max_pending: int = 1, lock_timeout: str = "2s", ) -> uuid.UUID: key = advisory_key("setup_request", user_id) try: with session.begin(): # ロック取得「後」の文が最新のコミットを見られるよう、分離レベルを明示する（5.2節） session.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")) # SET はバインド変数を取れないので set_config(..., is_local => true) を使う session.execute( text("SELECT set_config('lock_timeout', :t, true)"), {"t": lock_timeout} ) session.execute(text("SELECT pg_advisory_xact_lock(:k)"), {"k": key}) pending = session.execute( text("SELECT count(*) FROM setup_requests WHERE user_id = :u AND status = 'pending'"), {"u": user_id}, ).scalar_one() if pending >= max_pending: raise PendingSetupExists(str(user_id)) return session.execute( text("INSERT INTO setup_requests (user_id, status) VALUES (:u, 'pending') RETURNING id"), {"u": user_id}, ).scalar_one() except OperationalError as exc: # SQLSTATE 55P03 = lock_not_available（lock_timeout を超えた） if getattr(exc.orig, "sqlstate", None) == "55P03": raise LockBusy(str(user_id)) from exc raise ``` このコードが保証すること： 1. **同じユーザーIDのリクエストは、チェックからCOMMITまでが直列に実行される。** B は A のトランザクションが終わるまで `pg_advisory_xact_lock` で待ち、A の INSERT がコミットされた後にチェックする。READ COMMITTED では各文がその文の開始時点のスナップショットを使うので、ロック取得後の `SELECT` には A の行が見える（この前提が崩れる分離レベルについては5.2節）。 2. **解放漏れが起きない。** 正常終了でも、`PendingSetupExists` による ROLLBACK でも、接続断でも、トランザクションが終われば解放される。 3. **別ユーザーは互いに待たない。** キーがユーザーごとに違うので、ロックの粒度は「1ユーザーの設定作成」だけ。 4. **待ち時間に上限がある。** `set_config('lock_timeout', ..., true)` はトランザクション内だけ有効な `SET LOCAL` と同じ効果で、2秒待っても取れなければ SQLSTATE `55P03` で失敗する（検証済み）。 ```text 時刻 Request A Request B t1 BEGIN; pg_advisory_xact_lock(k) → 取得 t2 BEGIN; pg_advisory_xact_lock(k) → 待機 t3 SELECT count(*) → 0 t4 INSERT (pending) t5 COMMIT（ロック解放） t6 → 取得 t7 SELECT count(*) → 1（A の行が見える） t8 ROLLBACK → 409 ``` 検証では、同じユーザーに2本同時に投げると結果は必ず「1本成功・1本 409」、`max_pending=3` で5本同時に投げると「3本成功・2本 409」になりました。 ### 4.1 待たない版：pg_try_advisory_xact_lock 「同じ操作が処理中なら、待たずに『処理中です』と返したい」API では try 版を使います。取れなければ `false` が返ります。 ```python def try_create_setup(session: Session, user_id: uuid.UUID) -> uuid.UUID | None: key = advisory_key("setup_request", user_id) with session.begin(): acquired = session.execute( text("SELECT pg_try_advisory_xact_lock(:k)"), {"k": key} ).scalar_one() if not acquired: return None # 呼び出し側で 409 "processing" を返す ... # 以降は create_setup と同じ ``` ただし、try 版で取れなかったことは「相手が成功する」ことを意味しません。相手がこの後 ROLLBACK する可能性もあります。クライアントには再試行してよいことを伝えます。 --- ## 5. まず制約、次に Advisory Lock：役割分担 ここまで Advisory Lock を説明してきましたが、**この題材の「1ユーザーにつき未完了は1件」に限れば、もっと良い答えがあります。** 部分一意インデックスです。 ```sql CREATE UNIQUE INDEX setup_requests_one_pending_per_user ON setup_requests (user_id) WHERE status = 'pending'; ``` この制約があれば、1章の壊れたコードを同時に2本走らせても、2本目の INSERT は一意制約違反で失敗し、pending は1件のままです（検証済み）。制約は**全ての書き込み経路に強制**されます。管理画面からの手動INSERTにも、将来別のエンジニアが書くバッチにも効きます。Advisory Lock は、同じキーを取るコードを通った書き込みにしか効きません。 それでも Advisory Lock が必要になるのは、次のような場合です。 | 規則 | 一意制約で書けるか | 道具 | | --- | --- | --- | | 1ユーザー未完了1件 | 書ける（部分一意インデックス） | **制約**。Advisory Lock は不要（409 を綺麗に返したいなら併用） | | プランごとに未完了 N 件まで | 書けない | Advisory Lock ＋ COUNT | | 1日の出金合計が上限以内 | 書けない | Advisory Lock（または行ロック）＋ SUM | | チェック前に重い外部検証が要る | 書けない | Advisory Lock で無駄な並行実行を抑止（ただし外部呼び出し中は握らない。7章） | 筆者の判断基準はこうです。**制約で書ける不変条件は制約に書く。Advisory Lock は制約で書けない規則を直列化するために使い、書ける部分は制約として併存させる。** 併用すると、通常は Advisory Lock で整然と 409 を返し、ロックを取り忘れた経路が将来生まれても制約が最後の防衛線として残ります。 ### 5.1 SERIALIZABLE という選択肢 分離レベルを SERIALIZABLE にする方法もあります。PostgreSQL の SERIALIZABLE（SSI）は、この種の「読んでから書く」競合を検出します。筆者の検証では、同じ check-then-insert を SERIALIZABLE で2本同時に実行すると、一方が `serialization_failure`（SQLSTATE `40001`）で失敗し、件数は1件に保たれました。 トレードオフは、**失敗したトランザクションを丸ごとリトライするコードが必須**になることです。全体を SERIALIZABLE で設計できるなら有力ですが、既存のアプリの一部だけを守りたいなら、Advisory Lock の方が影響範囲を限定しやすい、というのが筆者の判断です。 ### 5.2 このパターンは READ COMMITTED 前提 Advisory Lock による直列化は、**ロックを取った後の `SELECT` が、待っている間にコミットされた行を見られる**ことに依存しています。READ COMMITTED ではこれが成り立ちますが、**REPEATABLE READ ではスナップショットがトランザクションの最初の文で固定される**ため、成り立ちません。B の最初の文（`set_config` やロック関数そのもの）の時点で取られたスナップショットには、後から A がコミットした行が含まれないからです。 筆者の検証では、エンジンの既定を REPEATABLE READ にして、`SET TRANSACTION` の行を外した版を2本同時に実行すると、両方とも「0件」と判断して pending が2件になりました。本記事のコードがトランザクションの先頭で `SET TRANSACTION ISOLATION LEVEL READ COMMITTED` を実行しているのはこのためで、この行を入れた版は REPEATABLE READ が既定のエンジンでも1件に保たれました（検証済み）。アプリ全体の既定の分離レベルを変えたときに、この関数だけ静かに壊れることを防ぎます。 --- ## 6. 接続プーリングとの関係：セッションレベルを避ける理由 本番の多くの構成では、アプリとPostgreSQLの間に PgBouncer や RDS Proxy のような接続プーラーが入ります。ここでセッションレベルの Advisory Lock は問題を起こします。 - **PgBouncer の transaction モード**：トランザクションごとにサーバー接続を貸し出すので、`pg_advisory_lock` を取った接続が、次のトランザクションでは別のクライアントに渡ります。PgBouncer の公式の機能表でも、セッションレベルの Advisory Lock は transaction モードで使えない機能として挙げられています。 - **RDS Proxy（PostgreSQL）**：公式ドキュメントは `pg_advisory_lock` や `pg_try_advisory_lock` の使用を**ピン留め（pinning）の原因**として挙げています。ピン留めされた接続は多重化されず、プーラーの効果が落ちます。一方で、`pg_advisory_xact_lock` などのトランザクションレベルの関数ではピン留めしないと明記されています。 - **アプリ側の接続プール（SQLAlchemy の QueuePool など）**：プーラーが無くても、セッションレベルのロックを unlock し忘れた接続がプールに戻ると、その接続を次に借りた無関係なリクエストがロックを持ち続けます。 **`pg_advisory_xact_lock` はトランザクションの終了で必ず解放されるので、これらの問題がすべて起きません。** セッションレベルが必要なのは、マイグレーションツールの「同時に1つだけ実行」のように、1本の専用接続を長時間保持する用途にほぼ限られます。 --- ## 7. ロックを握ったまま外部APIを呼ばない Advisory Lock の区間には、**DBの中で完結する処理だけ**を入れます。 ```python # ❌ 壊れた例：ロック区間で外部APIを呼んでいる with session.begin(): session.execute(text("SELECT pg_advisory_xact_lock(:k)"), {"k": key}) ... stripe_client.v1.customers.create(...) # 数秒〜タイムアウトまで、同じユーザーの全リクエストが待つ session.execute(text("INSERT ...")) ``` 問題は3つあります。外部APIが遅い間、同じキーの全リクエストが待たされます。トランザクションと接続も握り続けるので、接続プールが枯渇します。そして外部APIは成功したのに後続の INSERT が失敗して ROLLBACK すると、DBには記録が無いのに外部には副作用が残ります。 外部への副作用は、DBトランザクションの中で「やるべき操作」をテーブルに記録し、COMMIT後に別のワーカーが実行する形にします（[Transactional Outboxでdual-writeを解消する方法](/blog/transactional-outbox-pattern-reliable-event-publishing-guide)）。そのワーカーを複数動かすときに必要になるのが、**COMMIT後も続く所有権**です。行ロックも `pg_advisory_xact_lock` もトランザクションの終了で消えるので、この用途には使えません。テーブルに永続化した lease と fencing token を使う設計は、[Outbox Dispatcherを複数workerで安全に動かす方法](/blog/outbox-dispatcher-skip-locked-lease-fencing-token-guide)で詳しく扱います。 --- ## 8. キー設計：UUID を 64bit に写像する ロックのキーは数値なので、UUIDや文字列のIDは写像が必要です。 ### 8.1 推奨：アプリ側でハッシュし、名前空間を含める 本記事の `advisory_key()` は、`"setup_request:"` という文字列を SHA-256 でハッシュし、先頭 8 バイトを符号付き 64bit 整数にしています。 - **名前空間（`setup_request`）を含める**のは、同じユーザーIDを別の用途（例：`withdrawal`）でロックしたときに、無関係な処理同士が待ち合わないようにするためです。 - **アプリ側で計算する**のは、写像のルールをコードとテストで固定し、言語やDBのバージョンに依存させないためです。 - **複数の言語から同じリソースをロックするなら、写像を1つに揃えます。** Python と TypeScript で別のハッシュを使うと、同じユーザーでも別のキーになり、互いを排他しません。SHA-256 の先頭 8 バイト（ビッグエンディアン・符号付き）は、Node.js では `createHash("sha256").update(s).digest().readBigInt64BE(0)` で同じ値になります（両方で計算して一致を確認済み）。 ### 8.2 衝突は「偽の競合」であって、正しさの問題ではない 64bit に縮める以上、別のリソースが同じキーになる可能性はゼロではありません。ただし、**Advisory Lock を相互排他にだけ使う限り、衝突の影響は「無関係な2つのリソースが同じロックを待つ」ことだけ**です。直列化が過剰になるだけで、相互排他は破れません。64bit の空間で衝突が実害になる頻度は、通常は無視できます。 逆に、「ロックを取れた＝自分がそのリソースの唯一の所有者だ」という**証明として使う設計**では、衝突も、取得後のセッション切断も問題になります。所有権の証明が要るなら、7章の通り永続化された lease と fencing token を使います。 ### 8.3 2×int 形式を使う場合 `pg_advisory_xact_lock(namespace_id, resource_id)` のように、第1引数を用途の番号、第2引数をリソースの番号にする方法もあります。リソースIDが32bitの整数（`integer` の主キー）なら衝突は起きませんが、UUIDを32bitに縮めると衝突の確率は64bitより大幅に上がります。UUIDを使うなら bigint 1つの形式が扱いやすいでしょう。 --- ## 9. デッドロックとロックの順序 複数のキーをロックするなら、**全経路で同じ順序で取得**します。A がキー1→キー2、B がキー2→キー1 の順で取ると、互いに相手を待つデッドロックになります。PostgreSQLはデッドロックを検出し、関係するトランザクションの1つを中断します。ただし公式ドキュメントにある通り、どちらが中断されるかは予測できず、頼るべきではありません。 ```python # 複数リソースをロックするときは、キーの数値順に取る for k in sorted({advisory_key("account", src), advisory_key("account", dst)}): session.execute(text("SELECT pg_advisory_xact_lock(:k)"), {"k": k}) ``` もう1つの落とし穴は、**`LIMIT` と組み合わせたロック関数**です。公式ドキュメントの例をそのまま示します。 ```sql SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger! SELECT pg_advisory_lock(q.id) FROM ( SELECT id FROM foo WHERE id > 12345 LIMIT 100 ) q; -- ok ``` 2つ目は、`LIMIT` が適用される前にロック関数が評価される可能性があり、想定外の行のロックまで取ってしまうことがあります。セッションレベルなら、それらは解放されずに残ります。ロック関数は、対象を確定させたサブクエリの外側で呼びます。 --- ## 10. テスト：本物のPostgreSQLで並行実行する ロックの正しさは、**本物のPostgreSQLに2本以上の独立した接続を張って、同時に実行する**ことでしか確かめられません。SQLiteやモックではPostgreSQLのロックの意味を再現できません。 ```python import threading import uuid from concurrent.futures import ThreadPoolExecutor from sqlalchemy import text from sqlalchemy.orm import Session def race(engine, fn, user_id, n=2, **kw): """n 本の独立した接続から同時に fn を呼ぶ。""" barrier = threading.Barrier(n) def worker(): with Session(engine) as s: barrier.wait() # 全スレッドがそろってから同時に開始 try: return ("ok", fn(s, user_id, **kw)) except PendingSetupExists: return ("exists", None) with ThreadPoolExecutor(n) as ex: return [f.result() for f in [ex.submit(worker) for _ in range(n)]] def test_advisory_lock_serializes(engine): uid = uuid.uuid4() results = race(engine, create_setup, uid) assert sorted(r[0] for r in results) == ["exists", "ok"] with engine.connect() as c: assert c.execute( text("SELECT count(*) FROM setup_requests WHERE user_id = :u AND status = 'pending'"), {"u": uid}, ).scalar_one() == 1 ``` 筆者は次のケースを PostgreSQL 18 のコンテナに対して実行し、すべて通過することを確認しています。 - 壊れた check-then-insert → pending が2件になる（失敗の再現） - `FOR UPDATE` 版 → 同じく2件になる（失敗の再現） - Advisory Lock 版 → 1本成功・1本 409、件数は1件 - `max_pending=3` で5本同時 → 3本成功 - 別ユーザーのロックには待たされない - 別の接続がロックを保持中に `lock_timeout='200ms'` → `LockBusy`、保持側の ROLLBACK 後は成功 - セッションレベルのロックは ROLLBACK 後も保持され、unlock で解放される - 部分一意インデックスがあれば、壊れたコードでも2件目は一意制約違反になる - エンジンの既定が REPEATABLE READ でも、Advisory Lock 版は1件に保たれる（`SET TRANSACTION ISOLATION LEVEL READ COMMITTED` の効果） - Python の `advisory_key()` と Node.js の導出が同じ値を返す 失敗を再現するテストでは、チェックとINSERTの間に短い `sleep` を入れて競合窓を広げると、結果が安定します。この `sleep` はテスト専用で、本番コードには入れません。 --- ## 11. 観測：pg_locks で誰が何を待っているか Advisory Lock は `pg_locks` ビューで観測できます。公式ドキュメントによれば、bigint キーは上位32bitが `classid`、下位32bitが `objid` に表示され、`objsubid` は 1 になります。元のキーは `(classid::bigint << 32) | objid::bigint` で復元できます（負のキーも正しく復元されることを確認済み）。 ```sql -- 現在保持・待機中の Advisory Lock と、そのセッションの状況 SELECT l.pid, (l.classid::bigint << 32) | l.objid::bigint AS lock_key, l.mode, l.granted, a.state, now() - a.xact_start AS xact_age, left(a.query, 80) AS query FROM pg_locks AS l JOIN pg_stat_activity AS a USING (pid) WHERE l.locktype = 'advisory' AND l.objsubid = 1 ORDER BY l.granted, xact_age DESC; ``` `granted = false` の行が待っているセッションです。運用では次を監視します。 - **待機中の Advisory Lock の数と最長待ち時間**：増え続けるなら、ロック区間が長すぎるか、キーの粒度が粗すぎる。 - **`xact_age` が長いロック保持者**：ロック区間に外部呼び出しが紛れ込んでいる兆候。 - **`LockBusy`（SQLSTATE `55P03`）の発生率**：アプリ側のメトリクスとして出す。急増は同一キーへの集中か、保持者の詰まりを示す。 --- ## 12. 使うべきでない場面 - **不変条件が一意制約・CHECK制約・排他制約で書ける**：制約を使う（5章）。 - **ロック区間に外部APIやユーザー操作の待ちが入る**：Outbox と lease を使う（7章）。 - **複数のデータベースやサービスをまたぐ排他**：Advisory Lock はデータベースごとにローカルです。別のDBに接続したプロセスとは排他できません。 - **ロックの取得を「所有者の証明」として使いたい**：接続が切れればロックは消え、その事実を元の処理は知りません。fencing token を使います。 - **大量のキーを1トランザクションで取る**：共有メモリの上限に当たります（3.4節）。 --- ## まとめ - check-then-insert は、READ COMMITTED では同時リクエストで両方通る（TOCTOU）。 - `FOR UPDATE` は既存の行しかロックできないので、まだ無い行に対するチェックは守れない。 - Advisory Lock はアプリが定義したキーをロックできる。実務では `pg_advisory_xact_lock` を使い、`lock_timeout` で待ち時間を区切る。 - セッションレベルのロックは ROLLBACK で解放されず、PgBouncer の transaction モードでは使えず、RDS Proxy ではピン留めを起こす。 - キーは名前空間付きで 64bit にハッシュする。衝突は偽の競合を生むだけで、相互排他は破れない。 - 制約で書ける不変条件は制約に書き、Advisory Lock は制約で書けない規則に使う。ロック区間に外部APIを入れない。 COMMIT後も続く排他（外部APIを呼ぶワーカーの所有権）が必要になったら、次は [Outbox Dispatcherの lease と fencing token](/blog/outbox-dispatcher-skip-locked-lease-fencing-token-guide) へ進んでください。MVCC と行ロックの全体像は [PostgreSQL の MVCC・トランザクション分離の実務ガイド](/blog/postgresql-mvcc-transaction-isolation-vacuum-autovacuum-guide)、プーラーの transaction モードの他の注意点は [PostgreSQL 接続プーリング実践](/blog/postgresql-connection-pooling-pgbouncer-serverless-guide) にまとめています。