Postgres Advisory Locks Quietly Fixed Our Double-Running Cron Jobs

For about a month last year, a small number of our business customers received their monthly invoice email twice. Same invoice, same amount, sent a few seconds apart. Nobody was charged twice, thankfully, but our support team got 37 confused emails, and one customer asked if we had been hacked.

The cause turned out to be simple and the fix turned out to be smaller than the investigation.

How two servers ran one job

Our invoicing job ran from a scheduler inside our application. When we had one application server, this was fine. Then we scaled up to two servers for a traffic spike and never scaled back down. Both servers now had a scheduler. Both servers ran the job at 02:00.

Most nights, it did not matter. The job marked each invoice as sent in the database, and the second server would find nothing left to send. But on nights when the job took a few seconds to start, both servers read the same unsent invoices before either one marked them. Classic race.

Options we considered

We talked through a few approaches.

• Only run the scheduler on one server. Simple, but fragile. If that server is down at 02:00, nothing runs.
• Use an external job queue. Better long term, but a big change for one job.
• Use a lock table. Insert a row before running, delete it after. Works, but if a server crashes mid-job, the row stays and you need cleanup logic.
• Use a Postgres advisory lock. We already had Postgres. Advisory locks are built in and are released automatically when the connection closes.

We went with the advisory lock.

The actual change

An advisory lock is a lock on an arbitrary number that your application chooses. Postgres does not know or care what it means. You call pg_try_advisory_lock(42) and it returns true if you got the lock or false if someone else holds it. If your process dies, the connection closes and the lock disappears.

Our change wrapped the job in this logic: try the lock with a fixed number reserved for invoicing; if it returns false, log a line and exit; otherwise run the job and release the lock at the end. Eleven lines including logging.

We picked lock numbers by hashing the job name, so each job has its own lock without a central registry.

A few things to watch for

There are some sharp edges. Advisory locks are tied to a session, so if you use a connection pooler in transaction mode, the lock can end up on a connection that gets handed to someone else. We use session-level pooling for our job runner for exactly this reason.

It also only protects you while the job runs. If one server finishes before the other starts, the second will happily run again. Our job was already idempotent at the row level, so that was fine, but it is worth checking.

Small tools are underrated

What I like about this fix is that it did not require new infrastructure. We already had everything we needed. We have since wrapped four other scheduled jobs the same way. Zero duplicate invoices in the eleven months since.