Advisory Locks for Run-Once Jobs

Scenario

Example app: a SaaS platform runs a nightly billing job at 02:00 that charges every tenant whose renewal date is today. The job is a scheduled task inside the web app — and the app runs on three instances.

What you see in production

Every tenant renewing on the 1st was charged three times. Each instance's scheduler fired at 02:00 and processed the same tenants.

The naive code

@scheduler.scheduled_job("cron", hour=2)
def nightly_billing():
    for tenant in Tenant.objects.filter(renews_on=date.today()):
        charge(tenant)

@Scheduled(cron = "0 0 2 * * *")            // runs on every instance
public void nightlyBilling() {
    tenants.findRenewingToday().forEach(billing::charge);
}

// app/Console/Kernel.php — scheduled on every server that runs `schedule:run`
$schedule->job(new NightlyBilling)->dailyAt('02:00');

Reproduce it

Three instances start the job at the same second. With a try-lock, only one proceeds:

advisory_locks = {}                         # key -> session holding it

def pg_try_advisory_lock(session, key):
    if key in advisory_locks:
        return False
    advisory_locks[key] = session
    return True

def pg_advisory_unlock(session, key):
    if advisory_locks.get(key) == session:
        del advisory_locks[key]

BILLING_LOCK = 4242                          # any agreed 64-bit number per job

for instance in ("web-1", "web-2", "web-3"):
    if pg_try_advisory_lock(instance, BILLING_LOCK):
        print(f"{instance}: got the lock -> billing 120 tenants")
    else:
        print(f"{instance}: another instance is billing -> skip")
pg_advisory_unlock("web-1", BILLING_LOCK)
print("after web-1 finishes, lock held by:", advisory_locks.get(BILLING_LOCK))

web-1: got the lock -> billing 120 tenants
web-2: another instance is billing -> skip
web-3: another instance is billing -> skip
after web-1 finishes, lock held by: None

Root cause

Every instance runs the same scheduler, and nothing coordinates them. Row locks don't help — there's no single row that represents "the billing job". Advisory locks are named locks managed by the database whose meaning is defined by your application: all instances agree that lock 4242 means "nightly billing is running".

The fix

-- session-level: held until unlocked or the connection closes
SELECT pg_try_advisory_lock(4242);      -- true for exactly one session
-- … do the work …
SELECT pg_advisory_unlock(4242);

-- transaction-level: released automatically at commit/rollback (safer)
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('billing:2026-10-13'));
COMMIT;

SELECT GET_LOCK('nightly_billing', 0);   -- 1 = acquired, 0 = held by someone else (timeout 0 s)
-- … do the work …
SELECT RELEASE_LOCK('nightly_billing');

with engine.connect() as conn:
    if not conn.scalar(text("SELECT pg_try_advisory_lock(:k)"), {"k": 4242}):
        return                                  # another instance has it
    try:
        run_billing()
    finally:
        conn.execute(text("SELECT pg_advisory_unlock(:k)"), {"k": 4242})

// ShedLock stores the lock in a table (or Redis) so only one instance runs the task
@Scheduled(cron = "0 0 2 * * *")
@SchedulerLock(name = "nightlyBilling", lockAtMostFor = "PT2H", lockAtLeastFor = "PT5M")
public void nightlyBilling() { ... }

$schedule->job(new NightlyBilling)->dailyAt('02:00')->onOneServer();   // uses an atomic cache lock

Even with the lock, make billing idempotent per tenant and period — a unique key on (tenant_id, period) in the invoices table — so a crash-and-rerun can't double-charge.

Trade-offs

• Session-level locks + poolers: behind PgBouncer in transaction mode, a session lock taken on one server connection may be "held" by a connection your next query doesn't use. Prefer transaction-level locks (pg_advisory_xact_lock) there, or run the job on a direct connection.
• If the holder's connection drops, the lock is released while the job may still be running on that instance → add per-item idempotency.
• Advisory locks only coordinate code that asks for them; they don't protect rows from other writers.
• For locks across services or databases, use a lease with fencing tokens (see Distributed Systems).

Detect it in production

• Postgres: SELECT * FROM pg_locks WHERE locktype = 'advisory'; shows holders.
• Log "acquired" and "skipped" per run on every instance; alert if more than one instance reports "acquired" for the same run, or none does.
• Invoices table invariant: no duplicate (tenant_id, period).

Key points

• Schedulers on every instance run the job on every instance.
• Advisory locks (pg_try_advisory_lock, MySQL GET_LOCK) let exactly one instance proceed.
• Prefer transaction-level locks behind poolers; know what happens on disconnect.
• Keep per-item idempotency as the safety net.