Skip to content

ADR-045: Hot-path triggers never wait on another transaction

Status

Accepted. Tracked in #492. Implemented by migration v046 (awa-model/migrations/v046_wait_free_dirty_marks.sql), the 0.6 schema patch wait_free_dirty_marks, the hot_path_triggers_cannot_wait_on_other_transactions test in awa/tests/dirty_marks_wait_free_test.rs, and the AwaCanonicalDirtyMarks TLA+ model.

Context

Every INSERT, UPDATE, or DELETE on awa.jobs_hot and awa.scheduled_jobs fires triggers, so whatever a trigger does executes inside every job transition: enqueue, claim, heartbeat, completion, re-schedule, rescue, and every application transaction that enqueues through awa.jobs. Since v006 the admin-metadata triggers recorded "this queue / kind needs its cached counts recomputed" with

INSERT INTO awa.admin_dirty_queues (queue) ... ON CONFLICT (queue) DO NOTHING

into tables keyed by queue and kind. ON CONFLICT DO NOTHING is free only when the keyed row is committed. Against a row another transaction has inserted or deleted and not yet committed, Postgres waits for that transaction. Two maintenance paths made that wait common: the 2 s drain deleted the keyed rows inside its recompute transaction, and the 60 s full refresh TRUNCATEd both tables under ACCESS EXCLUSIVE for its whole run.

A production cluster showed the consequence on 2026-09-12. The refresh held every job transition for about three seconds each minute; when it committed the tables were empty and the queued transitions stampeded onto them. A re-schedule CTE fires two statement triggers (the jobs_hot DELETE trigger, then the scheduled_jobs INSERT trigger), each marking queue then kind, while a claim UPDATE marks only the queue. With a drain landing between one re-schedule's two triggers, two re-schedules in the same queue acquired the queue row and the kind row in opposite orders and deadlocked. The victim's completion was only logged, and its job sat running until heartbeat rescue two minutes later. The cluster recorded 37 such deadlocks in a week and roughly 3,000 transitions a day waiting more than a second behind the refresh.

The v006 mitigation (sorted inserts, ON CONFLICT DO NOTHING) addressed lock ordering within one statement. The defect is that the trigger could wait at all: a hot-path transaction that waits on another while holding its own locks is a deadlock edge, and this edge joined transactions that shared nothing but a queue name. No amount of ordering removes an edge whose direction depends on what a concurrent drain deleted a millisecond earlier.

Decision

A trigger on a canonical hot table may not perform any operation that can wait on another transaction, except for the one case where waiting expresses the feature itself.

  1. Shared bookkeeping written from triggers is append-only and unconstrained. The dirty marks live in awa.admin_dirty_queue_marks and awa.admin_dirty_kind_marks, which carry no index and no constraint. The trigger INSERT is a plain heap insert. Nothing on the hot path updates or deletes a mark. Duplicate marks are collapsed by DISTINCT on read.
  2. The maintenance reader never takes a lock that conflicts with the hot path. The drain selects the distinct keys it can see, deletes exactly those visible rows, then recounts under READ COMMITTED statement snapshots; a mark from a transaction that commits later stays for the next drain, so the cached counts remain exact. The full refresh deletes visible marks instead of truncating. TRUNCATE is not used on any table a hot-path trigger writes.
  3. The one permitted wait is the unique claim. awa.sync_job_unique_claims inserts into awa.job_unique_claims, whose unique index is the uniqueness feature. A wait there is a genuine duplicate racing a transition, and the transaction that loses gets the documented unique-violation outcome. It writes only the transitioning job's own claim rows.
  4. The contract is executable. hot_path_triggers_cannot_wait_on_other_transactions enumerates every non-internal trigger on jobs_hot and scheduled_jobs, extracts each body's write targets, and fails on any target outside the allowlist, on ON CONFLICT, TRUNCATE, LOCK, or any row lock (FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE) in a trigger body, on UPDATE/DELETE outside the claim trigger, on any index or constraint on a mark table, and on TRUNCATE in the maintenance functions. AwaCanonicalDirtyMarks keeps the v006 cycle, including the production interleaving, as an expected counterexample and proves the wait-free plans never block.
  5. A deadlock abort is retried, not surfaced. Every finalize path is one lease-guarded transaction, so the executor re-runs a transaction Postgres aborted with SQLSTATE 40P01 (up to three retries after the initial attempt, 10/20/40 ms backoff) and counts it in awa.completion.deadlock_retry. A non-zero rate of that metric means a new wait has entered the hot path.

Adding a trigger that writes anywhere else, adding a constraint to a mark table, or re-introducing a keyed upsert on the hot path requires a new ADR that explains why the wait cannot form a cycle.

Consequences

  • One small heap insert per DML statement on the canonical tables replaces a keyed upsert that was a read in the steady state and a wait after every drain. Marks accumulate between drains and are removed by every drain and refresh; while a maintenance leader is alive the tables stay a few rows long.
  • The runtime role no longer needs TRUNCATE for the canonical maintenance path; queue-storage segment pruning still uses it on partitions.
  • The old admin_dirty_queues / admin_dirty_kinds tables remain, unread, until a later contract migration removes them under ADR-041.
  • The 0.6 series cannot add migration versions, so the same SQL ships there as an idempotent, versionless schema patch applied at v040 and included in the SQL export for external runners.

Alternatives considered

  • Sorted acquisition or a per-queue advisory lock in the trigger. Ordering does not remove the edge; a drain between two triggers of one statement still reverses it. An advisory lock per queue would serialise every transition in a queue behind the slowest one and still leave the refresh's TRUNCATE stall.
  • Drain in a separate short transaction, keep ON CONFLICT. Shortens one window but keeps the first-inserter-after-drain wait and the stampede after every refresh.
  • pg_notify instead of mark rows. Wait-free and durable enough given the reconciliation backstop, but NOTIFY serialises briefly at commit under the async queue lock, and a leader that is not listening loses the signal for up to a full refresh interval. Append-only rows give the same wait-freedom with exact counts.
  • Drop the cached counts and compute on demand. Exactness without maintenance, but the admin UI and the queue-health metrics read those counts on every tick.