One connection took the lock, another tried to release it

Orbitly runs scheduled jobs: reminders, the evening "did you log your spending?" nudge, weekly summaries. A tick arrives every minute, the job works out whose turn it is, and sends.

One day the app will run as two instances — during a zero-downtime deploy, at the very least. The tick reaches both, both dutifully send the reminders, and someone gets the same message twice.

The standard guard against that is a Postgres advisory lock. Whoever takes it first does the work; the other gets a refusal and quietly skips the tick. This was written in April and looked like so:

const lockResult = await db.execute(
  sql`SELECT pg_try_advisory_lock(hashtextextended(${name}, 0)) as locked`
);
// ... work ...
await db.execute(sql`SELECT pg_advisory_unlock(hashtextextended(${name}, 0))`);

Looks harmless. The trouble is that db.execute takes a connection from the pool and hands it straight back. And pg_try_advisory_lock is a session-level lock: it belongs to a specific connection, not to the application. One connection hung the lock; a different one came to take it down.

What it looks like in numbers

Checking turned out to be easy — just ask the database who is who:

took the lock      → pg_backend_pid = 76783
released the lock  → pg_backend_pid = 76784
pg_advisory_unlock returned false
locks left hanging: 1

That false is Postgres politely saying "you don't hold such a lock". We never checked the return value, so it bothered nobody for four months.

Why nobody noticed

The lock did hang around, but not for long. Pooled connections close after twenty seconds idle, the session ends with them, and Postgres releases the lock on its own. The next tick arrives sixty seconds later — by then everything is clean.

So the guard worked. Just not because we wrote it, but because the timeout got there first.

It turned dangerous the moment I rewrote the mailings to process in batches. The pool got busier, and a connection may well not sit idle for its twenty seconds. Then the lock outlives the tick, pg_try_advisory_lock returns false — and the job is skipped. Silently. Nothing in the logs: a refused lock is indistinguishable from an honest "another instance is already on it".

The worst kind of failure. Not a crash — a silence.

The fix

Reserve a connection outright and hold it until the lock is released:

const connection = await reserveCronConnection();
try {
  const [row] =
    await connection`SELECT pg_try_advisory_lock(hashtextextended(${name}, 0)) as locked`;
  if (row?.locked !== true) return false;
  try {
    return await fn();
  } finally {
    await connection`SELECT pg_advisory_unlock(hashtextextended(${name}, 0))`;
  }
} finally {
  connection.release();
}

The background-job pool now holds five connections: four handlers and one that keeps the lock. It stays occupied for the whole run and does nothing else — that is precisely the point.

What I take from it

The bug survived four months not because it was clever. It survived because it did no harm: Orbitly has no users yet, there is one instance, and the timeout tidied up after us. Tests don't catch this — as far as a test is concerned, everything ran. Logs stay quiet — there was no error.

It surfaced as a side effect. I was splitting the connection pools — the site on one, background jobs on another, so a mailing can't eat the connections live requests need — and along the way had to ask which connection the lock actually lives on.

One last detail. The previous post on this blog went up on 4 May. The lock broke on 25 April. All the time it was quiet here, it was sitting in the code.