The Deadlock That Only Happened During the Monthly Invoice Run

A SaaS billing system locked up every invoice cycle. The cause was two transactions grabbing the same rows in opposite order — a textbook deadlock hiding behind a once-a-month batch job.

The support ticket said "invoice generation stuck again." It was the third month in a row.

My client ran a SaaS platform with around 4,000 active accounts. On the first of every month, a batch job ran through all accounts, calculated usage, and generated invoices. Most months it finished in about 45 minutes. Some months, it just stopped — no error, no crash, just a process sitting there doing nothing until someone killed it and restarted.

The team had developed a ritual. On the first of every month, whoever was on-call would babysit the job. If it stalled past the hour mark, they'd kill the process and re-run it. Usually the second attempt worked fine. Nobody had dug into why.

The "it usually works" trap

I was four weeks into an engagement focused on their data layer — query performance, schema cleanup, some index housekeeping. The invoice issue came up during a standup when someone mentioned "the monthly restart dance." I asked how long it had been happening.

Six months, give or take.

Six months of a manual workaround that nobody questioned because it cost fifteen minutes once a month. That's the thing about intermittent problems — the pain feels tolerable right up until it isn't. I asked to observe the next run. We were three days from month-end.

Finding the lock

When the job stalled on August 1st, I didn't kill it. Instead, I connected to PostgreSQL and went looking for lock contention.

SELECT
  blocked.pid     AS blocked_pid,
  blocked.query   AS blocked_query,
  blocking.pid    AS blocking_pid,
  blocking.query  AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity blocked  ON blocked.pid  = bl.pid
JOIN pg_locks     kl
  ON  kl.locktype = bl.locktype
  AND kl.relation = bl.relation
  AND kl.pid     != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

Two processes stared back at me. The batch invoice job held a lock on a row in subscriptions and was waiting for a lock on a row in usage_records. A user-facing API request held a lock on that same usage_records row and was waiting for the subscriptions row the batch job already had.

Textbook deadlock. But PostgreSQL's deadlock detector should abort one of them after deadlock_timeout (set to the default one second). So why were they stuck?

Because the batch job wasn't using row-level locks for its outer guard. It used advisory locks — pg_advisory_lock(account_id) — to prevent duplicate invoice generation. Reasonable idea. The problem is that PostgreSQL's deadlock detector doesn't monitor advisory locks. It only resolves cycles among regular row-level locks. These two processes were stuck forever, each waiting on the other with no referee.

How the code created the trap

The invoice batch job processed accounts in ID order. For each account, it:

1. Acquired an advisory lock on the account ID
2. Locked the subscriptions row with SELECT ... FOR UPDATE
3. Locked related usage_records rows
4. Calculated the invoice total, inserted the row, released everything

The API had an endpoint for customers to upgrade their subscription tier mid-cycle. That endpoint:

1. Locked the usage_records row to snapshot current usage
2. Locked the subscriptions row to update the tier
3. Committed

The batch job locks subscriptions first, then usage_records. The API locks them in the opposite order. If they touch the same account at the same time — deadlock. Most months the timing didn't line up. The batch job raced through accounts fast enough that the collision window was narrow. But with 4,000 accounts, once a month someone would upgrade their plan at the exact moment their invoice was being generated.

The fix

I changed the API endpoint to acquire locks in the same order as the batch job: subscriptions first, then usage_records.

# Before: usage_records first, then subscriptions
async def update_subscription_tier(account_id, new_tier):
    usage = await db.execute(
        "SELECT * FROM usage_records WHERE account_id = $1 FOR UPDATE",
        account_id
    )
    sub = await db.execute(
        "SELECT * FROM subscriptions WHERE account_id = $1 FOR UPDATE",
        account_id
    )
    # ... update logic
 
# After: match the batch job's lock order
async def update_subscription_tier(account_id, new_tier):
    sub = await db.execute(
        "SELECT * FROM subscriptions WHERE account_id = $1 FOR UPDATE",
        account_id
    )
    usage = await db.execute(
        "SELECT * FROM usage_records WHERE account_id = $1 FOR UPDATE",
        account_id
    )
    # ... update logic

Consistent lock ordering eliminates deadlocks entirely. It's on page one of every database textbook and page zero of nobody's code review checklist.

I also replaced the advisory locks in the batch job with SELECT ... FOR UPDATE SKIP LOCKED. Two wins from that change: PostgreSQL's deadlock detector would actually function as a safety net if we ever introduced another lock-ordering mistake, and SKIP LOCKED let the batch job skip accounts that were mid-update rather than blocking on them. Skipped accounts got picked up in a follow-up pass.

The September invoice run finished in 38 minutes with zero stalls. October, November, December — same. The monthly restart ritual quietly disappeared.

The pattern I keep seeing

Deadlocks aren't runtime bugs. They're design bugs that manifest at runtime. Both the batch job and the API endpoint worked flawlessly in isolation. It was the interaction between them — the unspoken ordering assumption baked into each — that created the trap.

I've hit this same pattern on three engagements now. The ingredients are always the same: a batch job that touches many rows, an API that touches some of those same rows, and nobody thinking about lock ordering across the two code paths. The collision window is tiny, which is why it survives testing and only appears under real production traffic with real timing.

What nags at me is the advisory lock detail. The team added those locks deliberately to prevent duplicate invoices. Good instinct, responsible engineering. But those locks happened to be invisible to the deadlock detector, which turned a recoverable conflict into an infinite hang. The safety mechanism made the failure mode worse. I've seen this before — in circuit breakers that mask errors, in retries that amplify load, in fallbacks that hide the real problem. The thing you add to make the system more robust becomes the thing that makes it harder to diagnose when it breaks.

How many of your batch jobs and API endpoints touch the same tables? Do they lock rows in the same order? I'd bet most teams have never checked.