Skip to content

pg_advisory_lock is the first thing you reach for when a scheduled job overlaps itself. Behind Supabase, PgBouncer or a serverless pool it quietly protects nothing. What advisory locks actually guarantee, the three ways they fail silently, a TTL lock for HTTP-only databases, and the conditional claim that makes double work impossible.

Published
Reading
11 min
Author
Yaroslav Tsarenko
–views
Two glowing lines run at the same job; one passes through a narrow gate, the other is turned back

The log said the run started at 14:00. It also said the run started at 14:00. Two lines, same minute, two different process ids, and somewhere out in the world one person got the same email from me twice.

The fix looked obvious. Postgres has advisory locks; take one at the top of the job, release it at the end, done. That is the advice you will find in every thread about a cron job that overlaps itself, and in a lot of setups it is quietly wrong. Not "suboptimal" wrong. "The function returns true, the lock does nothing, and you believe you are protected" wrong.

This is what advisory locks actually promise, the three ways they fail without telling you, what to do when your database only speaks HTTP, and the one pattern that makes duplicate work impossible rather than unlikely.

What an advisory lock actually is

Everything Postgres normally locks is a thing: a row, a table, an index. An advisory lock is a number. You pick a 64-bit key (or two 32-bit ones), you ask for it, and Postgres hands it to exactly one caller at a time. That is the entire feature.

What it does not do is just as important:

  • It locks nothing. No row becomes unwritable. No table becomes unreadable.
  • It binds nobody. Code that never asks for the key carries on regardless. The lock is a convention between the processes that agreed to use it.
  • It has no memory. Nothing records why the key was taken or by whom, unless you write that somewhere yourself.

It is a meeting room key on a hook by the door. Taking the key means nobody else takes it. It does not stop anyone walking in through the other door.

There are two scopes, and the difference between them is where most of the trouble lives.

sql
-- session scope: lives until you unlock it or the session ends
select pg_advisory_lock(42);          -- waits, possibly forever
select pg_try_advisory_lock(42);      -- true or false, immediately
select pg_advisory_unlock(42);

-- transaction scope: released on commit or rollback, no unlock call exists
select pg_advisory_xact_lock(42);
select pg_try_advisory_xact_lock(42);

Session locks survive commits and rollbacks. They stack, too: ask three times, unlock three times, or the key stays on the hook. Transaction locks disappear the moment the transaction ends, whichever way it ends. You can see every one of them in pg_locks with locktype = 'advisory', which is worth knowing at three in the morning.

Failure one: the pooler eats your session

Here is the pattern that looks correct and protects nothing:

ts
await sql`select pg_advisory_lock(42)`;
await doTheWork();
await sql`select pg_advisory_unlock(42)`;

Three statements, three trips to the database, and nothing anywhere says they must run on the same connection. Behind a connection pool they usually do not.

  • PgBouncer in transaction mode hands out a server connection per transaction. Your lock goes out on one backend and your unlock on another, where there is nothing to unlock. The first connection goes back into the pool still holding key 42, and the next tenant of that connection inherits a lock they never asked for.
  • Supabase over PostgREST is worse, and clearer. Every call is an HTTP request. There is no session you can hold across two of them, by design. select pg_advisory_lock(42) succeeds and is released before your next line of JavaScript runs.
  • Serverless functions multiply the problem by however many instances the platform decided to start.

I wrote about the same trap from the other side in PostgreSQL query optimization in Node.js: in transaction mode, session state breaks in general. SET without LOCAL, LISTEN, session advisory locks. They all assume a session that is yours, and a pooler's whole job is that it is not.

One job, three statements, three backendsa session lock behind PgBouncer in transaction modeYour jobconnection poolpg_advisory_lock(42)backend #1takes key 42, goes back to the pool with itdo the workbackend #2runs the work with no lock at allpg_advisory_unlock(42)backend #3unlocks a key it never held: falseThe call succeeded, the lock protected nothing, and #1 is still holding it

The rule is short: a session-level advisory lock is only real on a connection you hold yourself, from acquire to release. A direct connection string, a dedicated worker, a long-lived process. If you cannot point at that connection, you do not have the lock.

Failure two: waiting forever is not a strategy

pg_advisory_lock blocks. No timeout, no limit on how many callers queue up behind it. On a job that runs every ten minutes and sometimes takes eleven, you do not get one skipped run; you get a queue of waiting processes, each holding a connection, each perfectly willing to wait until the pool runs out.

The scheduled version of a job almost never wants to wait. It wants to find out it is late and go home:

ts
const [{ ok }] = await sql`select pg_try_advisory_xact_lock(42) as ok`;
if (!ok) return { skipped: "another run holds the lock" };

pg_try_advisory_xact_lock is the one I reach for by default. It answers immediately, and the lock is gone the moment the transaction ends — on success, on error, on the process being killed mid-flight. There is nothing left to leak and no unlock call to forget in a finally you never wrote. If you genuinely need to wait, set lock_timeout first so the wait has an end.

Failure three: the lock was never the guarantee

This one is the real lesson, and it took a duplicate email to teach me.

Say the lock works perfectly. One run, no overlap. Now the process dies halfway: a deploy, an OOM kill, a platform draining the instance. The transaction lock is released instantly and correctly, and the next run picks up a message that was already handed to the mail server but never marked as sent. Nothing overlapped. The email still went out twice.

A lock coordinates. It does not remember. The thing that makes work happen once is the record of the work, not the lock around it.

What to do when the database only speaks HTTP

My own cold-outreach engine runs as a single scheduled job once an hour: collect, process, audit, inbox, send, report, each step on its own time box so a slow step cannot eat the ones behind it. Its data lives in Supabase and every call is an HTTP request, so the advisory-lock route is closed before it starts.

So the lock is a row with an owner and an expiry:

ts
type Lock = { owner: string; at: string; until: string };
const owner = `${process.env.RENDER_SERVICE_NAME ?? "local"}-${process.pid}-${rand()}`;

const held = await getSetting<Lock | false>("cycle_lock", false);
if (held && Date.parse(held.until) > Date.now()) return { skipped: "locked" };

await setSetting("cycle_lock", { owner, at: iso(now), until: iso(now + TTL) });
await sleep(400);
// two runs that started together both wrote: last writer wins, the other backs off
const now = await getSetting<Lock | false>("cycle_lock", false);
if (!now || now.owner !== owner) return { skipped: "lost the race" };

Three properties make this survivable. The TTL (twenty minutes, comfortably longer than the twelve-minute budget) means a crashed run cannot wedge the job forever. The owner string makes releasing safe: a run only clears the lock if it still belongs to it, so a slow run that already expired cannot delete a newer run's lock. And the re-read after a short pause turns "we both wrote" into "one of us notices and leaves".

Now the honest part, because this is exactly where people fool themselves. Read, then write, then read again is not mutual exclusion. It is a race with a smaller window. Two runs can land inside those 400 milliseconds and both conclude they own the key. The TTL row is there to avoid duplicated effort and a duplicated report in my Telegram. It is not what keeps an email from going out twice.

Read, write, read again is still a racetwo runs of the same job, milliseconds apartboth believe the lock is freeRun Areads: freewrites: minere-readsRun Breads: freewrites: minere-readsA re-reads after B wrote, sees B as the owner and backs off.B re-reads last and sees itself: one run proceeds. Shift the timing and both do.A narrower window is not a closed one

If you want a lock row with no race, it has to be one statement, and the database decides the winner:

sql
insert into job_locks (key, owner, until)
values ('cycle', $1, now() + interval '20 minutes')
on conflict (key) do update
  set owner = excluded.owner, until = excluded.until
  where job_locks.until < now()
returning owner;

One round trip. The where on the do update is the whole trick: the row is only taken over if the previous holder's lease has expired. Compare the returned owner to yours — if it does not match, somebody else has it. That works over PostgREST, over any pooler, from any number of regions, because the atomicity lives in Postgres instead of in your control flow.

The pattern that actually makes it once

Whatever coordinates the runs, correctness comes from a conditional state change. Do not read a row and then act on it. Claim it, and let the number of rows returned tell you whether the work is yours:

sql
update messages
   set status = 'sending', claimed_at = now()
 where id = $1
   and status = 'approved'
returning id;

Zero rows back means another worker claimed it first, or a human cancelled it, or it already went out. One row back means it is yours and nobody else can take it, because status = 'approved' is now false for everyone else. Same shape for a queue, taking several at once:

sql
update messages set status = 'sending'
 where id in (
   select id from messages
    where status = 'approved' and scheduled_at <= now()
    order by scheduled_at
    limit 5
    for update skip locked
 )
returning *;

FOR UPDATE SKIP LOCKED is the part that makes this scale: concurrent workers step over rows already being claimed instead of queueing behind them.

Then make the write-back provable. Store the mail provider's message id on the row when the send succeeds, and let a unique index on the business key — recipient plus campaign step, say — turn a double send into a database error you can see rather than an email your prospect sees. This is the same idempotency discipline I went through in detail in the Node.js job queues guide: at-least-once delivery plus an idempotent handler is what adds up to the effect of exactly once.

Session lock

  • pg_advisory_locksession scope, waits forever by default
  • Dies behind a poolerPostgREST, PgBouncer transaction mode, serverless

Transaction lock

  • pg_try_advisory_xact_lockreturns true or false immediately
  • Released on commitnothing to leak, nothing to unlock

TTL row

  • Lock row with a TTLworks over HTTP, expires on its own
  • Read then write racesbest-effort dedupe, not exclusion

Claim

  • Conditional claimUPDATE … WHERE status = previous
  • Atomic by definitionone row returned, one worker proceeds
Four tools people call a lock, and what each one gives you

How the two layers fit together

With the claim in place, the lock changes job. It is no longer load-bearing; it is an optimisation that stops two runs doing the same twelve minutes of work and sending me two identical reports. If it fails occasionally, I lose some CPU time. If the claim fails, somebody gets emailed twice. Knowing which is which is most of the skill.

So the engine stacks them, and keeps a few rules that have nothing to do with locking at all:

  • nothing sends outside a window — weekdays only, afternoon into evening in the recipient's working hours;
  • a rolling 24-hour limit, not a calendar day, because a calendar day resets at midnight and lets a run at 23:59 and one at 00:01 double up legitimately;
  • a bounce and complaint guard that pauses everything when the recent bounce rate goes above its threshold, with the thresholds tuned so a single bounce during warm-up does not stop the queue;
  • sending only ever picks up messages a human approved, so the worst a bug can do is send something already written and read.

Each of those is a smaller blast radius if the one above it fails. That is the actual design principle here: not "find the correct lock" but "assume every layer fails and ask what the next one catches".

Job startscron, retry or a second regionTry the lockxact lock or TTL rowClaim the messageapproved -> sending, returning idDo the workthe actual sendRecord the resultsent, with the provider's idExit quietlysomeone else owns this rungot itbusy1 row0 rows
A scheduled send that cannot send twice

The same thinking applies well outside email. The Parandis Bud store syncs stock from a warehouse system on a schedule, and a sync that runs twice is not a cosmetic problem — it is prices and stock levels that disagree with the warehouse while a customer is mid-checkout.

A short decision list

Situation What to use
Direct connection, work fits one transaction pg_try_advisory_xact_lock
Direct connection, work spans transactions session lock on a connection you hold, released in finally
PgBouncer transaction mode, PostgREST, serverless lock row claimed with a single conditional insert … on conflict … where
Several workers pulling from a queue for update skip locked plus a status claim
Anything that touches money, email or another system a conditional claim and a unique key, regardless of the lock

And the five-minute check on a job you already run:

  1. Is the lock acquired and released on the same connection? If you cannot prove it, it is not a lock.
  2. Does a late run skip rather than wait? try variants, or lock_timeout.
  3. If the process is killed mid-run, does the lock expire on its own?
  4. Does the work itself have a conditional claim, so a second run finds nothing to do?
  5. Is there a unique key that turns a duplicate into an error you can see?

Three or more uncertain answers and the job is not protected — it has just been lucky so far. Mine was lucky for weeks before it was not.

The short version

  • An advisory lock is a number Postgres hands to one caller at a time. It locks no data and binds no code that does not ask for it.
  • Session-level locks need a connection you hold from acquire to release. Behind PgBouncer's transaction mode, PostgREST or a serverless pool, that connection does not exist and the lock silently protects nothing.
  • Prefer pg_try_advisory_xact_lock: it answers immediately and is released by the end of the transaction, however the transaction ends.
  • When the database is only reachable over HTTP, a lock row works — but claim it in one conditional statement, not read-then-write, or you have only made the race narrower.
  • Locks prevent wasted work. Conditional claims and unique keys prevent wrong work. Only the second kind is a guarantee.

If you have a scheduled job you are not quite sure about — one that sends, charges or syncs — I am happy to look at it. Usually it takes one read of the claim query to know whether it can run twice.

Was this useful?

– likes · – views

Need this for your product?Next.js and Node.js development, from schema to screenLearn more →

Questions people ask

What is a Postgres advisory lock?

A named lock your application takes out by number instead of by row or table. Postgres hands it to one caller at a time and attaches no meaning to it: nothing in the database is locked, and nothing stops code that never asks for the lock. It exists so two processes can agree who does a piece of work.

Why doesn't pg_advisory_lock work with Supabase or PgBouncer?

Session-level advisory locks live on one database session. PostgREST, PgBouncer in transaction mode and most serverless pools give you a different session per request or per transaction, so the lock is taken on one connection and the unlock lands on another. The lock either disappears immediately or leaks into a pooled connection and is held by the next unrelated query.

What is the difference between pg_advisory_lock and pg_advisory_xact_lock?

The session version is held until you call pg_advisory_unlock or the session ends, and it survives commits and rollbacks. The transaction version is released automatically at the end of the transaction and cannot be unlocked by hand. If your work fits in one transaction, the xact variant is almost always the safer choice.

How do I stop a scheduled job from running twice?

Two layers. A lock to avoid wasted work: pg_try_advisory_xact_lock on a direct connection, or a lock row with a TTL if you only reach the database over HTTP. And a conditional claim for correctness: update the row to a 'claimed' state only where it is still in the previous state, and act only if the update returned a row.

Is a lock row in a table as good as an advisory lock?

No. A read-then-write lock row has a race window between the read and the write, so two runs can both believe they own it. It is a best-effort way to avoid duplicated work, not mutual exclusion. Either close the race with a single conditional statement, or treat the lock as an optimisation and put the real guarantee in the claim.