PostgreSQL Advisory Locks in Practice: Serializing the Check-Then-Insert Race That FOR UPDATE Can't Stop

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. 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:

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:

# ❌ 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:

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:

# ❌ 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 integers. 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

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.

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).

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.

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.

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:

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.

# ❌ 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). 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.

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:<user_id>" 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.

# 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:

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.

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).

-- 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. The bigger picture of MVCC and row locks is in the PostgreSQL MVCC and transaction isolation guide, and other transaction-mode caveats are in PostgreSQL connection pooling in practice.