audit_log Advisory Lock Plus UPDATE Order Opens a Deadlock

Conclusion

The advisory lock that serializes the tamper-detection hash chain into a single sequence is held until the transaction commits. audit_log‘s BEFORE INSERT trigger (migration 0003) takes pg_advisory_xact_lock(hashtext('audit_log')::bigint) to avoid races between concurrent INSERTs, and by design doesn’t release it until that transaction ends. Meanwhile, the portal delivery sync process (applyPartnerDelivery) was interleaving the audit record for ads leaving or returning to a placement’s delivery scope as “ads UPDATE → audit INSERT (takes advisory lock) → next placement’s ads UPDATE,” repeated per placement. When this ran concurrently with the operator screen’s ad pause action (which does the same shape: “ads UPDATE → audit INSERT”), and the two ended up requesting rows the other had already updated, a cycle formed: whichever held the advisory lock waited on the other’s row lock, while the other waited for the advisory lock to be released. PostgreSQL’s deadlock detector caught the cycle and force-killed one side with 40P01 (deadlock detected). The fix was to collapse the audit INSERT calls into a single batch, issued only after all the target rows’ UPDATEs are done.

Symptom

Reflecting advertising delivery changes sent from the portal (the school-facing sales support system) is handled by applyPartnerDelivery in partner-delivery.ts. When the set of schools a delivery targets changes, an ad row that drops out of scope is kept and marked with ads.retired_at rather than physically deleted (changed from a physical DELETE on 2026-09-14, to prevent a recurrence of incident K1, where deleted rows made past impression counts unrecoverable from the API), and an ad row returning to scope has its retired_at reset to null. Each of these “retired” / “revived” transitions gets written to audit_log as its own audit row.

// partner-delivery.ts (as of e0a9d22b, before the fix)
for (const [placementId, adIds] of deliveredAdIdsByPlacement) {
  const revived = await tx
    .update(ads)
    .set({ retiredAt: null, updatedAt: new Date(), updatedBy: null })
    .where(and(inArray(ads.id, adIds), isNotNull(ads.retiredAt)))
    .returning({ id: ads.id, schoolId: ads.schoolId });
  await writeScopeAudit(tx, revived, {
    portalPlacementId: placementId,
    change: "revive",
    retiredAt: null,
  });
}
// ...the "retire" step (4) has the same shape: writeScopeAudit called right after each UPDATE

With multiple delivery placements in scope, this UPDATE → audit INSERT pair repeats several times inside a single transaction. Running alone, this was never a problem — but pausing or resuming an ad from the operator screen runs the same UPDATE-then-audit-INSERT pair against the same ads table.

// operator-ads-actions.ts (as of e0a9d22b, before the fix)
await tx
  .update(ads)
  .set({ isPaused: paused, updatedBy: actorRef, updatedAt: new Date() })
  .where(and(eq(ads.id, adId), isNotNull(ads.advertiserId)));
await writeAudit(tx, {
  actor: user,
  schoolId: target.schoolId,
  recordId: adId,
  operation: "update",
  diff: { before: { isPaused: target.isPaused }, after: { isPaused: paused } },
});

A reviewer finding on PR #1385 reported that when portal delivery sync and an operator pause operation overlapped, one of the two transactions would occasionally abort with 40P01: deadlock detected.

Cause

audit_log’s BEFORE INSERT trigger uses an advisory lock keyed by a fixed value for the whole table, to keep the hash chain as a single sequence.

-- 0003_audit_trigger.sql (lines 69-107, unchanged)
-- Serialize the chain (table-wide advisory lock on audit_log)
PERFORM pg_advisory_xact_lock(hashtext('audit_log')::bigint);

pg_advisory_xact_lock is a transaction-scoped lock that is not automatically released until COMMIT or ROLLBACK. In other words, the instant a transaction INSERTs even one row into audit_log, it enters a state of “holding this fixed-key lock until commit.”

Layer on top of that the “ads UPDATE → audit INSERT” order that applyPartnerDelivery and setOperatorAdPausedAction shared before the fix. Consider two transactions T1 and T2 proceeding as follows:

• T1 (portal delivery, processing placements A then B): UPDATEs ads row A (takes row A’s lock) → audit INSERT (takes the advisory lock) → UPDATEs ads row B (requests row B’s lock, waits if T2 is already holding it)
• T2 (operator pause, targeting row B): UPDATEs ads row B (takes row B’s lock) → audit INSERT (requests the advisory lock, waits if T1 is already holding it)

T1 is left holding the advisory lock while waiting on row B’s lock; T2 is left holding row B’s lock while waiting on the advisory lock. Neither can release what it holds unless the other proceeds first, and PostgreSQL’s deadlock detector finds this cycle and aborts one side with 40P01.

What’s distinctive about this structure is that the advisory lock’s key, 'audit_log', is a single fixed value shared by the whole table. A row-level lock would only collide when the same row is touched, but an advisory lock is contended on the same key regardless of which row is involved — so as long as two or more pieces of code share the pattern “interleave an audit INSERT right after an UPDATE,” the moment their UPDATE/INSERT ordering crosses, the cycle above becomes possible even though each is touching different rows.

The fix

The fix (PR #1385, finding M1) changed applyPartnerDelivery’s audit writes to buffer until all UPDATEs for every target placement are done, then issue a single batch at the end.

// partner-delivery.ts (as of a49eba4b, after the fix)
// Buffer the audit entries for steps 3.5 / 4 here, and write them all together after every ads UPDATE is done
const scopeChanges: ScopeChange[] = [];

for (const [placementId, adIds] of deliveredAdIdsByPlacement) {
  const toRevive = await tx
    .select({ id: ads.id, schoolId: ads.schoolId, retiredAt: ads.retiredAt })
    .from(ads)
    .where(and(inArray(ads.id, adIds), isNotNull(ads.retiredAt)));
  if (toRevive.length === 0) continue;
  await tx.update(ads).set({ retiredAt: null, updatedAt: new Date(), updatedBy: null })
    .where(inArray(ads.id, toRevive.map((r) => r.id)));
  for (const r of toRevive) {
    scopeChanges.push({ adId: r.id, schoolId: r.schoolId, portalPlacementId: placementId, change: "revive", before: r.retiredAt, after: null });
  }
}
// ...step 4's "retire" similarly only does UPDATEs and pushes to scopeChanges

// 5. Write all audits together (the advisory lock is taken only once, at the end)
await writeScopeAudits(tx, scopeChanges);

The key point is that the call to writeScopeAudits (formerly writeScopeAudit) now happens exactly once, at the end, inside this transaction. By the time the transaction takes the advisory lock, it has already secured every row lock it needs. So the state of “holding the advisory lock while still waiting on a row lock not yet acquired” can no longer exist inside this function at all — which means the T1↔T2 cycle described above can no longer form.

// partner-delivery.ts (as of a49eba4b, doc comment on writeScopeAudits)
// Call this once, at the end, only after every ads UPDATE is finished.
// audit_log's BEFORE INSERT trigger (migration 0003) serializes the chain by taking
// pg_advisory_xact_lock(hashtext('audit_log')) and holding it until commit.
// Alternating "UPDATE ads → audit INSERT → UPDATE another ads row" creates an order
// where, after taking the advisory lock, the transaction goes on to request another
// row lock — which can deadlock when two portal delivery entry points run concurrently
// (reproduced on local PG16).
// Pushing the writes to the end means no tx is left waiting on a row lock after it has
// already taken the advisory lock, so the cycle can't be built.

Per this commit’s own record, a probe running portal delivery sync covering 2 placements concurrently with an operator pause operation reliably reproduced 40P01 before the fix, and after the fix the same probe ran 3 times at 360 operations each with zero occurrences. Note that setOperatorAdPausedAction itself was not changed in this PR — the “audit INSERT right after an UPDATE” shape still exists there. What this fix resolved was specifically the side where applyPartnerDelivery repeated that shape multiple times in a row across placements; a single standalone UPDATE → INSERT has no point afterward where the same transaction requests another row lock once it holds the advisory lock, so that pair alone can’t build the same cycle.

Preventing a repeat

The condition for this bug to occur was that an INSERT into audit_log (which requests one table-wide advisory lock) and an UPDATE on a target row (which requests a row lock) appeared interleaved two or more times within a single transaction. The doc comment on writeScopeAudits spells this condition out for the next person who touches this function, and the fix’s substance is preserved there as an ordering constraint on the function itself: “audit writes must be batched after every UPDATE that transaction performs.” Anyone adding new code that writes to audit_log — especially a design like this one that UPDATEs ads multiple times while interleaving audit writes — needs to assume that, because the advisory lock is a single fixed key for the whole table, it can collide with any other write path regardless of which rows are involved, and follow the same design of pushing the audit INSERT to the end of the transaction.