Postgres Advisory Locks Are Not the Lock You Think

At 03:40 my nightly reconcile job ran twice. Two workers, same pg_advisory_lock(42), both proceeded, and the refunds went out twice. The lock was real. The code was correct. It still did nothing, because each worker held a different Postgres backend behind PgBouncer in transaction mode.

pg_advisory_lock looks like a distributed lock. It is not one. It is a mutex owned by a database session.

What the lock actually is

A session-level advisory lock lives in the backend process that ran the statement. pg_advisory_lock(key) blocks until it gets the lock. pg_try_advisory_lock(key) returns true or false right away. The lock is held until pg_advisory_unlock(key) runs or that backend disconnects. It is reentrant: two calls on the same session need two unlocks, and a stray pg_advisory_unlock on a lock you do not hold returns false and logs a warning while changing nothing.

Now the failure modes, in the order I hit them.

The pool owns the session, not you. In transaction mode each transaction can land on a different backend. My worker acquired the lock in transaction one and released it in transaction two. The unlock ran on a backend that never held it, returned false, and I had been filtering that warning out of the logs for months. The original backend kept the lock for whichever client got it next. New workers blocked, or with the try variant, skipped their turn forever.

Disconnects release the lock. A failover, a restart, or a dropped TCP connection kills the backend and the lock with it. A worker that believes it holds the lock now does not, and nothing tells it.

No fencing token. Even with one stable connection, the lock says nothing about the write that happens after it. A worker can be paused by GC, a VM freeze, or a network partition past its lock's lifetime, wake up, and write anyway. The lock was never what stopped the second writer. Only the resource can do that.

What advisory locks are good for

Short mutual exclusion inside one database, inside one transaction. That is the honest scope.

Use pg_advisory_xact_lock or pg_try_advisory_xact_lock. They release at COMMIT or ROLLBACK, so a crashed client cannot leak them, and you write no unlock code at all.

BEGIN;
-- Non-blocking: false means another transaction is already doing this work.
SELECT pg_try_advisory_xact_lock(hashtext('nightly-reconcile')) AS got_it;
-- Application: if got_it is false, COMMIT and return.
UPDATE ledger SET reconciled = true WHERE reconciled = false;
COMMIT;

That pattern is fine for guarding a migration, serializing a trigger, or stopping two cron entries from running the same statement at once. It needs direct connections, or a pooler in session mode, and a transaction measured in seconds, not minutes.

What to use when you need a lease

If the work outlives a transaction, or crosses processes you do not control, put the lease in a table. A row with an expiry and a fencing token gives you something a session lock cannot: a value the resource itself can check.

CREATE TABLE job_lease (
  job_name   text PRIMARY KEY,
  owner      text        NOT NULL,
  fencing    bigint      NOT NULL DEFAULT 0,
  expires_at timestamptz NOT NULL
);

-- Claim. One atomic statement, no session state, safe behind any pooler.
INSERT INTO job_lease (job_name, owner, fencing, expires_at)
VALUES ('nightly-reconcile', :worker_id, 1, now() + interval '5 minutes')
ON CONFLICT (job_name) DO UPDATE
  SET owner      = EXCLUDED.owner,
      fencing    = job_lease.fencing + 1,
      expires_at = EXCLUDED.expires_at
WHERE job_lease.expires_at < now()
RETURNING fencing;
-- No row returned: a live lease exists, back off.
-- Row returned: you own the lease and the new fencing token.

The token only matters if the resource enforces it. Store the last token on the row you mutate and reject anything older:

UPDATE refunds
   SET processed_at = now(), last_fencing = :fencing
 WHERE id = :refund_id
   AND :fencing > last_fencing;

Zero rows updated means a stale worker tried to write. Log it and stop. Renew the lease with the same three-way check, and treat a zero-row renewal as an immediate stop:

UPDATE job_lease
   SET expires_at = now() + interval '5 minutes'
 WHERE job_name = 'nightly-reconcile'
   AND owner = :worker_id
   AND fencing = :fencing
RETURNING expires_at;

Set the expiry from data, not vibes. Log clock_timestamp() at the start and end of the critical section in production, read the high percentile after a week, and make the lease several times longer than that while renewing at a fraction of it. If a run can occasionally take much longer than usual, chunk the work and renew between chunks.

I still use pg_advisory_xact_lock. I use it for what it is: a lock for the length of one transaction, on a connection I control. Anything longer, or anything that has to survive a reconnect, gets a row and a token.

Comments

[Raknaos]

The pg_try_advisory_lock returning false and the caller filtering that warning out of the logs is the part that stings, because the lock did its job and the reporting layer deleted the only evidence. Your scope table is the honest version of this: a session-owned mutex is fine for a statement or two, useless as a claim marker.

The fencing token is what I always underestimate. Rejecting a write where fencing <= last_fencing turns a stale-worker race into one boring row count instead of a reconciliation script. How do you handle the token across tables a single job touches — one last_fencing column per row, or a separate lease-check statement inside the same transaction?