Quick tip: one Postgres advisory lock stops your scheduled job running twice

#postgres #cron #backend #devops

You scaled your app from one server to two. Everything got faster, except that the nightly invoice job now runs twice, and some customers got two invoices.

It's one of the most common surprises in growing a backend: scheduled code that was safe on one machine isn't safe on several. The heavyweight fixes are a dedicated scheduler service, a leader election library, or a distributed lock in Redis or ZooKeeper. But if you already run PostgreSQL, there's a simpler tool sitting right there: advisory locks.

The tip

Wrap the job in a non-blocking advisory lock. Whoever gets the lock runs the job; everyone else skips it.

SELECT pg_try_advisory_lock(42);

pg_try_advisory_lock returns true if this session acquired the lock and false immediately if another session holds it. It never waits. The number (42 here) is an application-chosen key: Postgres doesn't care what it means, only that everyone uses the same one for the same job.

In application code (Python with psycopg here, but the idea is identical in any language):

import psycopg

INVOICE_JOB_LOCK = 7_310_001  # any bigint, unique per job

def run_nightly_invoices(conn):
    with conn.cursor() as cur:
        cur.execute("SELECT pg_try_advisory_lock(%s)", (INVOICE_JOB_LOCK,))
        got_lock = cur.fetchone()[0]
    if not got_lock:
        print("another instance is running invoices; skipping")
        return
    try:
        generate_invoices(conn)
    finally:
        with conn.cursor() as cur:
            cur.execute("SELECT pg_advisory_unlock(%s)", (INVOICE_JOB_LOCK,))

Every server can keep its cron entry or in-process scheduler unchanged. They all wake up, they all ask, one wins.

Why this works

Advisory locks are managed by Postgres's normal lock manager, but they don't lock any table or row. They're just named locks your application agrees to respect. Two properties make them a good fit for scheduled jobs:

They're atomic. Two sessions can't both get the same lock, even if they ask at the exact same millisecond.
Session-level locks are released automatically if the session ends. If the server running the job crashes and its database connection drops, the lock goes with it. You don't end up with a stale lock that blocks the job forever, which is a classic problem with "lock" rows in a table.

Gotchas worth knowing

1. Hold the lock on the same connection that took it. Session-level advisory locks belong to a database session. If your code takes the lock on one pooled connection and then returns that connection to the pool, the lock stays held by whatever session the connection represents, and your unlock may run on a different connection and do nothing. Take a dedicated connection for the duration of the job.

2. Be careful with transaction-mode connection poolers. If you use PgBouncer in transaction pooling mode, consecutive statements may run on different server sessions, so session-level locks don't behave as you'd expect. Options: connect directly to Postgres for the job, use session pooling for that connection, or use the transaction-level variant below.

3. Consider the transaction-level variant. pg_try_advisory_xact_lock(key) holds the lock until the current transaction ends, then releases it automatically, with no explicit unlock. That's neat when the whole job fits in one transaction, but a long-running transaction has its own costs (it can hold back vacuum and keep locks on rows it touched), so it's not always the right choice for big batch jobs.

4. This prevents overlap, not duplicates over time. The lock guarantees two instances don't run at the same time. It doesn't stop the job running twice in a row: if server A finishes at 02:00:03 and server B's scheduler fires late at 02:00:05, B will get the lock and run again. For jobs that must run exactly once per period, combine the lock with a record of what's been done, for example:

INSERT INTO job_runs (job, period) VALUES ('invoices', '2026-11-03')
ON CONFLICT DO NOTHING
RETURNING 1;

If no row comes back, this period is already done; skip it. With a unique constraint on (job, period), this is safe even without the lock, and with it, you also avoid wasted parallel work.

5. Choose keys deliberately. Keys are a single 64-bit integer or two 32-bit integers. Keep a small registry in code (constants with clear names) so two different jobs don't accidentally share a key. Some teams derive keys by hashing the job name, which works, as long as everyone uses the same hash.

6. Make the skip visible. Log when an instance skips because another holds the lock. If every instance logs "skipping" and the job never runs, something is holding the lock that shouldn't be, and you can find it in pg_locks:

SELECT pid, granted, objid
FROM pg_locks
WHERE locktype = 'advisory';

When to reach for something else

Advisory locks are a great fit when you have a handful of app servers, one Postgres primary, and jobs that run on a schedule. They're less suitable when:

Your jobs run across several databases or regions with no single shared primary.
You need queues, retries, delays and visibility into each run, not just mutual exclusion.
The job needs to keep running through a database failover. A lock held on the old primary doesn't carry over.

At that point you're building a job system, and it may be time for one, whether that's a library backed by your database or a hosted scheduler like delay.run that runs each job once at the time you asked.

Summary

pg_try_advisory_lock(key): skip if someone else is running.
Use a dedicated connection, and watch out for transaction-mode poolers.
Add a job_runs table with a unique constraint if "once per period" matters.
Log skips so you notice when nothing runs at all.

Four lines of SQL, no new infrastructure, and no more double invoices.

DEV Community