# PostgreSQL Advisory Locks in Practice: Serializing the Check-Then-Insert Race That FOR UPDATE Can't Stop > How to serialize the TOCTOU race where 'insert if it doesn't exist' succeeds twice under concurrent requests, using PostgreSQL advisory locks. Covers the difference from FOR UPDATE, session vs transaction level, PgBouncer and RDS Proxy caveats, mapping UUIDs to 64-bit keys, lock_timeout, observing locks in pg_locks, and when a constraint is the better tool — with Python (SQLAlchemy 2.x) code tested against PostgreSQL 18. - Published: 2026-10-04 - Author: 友田 陽大 - Tags: PostgreSQL, データベース, 信頼性, Python, アーキテクチャ設計 - URL: https://tomodahinata.com/en/blog/postgresql-advisory-lock-pg-advisory-xact-lock-race-condition-guide - Category: Reliability, async & real-time - Pillar guide: https://tomodahinata.com/en/blog/transactional-outbox-pattern-reliable-event-publishing-guide ## Key points - 'Check for an existing row, then INSERT' lets both of two concurrent transactions pass the check and insert (TOCTOU). Under READ COMMITTED it reproduces with ordinary code - FOR UPDATE can only lock rows that already exist. The row being checked for doesn't exist yet, so nothing is locked and the race remains - An advisory lock locks an application-defined resource (such as a user ID). pg_advisory_xact_lock is released automatically at COMMIT/ROLLBACK and is safe behind PgBouncer's transaction mode and RDS Proxy - If the invariant can be written as a unique constraint (a partial unique index), add the constraint first. Advisory locks are for rules a constraint can't express, such as 'at most N' or 'total amount under a limit', and the constraint stays as the last line of defence - Never call an external API inside the locked section. If you need ownership that survives COMMIT, use a lease persisted in a table, not a row lock or an advisory lock --- **The short answer.** "Reject if this user already has a pending record, otherwise INSERT" lets both requests through when two arrive at once. Adding `SELECT ... FOR UPDATE` doesn't fix it, because the row you're checking for doesn't exist yet — there is nothing to lock. PostgreSQL **advisory locks** lock an **application-defined logical resource** such as a user ID, so they can serialize this check-then-insert. In practice you use `pg_advisory_xact_lock` and let the transaction end release the lock. But if the invariant can be expressed as a unique constraint, **add the constraint first.** This article reproduces the race, shows why it breaks, then walks through the advisory-lock fix, connection poolers, key design, timeouts and observability. The code was **run against PostgreSQL 18.6 with SQLAlchemy 2.1 / psycopg 3.3, and tested by calling it concurrently from two independent connections** (October 2026). > **Ground rules**: PostgreSQL behaviour is taken from the [official PostgreSQL 18 documentation](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS). Where I say "should", that is my own engineering judgement, and I keep it separate from what the documentation specifies. --- ## 1. What breaks: a one-per-user invariant is violated The requirement: > Each user may have **at most one pending setup request**. If a new request arrives while one is still pending, return 409. The table: ```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() ); ``` Almost everyone starts with this: ```python # ❌ Broken: check-then-insert (TOCTOU). Do not copy this 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() ``` It runs inside a transaction, so it looks safe. But when a double-click or a mobile retry sends two requests for the same user at the same moment, you get this timeline: ```text time Request A (connection 1) Request B (connection 2) t1 BEGIN BEGIN t2 SELECT ... status='pending' → 0 rows t3 SELECT ... status='pending' → 0 rows t4 INSERT (pending) t5 INSERT (pending) t6 COMMIT COMMIT → two pending rows; the invariant is broken ``` This is a **TOCTOU (time of check to time of use) race**. The fact you checked (zero pending rows) is no longer true by the time you act on it (the INSERT). Under READ COMMITTED, PostgreSQL's default isolation level, each statement sees **data committed before that statement began**. When B runs its `SELECT`, A's INSERT hasn't committed, so B can't see it. In my tests, widening the race window with a 0.3-second pause between the check and the INSERT and calling the function from two connections at once produced **two pending rows every time**. In production you don't need the pause: the busier the system, the more often it happens. --- ## 2. Why FOR UPDATE doesn't fix it The first fix people reach for is adding `FOR UPDATE` to the check: ```python # ❌ Broken: FOR UPDATE cannot lock a row that doesn't exist yet exists = session.execute( text("SELECT 1 FROM setup_requests WHERE user_id = :u AND status = 'pending' FOR UPDATE"), {"u": user_id}, ).first() ``` **`FOR UPDATE` takes row locks only on the existing rows the `SELECT` returns.** If the check returns zero rows, zero rows are locked. Neither A nor B locks anything, both go on to INSERT, and you end up with two rows exactly as in section 1 (my tests showed the same result). This is precisely where advisory locks come in. > **Advisory lock vs FOR UPDATE**: `FOR UPDATE` locks rows **that already exist** in a table. An advisory lock locks **an arbitrary integer key** chosen by the application, so it can serialize **a logical resource for which no row exists yet**, such as "user X's setup requests". `FOR UPDATE` does have a role here: you could lock the parent row (`users`) with `FOR UPDATE` and then check the children. The user row always exists, so this also serializes. The cost is that every other update to that user row (profile edits and so on) now waits on the same lock. An advisory lock lets you narrow the lock to just "creating a setup request". --- ## 3. Advisory lock basics (from the documentation) The PostgreSQL documentation describes advisory locks as locks "that have application-defined meanings". The system doesn't enforce how they are used, hence "advisory". ### 3.1 Keys: one 64-bit integer, or two 32-bit integers You lock a **number**: either one `bigint` or two `integer`s. As the documentation notes, **the two key spaces do not overlap**, so `pg_advisory_lock(1)` and `pg_advisory_lock(0, 1)` are different locks. ### 3.2 Session level vs transaction level | | Session level | Transaction level | | --- | --- | --- | | Acquire | `pg_advisory_lock(key)` | `pg_advisory_xact_lock(key)` | | Non-blocking variant | `pg_try_advisory_lock(key)` | `pg_try_advisory_xact_lock(key)` | | Release | `pg_advisory_unlock(key)` or end of session | **Automatically at transaction end** (no manual release) | | On ROLLBACK | **Not released** | Released | | Same key acquired repeatedly | Needs one unlock per acquisition (they stack) | All released together at transaction end | The key sentence in the documentation, paraphrased: **session-level advisory lock requests do not honour transaction semantics.** A lock acquired in a transaction that is later rolled back is still held after the rollback. My tests confirmed this: after `BEGIN; SELECT pg_advisory_lock(k); ROLLBACK;`, `pg_try_advisory_lock(k)` from another connection returned `false`. ### 3.3 Shared and exclusive locks Functions ending in `_shared` (such as `pg_advisory_xact_lock_shared`) take shared locks. Shared locks don't conflict with each other, only with exclusive locks. You can use them as a reader-writer lock: "reads of the configuration may run in parallel, but a rebuild makes everyone wait". ### 3.4 Capacity limits Advisory locks live in the same shared-memory pool as ordinary locks, sized by `max_locks_per_transaction` and `max_connections`. The documentation warns that if you exhaust it, the server can't grant any locks at all. Don't design something that locks tens of thousands of keys in one transaction. --- ## 4. The correct implementation: serialize with pg_advisory_xact_lock Here is the fixed version. Take the lock, then check, then INSERT; COMMIT releases the lock. ```python import hashlib import uuid from sqlalchemy import text from sqlalchemy.exc import OperationalError from sqlalchemy.orm import Session class PendingSetupExists(Exception): """Business error: the user already has the maximum number of pending requests (maps to HTTP 409).""" class LockBusy(Exception): """Could not get the lock within lock_timeout (HTTP 409/503, ask the client to retry).""" def advisory_key(namespace: str, resource_id: uuid.UUID | str) -> int: """Deterministically map a logical resource to a PostgreSQL bigint lock key.""" 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(): # Pin the isolation level so statements after the lock see the latest commits (section 5.2) session.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")) # SET can't take bind parameters, so use 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 exceeded) if getattr(exc.orig, "sqlstate", None) == "55P03": raise LockBusy(str(user_id)) from exc raise ``` What this code guarantees: 1. **For the same user ID, everything from the check to COMMIT runs serially.** B waits in `pg_advisory_xact_lock` until A's transaction ends, and checks only after A's INSERT has committed. Under READ COMMITTED each statement uses a snapshot taken when that statement starts, so the `SELECT` after the lock sees A's row (section 5.2 covers isolation levels where this premise breaks). 2. **The lock can't leak.** Whether the transaction commits, rolls back because of `PendingSetupExists`, or the connection drops, the lock is released when the transaction ends. 3. **Different users never wait for each other.** Each user has a different key, so the lock covers only "creating a setup request for this user". 4. **Waiting is bounded.** `set_config('lock_timeout', ..., true)` has the same effect as `SET LOCAL`, valid only inside the transaction; if the lock isn't acquired within 2 seconds, the statement fails with SQLSTATE `55P03` (verified). ```text time Request A Request B t1 BEGIN; pg_advisory_xact_lock(k) → acquired t2 BEGIN; pg_advisory_xact_lock(k) → waits t3 SELECT count(*) → 0 t4 INSERT (pending) t5 COMMIT (lock released) t6 → acquired t7 SELECT count(*) → 1 (A's row is visible) t8 ROLLBACK → 409 ``` In my tests, two simultaneous requests for the same user always produced "one success, one 409", and five simultaneous requests with `max_pending=3` produced "three successes, two 409s". ### 4.1 The non-blocking variant: pg_try_advisory_xact_lock For an API that should answer "already in progress" instead of waiting, use the try variant. It returns `false` if the lock isn't available. ```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 # the caller returns 409 "processing" ... # the rest is the same as create_setup ``` Failing to get the lock doesn't mean the other request will succeed — it may still roll back. Tell the client it's safe to retry. --- ## 5. Constraint first, advisory lock second So far this article has been about advisory locks, but **for this particular invariant, "at most one pending per user", there is a better answer**: a partial unique index. ```sql CREATE UNIQUE INDEX setup_requests_one_pending_per_user ON setup_requests (user_id) WHERE status = 'pending'; ``` With this constraint in place, even the broken code from section 1 run twice concurrently leaves only one pending row: the second INSERT fails with a unique violation (verified). A constraint is **enforced on every write path** — a manual INSERT from an admin screen, a batch job someone writes next year. An advisory lock only protects writes that go through code that takes the same key. Advisory locks are still needed in cases like these: | Rule | Expressible as a unique constraint? | Tool | | --- | --- | --- | | At most one pending per user | Yes (partial unique index) | **The constraint.** No advisory lock needed (combine them if you want a clean 409) | | At most N pending, depending on the plan | No | Advisory lock + COUNT | | Daily withdrawals must total under a limit | No | Advisory lock (or a row lock) + SUM | | An expensive external check precedes the insert | No | Advisory lock to stop wasted parallel work (but don't hold it during the external call; section 7) | My rule of thumb: **put every invariant you can express as a constraint into a constraint. Use advisory locks to serialize rules a constraint can't express, and keep the expressible part as a constraint alongside.** Combined, the advisory lock gives you an orderly 409 in normal operation, and if some future code path forgets to take the lock, the constraint is still there as the last line of defence. ### 5.1 The SERIALIZABLE option You could instead run the transaction at SERIALIZABLE. PostgreSQL's SERIALIZABLE (SSI) detects this kind of read-then-write race. In my tests, running the same check-then-insert twice concurrently at SERIALIZABLE made one of them fail with `serialization_failure` (SQLSTATE `40001`), and only one row was inserted. The trade-off is that **you must write code that retries the entire failed transaction.** If you can design the whole application around SERIALIZABLE, it's a strong option. If you only need to protect one part of an existing application, my judgement is that an advisory lock keeps the blast radius smaller. ### 5.2 This pattern assumes READ COMMITTED Serializing with an advisory lock depends on **the `SELECT` after the lock seeing rows committed while you waited.** That holds under READ COMMITTED, but **not under REPEATABLE READ, where the snapshot is fixed at the transaction's first statement.** The snapshot taken at B's first statement (`set_config`, or the lock call itself) doesn't include the row A commits afterwards. In my tests, with the engine's default set to REPEATABLE READ and the `SET TRANSACTION` line removed, two concurrent calls both counted zero and left two pending rows. That's why this article's code runs `SET TRANSACTION ISOLATION LEVEL READ COMMITTED` at the start of the transaction; with that line, the count stayed at one even on an engine defaulting to REPEATABLE READ (verified). It stops this function from silently breaking if someone changes the application-wide default isolation level. --- ## 6. Connection pooling: why to avoid session-level locks In most production setups a pooler such as PgBouncer or RDS Proxy sits between the application and PostgreSQL. Session-level advisory locks cause trouble there. - **PgBouncer in transaction mode** hands out a server connection per transaction, so the connection that took `pg_advisory_lock` is given to a different client for its next transaction. PgBouncer's official feature table lists session-level advisory locks among the features that don't work in transaction mode. - **RDS Proxy (PostgreSQL)**: the AWS documentation lists using `pg_advisory_lock` or `pg_try_advisory_lock` as a **cause of pinning**. A pinned connection isn't multiplexed, which erodes the point of the proxy. It also states explicitly that transaction-level functions such as `pg_advisory_xact_lock` do not cause pinning. - **Application-side pools (SQLAlchemy's QueuePool and the like)**: even without an external pooler, a connection returned to the pool with a session-level lock still held passes that lock to whichever unrelated request borrows it next. **`pg_advisory_xact_lock` is always released when the transaction ends, so none of these problems can happen.** Session-level locks are really only needed for things like a migration tool's "only one runner at a time", which holds one dedicated connection for a long time. --- ## 7. Don't call external APIs while holding the lock Put **only work that completes inside the database** in an advisory-locked section. ```python # ❌ Broken: calling an external API inside the locked section with session.begin(): session.execute(text("SELECT pg_advisory_xact_lock(:k)"), {"k": key}) ... stripe_client.v1.customers.create(...) # every request for this user waits, for seconds or until timeout session.execute(text("INSERT ...")) ``` Three things go wrong. While the external API is slow, every request for the same key waits. The transaction and its connection are held the whole time, so the pool runs dry. And if the external call succeeds but the following INSERT fails and rolls back, the side effect remains outside with no record of it in your database. For external side effects, record "the operation to perform" in a table inside the DB transaction and have a separate worker execute it after COMMIT ([eliminating dual writes with a transactional outbox](/blog/transactional-outbox-pattern-reliable-event-publishing-guide)). When you run several such workers, you need **ownership that survives COMMIT**. Row locks and `pg_advisory_xact_lock` both disappear at transaction end, so neither works for that. The design with a lease and fencing token persisted in the table is covered in [running an outbox dispatcher safely with multiple workers](/blog/outbox-dispatcher-skip-locked-lease-fencing-token-guide). --- ## 8. Key design: mapping a UUID to 64 bits Lock keys are numbers, so UUIDs and string IDs need mapping. ### 8.1 Recommended: hash in the application, include a namespace The `advisory_key()` function in this article hashes the string `"setup_request:"` with SHA-256 and reads the first 8 bytes as a signed 64-bit integer. - **The namespace (`setup_request`)** keeps unrelated work from waiting on each other when the same user ID is locked for another purpose (say, `withdrawal`). - **Computing it in the application** pins the mapping in code and tests, independent of language or database version. - **If several languages lock the same resource, use one mapping.** If Python and TypeScript use different hashes, the same user maps to different keys and they don't exclude each other. The first 8 bytes of SHA-256 (big-endian, signed) give the same value in Node.js with `createHash("sha256").update(s).digest().readBigInt64BE(0)` (I computed both and confirmed they match). ### 8.2 A collision means false contention, not a correctness bug Shrinking to 64 bits means two resources can, in principle, map to the same key. But **as long as the advisory lock is used only for mutual exclusion, a collision just makes two unrelated resources wait for the same lock.** You serialize slightly more than necessary; mutual exclusion is never violated. In a 64-bit space, the frequency at which this causes real harm is normally negligible. If instead you **use "I got the lock" as proof that you are the sole owner of a resource**, collisions — and a dropped session after acquiring the lock — become real problems. If you need proof of ownership, use a persisted lease and fencing token as described in section 7. ### 8.3 Using the two-integer form You can also call `pg_advisory_xact_lock(namespace_id, resource_id)` with a purpose number as the first argument and the resource number as the second. If the resource ID is a 32-bit integer (an `integer` primary key), there are no collisions; but squeezing a UUID into 32 bits makes collisions far more likely than with 64. With UUIDs, the single-bigint form is easier to work with. --- ## 9. Deadlocks and lock ordering When you lock several keys, **acquire them in the same order on every code path.** If A takes key 1 then key 2 while B takes key 2 then key 1, each waits for the other: a deadlock. PostgreSQL detects deadlocks and aborts one of the transactions involved, but as the documentation says, which one is aborted is hard to predict and shouldn't be relied upon. ```python # When locking several resources, take them in numeric key order for k in sorted({advisory_key("account", src), advisory_key("account", dst)}): session.execute(text("SELECT pg_advisory_xact_lock(:k)"), {"k": k}) ``` The other pitfall is **lock functions combined with `LIMIT`.** Here is the documentation's own example: ```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 ``` In the second form, the lock function may be evaluated before `LIMIT` is applied, acquiring locks on rows you didn't intend. At session level, those locks are never released. Call lock functions outside a subquery that has already fixed the set of targets. --- ## 10. Testing: run concurrently against a real PostgreSQL The only way to verify locking is **to open two or more independent connections to a real PostgreSQL and run the code at the same time.** SQLite and mocks can't reproduce PostgreSQL's locking semantics. ```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): """Call fn at the same moment from n independent connections.""" barrier = threading.Barrier(n) def worker(): with Session(engine) as s: barrier.wait() # start only once every thread is ready 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 ``` I ran the following cases against a PostgreSQL 18 container and all of them pass: - Broken check-then-insert → two pending rows (failure reproduced) - `FOR UPDATE` version → also two rows (failure reproduced) - Advisory-lock version → one success and one 409, one row - Five concurrent requests with `max_pending=3` → three successes - A lock held for another user doesn't block - With another connection holding the lock, `lock_timeout='200ms'` → `LockBusy`; after the holder rolls back, the call succeeds - A session-level lock survives ROLLBACK and is released by unlock - With the partial unique index in place, even the broken code fails the second insert with a unique violation - With the engine defaulting to REPEATABLE READ, the advisory-lock version still keeps one row (thanks to `SET TRANSACTION ISOLATION LEVEL READ COMMITTED`) - Python's `advisory_key()` and the Node.js derivation return the same value For the tests that reproduce failures, a short `sleep` between the check and the INSERT widens the race window and makes the result deterministic. That `sleep` belongs in tests only, never in production code. --- ## 11. Observability: who holds what, and who is waiting Advisory locks appear in the `pg_locks` view. According to the documentation, a bigint key is shown with its high-order half in `classid`, its low-order half in `objid`, and `objsubid` set to 1. You can reassemble the key with `(classid::bigint << 32) | objid::bigint` (I confirmed this round-trips negative keys correctly too). ```sql -- Advisory locks currently held or awaited, with what each session is doing 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; ``` Rows with `granted = false` are waiting sessions. In operation, watch: - **The number of waiting advisory locks and the longest wait**: if it keeps growing, the locked section is too long or the key is too coarse. - **Lock holders with a long `xact_age`**: a sign that an external call has crept into the locked section. - **The rate of `LockBusy` (SQLSTATE `55P03`)**: emit it as an application metric. A spike means contention on one key or a stuck holder. --- ## 12. When not to use it - **The invariant can be expressed as a unique, CHECK or exclusion constraint**: use the constraint (section 5). - **The locked section would include an external API call or waiting for a user**: use an outbox and a lease (section 7). - **You need mutual exclusion across databases or services**: advisory locks are local to each database. They can't exclude a process connected to another database. - **You want "I hold the lock" to prove ownership**: if the connection drops, the lock vanishes and the original work doesn't know. Use a fencing token. - **You'd lock a large number of keys in one transaction**: you'll hit the shared-memory limit (section 3.4). --- ## Summary - Under READ COMMITTED, check-then-insert lets concurrent requests both pass (TOCTOU). - `FOR UPDATE` only locks existing rows, so it can't protect a check for a row that doesn't exist yet. - Advisory locks lock application-defined keys. In practice use `pg_advisory_xact_lock` and bound the wait with `lock_timeout`. - Session-level locks survive ROLLBACK, don't work in PgBouncer's transaction mode, and cause pinning in RDS Proxy. - Hash keys to 64 bits with a namespace. A collision causes false contention, never a breach of mutual exclusion. - Put invariants a constraint can express into a constraint, use advisory locks for the rest, and keep external API calls out of the locked section. If you need exclusion that outlasts COMMIT (ownership for workers that call external APIs), continue with [leases and fencing tokens in an outbox dispatcher](/blog/outbox-dispatcher-skip-locked-lease-fencing-token-guide). The bigger picture of MVCC and row locks is in the [PostgreSQL MVCC and transaction isolation guide](/blog/postgresql-mvcc-transaction-isolation-vacuum-autovacuum-guide), and other transaction-mode caveats are in [PostgreSQL connection pooling in practice](/blog/postgresql-connection-pooling-pgbouncer-serverless-guide).