# Deep-Research Spike: Diagnose Campaign 009 S5 prestart PostgreSQL deadlock

Status: completed
Type: spike
Research Depth: deep
Updated: 2026-08-18
Next Action: none; retain as completed lock-order, deployment, reconciliation, and no-replay evidence for Campaign 009
Parent: work/00-campaigns/abandoned/009-complete-clean-contract-bound-a-b-qualification.md
Campaign: 009-complete-clean-contract-bound-a-b-qualification
Disposition: documented
Produces:

## Trigger

Campaign `a17fc306-8810-4e5a-bbc4-612adc47ce7c` reached Controller A S5 on
deployed application release `campaign009-layout-repair-a54fecb`
(`a54fecb83aa3c10d705f1c55ee143c26f0038333`, image
`sha256:1f8c0f36d2a2a8a337762243fcc3db3462bf63026303a496e58e1e65491e3624`).
At `2026-08-18T15:29:15Z`, the read-only BASELINE extended-runtime
observation deadlocked at PostgreSQL commit while checking the deferred
foreign key to `qualification_campaign`. The coordinator exhausted task
`f5df9729-e23b-470a-b790-038670f75b7b` at attempt `3/3` and blocked the A
lane. The admitted run was safely aborted as
`PRESTART_ABORTED_PREPARED_NO_TARGET_ACCESS`; its prepared Start operation was
changed to `BLOCKED`, the temporary A firewall rule was removed, and no target
command was published.

This follows two lock-related mitigations in the same release lineage:
`2d4d9d1` replaced Campaign-row event serialization with a PostgreSQL advisory
transaction lock, and `24bdcf7` removed the shared Campaign row lock from
supervisor snapshots. A third symptom-level lock change is prohibited until
the complete transaction and deferred-constraint interaction is understood.

## Decision or Uncertainty to Resolve

Identify the exact pair of transactions and lock-order cycle that produced the
S5 prestart deadlock, then decide the smallest correction that generalizes to
concurrent A/B coordinator, supervisor, event, Evidence, and Run writes. The
decision must establish whether the defect is caused by long-lived target
observation transactions, deferred foreign-key checks, parent/child lock
ordering, coordinator event publication, or a combination.

## Scope and Explicit Exclusions

In scope:

- Django transaction boundaries for HPT2 extended-runtime observation,
  coordinator task claim/heartbeat/completion, supervisor snapshots, and
  campaign event publication.
- PostgreSQL row, advisory, and deferred-foreign-key lock behavior for
  Campaign, Run, Evidence, Event, coordinator, and supervisor records.
- A deterministic concurrent PostgreSQL reproducer, focused regression tests,
  and an explicit no-deadlock/no-duplicate/no-replay acceptance gate.
- Read-only reconciliation of the failed S5 attempt and exact conditions for
  returning Campaign 009 to the `matrix.s5.a` gate.

Explicitly excluded:

- Retrying, replaying, or manually selecting an S5 case before the repair and
  campaign-resumption gates pass.
- Firmware, SD-card, CID, controller power, physical media, or USB changes.
- Weakening PostgreSQL isolation, disabling integrity constraints, swallowing
  deadlocks as success, or adding specimen-specific behavior.
- Advancing Controller B beyond its paused `matrix.s4.44640.after.b` gate.

## Timebox or Effort Boundary

Bound the spike to one deterministic PostgreSQL concurrency reproducer, an
exact lock graph or equivalent transaction-order proof, comparison with
`2d4d9d1` and `24bdcf7`, and one reviewed generalized correction proposal.
Stop before deployment or campaign resumption. The spike ends when the cause
is demonstrated and the focused/full validation plus live rollback plan are
specified, or when a named missing PostgreSQL diagnostic prevents that proof.

## System and Layer Map

| Layer | Role | Interfaces / dependencies | Evidence needed |
| --- | --- | --- | --- |
| Campaign coordinator | Claims and advances A/B durable tasks | CampaignCoordinatorTask, Event, authorization snapshot | Exact transactions active at `15:29:15Z` and their lock order |
| HPT2 runtime observer | Captures S5 BASELINE before target publication | Run lock, J-Link read-only capture, Evidence insert, Run heartbeat | Transaction duration, held locks, commit-time FK checks |
| Qualification supervisor/watchdog | Projects run/controller health | Run, Campaign, supervisor snapshot/state, Event | Whether a concurrent snapshot or transition participated in the cycle |
| Django ORM | Defines atomic scopes and deferred relation checks | `transaction.atomic`, `select_for_update`, model FKs | SQL and constraint timing for the reproducer |
| PostgreSQL 17 | Enforces row locks, advisory locks, and FKs | Campaign parent row and child tables | Deadlock graph including both statements and transactions |
| Host runner | Starts confined transient adapter operations | systemd user unit, Compose, nftables | Proof that cleanup and no-target-access classification remain fail-closed |

## Source Hierarchy

Use this order where applicable. Record conflicts; vendor material must not silently override a broader standard or hide a general requirement.

1. PostgreSQL 17 locking and constraint documentation
2. Django 5.2 transaction and ORM behavior
3. Product application transaction boundaries and tests
4. Observed production database, journal, and immutable campaign evidence

## Research Questions

- Which backend was PostgreSQL process `124247`, and what lock was it waiting
  for from transaction `869324`?
- Why did process `129890` reach commit while requiring a Campaign key-share
  lock after `24bdcf7` removed the explicit supervisor Campaign lock?
- Does `record_hpt2_extended_runtime_observation` hold a Run row lock across
  the complete J-Link capture and evidence publication, and can the I/O be
  safely separated from the durable commit without admitting stale identity?
- Which concurrent path still acquires Campaign before waiting on Run or a
  child row?
- Can transaction order be enforced without weakening evidence immutability,
  uniqueness, authorization, or no-replay behavior?
- What exact read-only reconciliation is required before the existing task may
  be replaced or reset, given its exhausted `3/3` infrastructure attempts?

## Behavior Classification

### Mandatory

- Preserve PostgreSQL referential integrity and one ordered campaign event
  stream.
- Preserve fail-closed command publication and no replay after uncertain
  outcomes.
- Keep Campaign 009 and both controller identities immutable.

### Optional

- Additional lock-wait instrumentation may be enabled in an isolated
  PostgreSQL test fixture if it does not mutate production.

### Implementation-Specific

- PostgreSQL advisory event-stream locking is an application choice, not a
  substitute for a complete row/constraint lock-order policy.
- The host runner's prestart cleanup converts prepared/no-target-access
  operations to terminal `BLOCKED`; this is cleanup evidence, not permission
  to retry.

## Timing and Recovery Requirements

The S5 task exhausted its fixed coordinator retry budget at `3/3`. Campaign
rules allow automatic read-only observation and reconciliation but do not
allow another state-changing attempt, command replay, or silent budget
expansion. Any replacement task or campaign revision must preserve the
original failed attempts and append-only history.

## Capability Discovery

Production can prove current durable task/run/command state, container release
identity, PostgreSQL deadlock timing, firewall cleanup, and matrix credit. The
complete historical lock graph cannot be reconstructed from the current log
configuration because PostgreSQL recorded only the victim statement and
parent tuple context. The missing peer statement must be obtained through a
deterministic reproducer or proportionate test-only lock instrumentation.

## Last-Known-Good / Current Comparison

`2d4d9d1` removed Campaign row locking from `append_event`; `24bdcf7` removed
Campaign row locking from `record_and_evaluate_snapshot`. Release `a54fecb`
contains both mitigations, yet the S5 BASELINE Evidence commit still entered a
Campaign-parent deadlock. This disproves the assumption that those two shared
Campaign locks were the complete cause.

## Claims and Evidence

| Claim | Source | Confidence | Applicability |
| --- | --- | --- | --- |
| The deadlock occurred on the deployed `a54fecb` release | live container labels and PostgreSQL log, inspected 2026-08-18 | high | exact Campaign 009 runtime |
| The failed run had no target access and no command was published | run `3438ca44-061a-40b7-9c58-c9bc5ee2251d`, blocked Start operation `47676166-18dd-4848-a11d-539cb55f88cc`, host journal | high | safe failure disposition |
| No HPT2 command remains PREPARED, PUBLISHING, or RECONCILIATION_REQUIRED | live database query, 2026-08-18 | high | current campaign safety gate |
| A S4 is complete; B is complete through 44640/AT and paused before 44640/AFTER | `audit_hpt2_measurement_matrix`, coordinator lane state | high | current matrix frontier |
| Earlier event and supervisor lock removals did not eliminate all Campaign-parent deadlocks | commits `2d4d9d1`, `24bdcf7`, and the 2026-08-18 production failure | high | repair scope |

## Contradictions, Counterexamples, and Disconfirming Evidence

- The active campaign checkpoint said `24bdcf7` was deployed and two
  postdeployment concurrent cycles had no deadlock. Production is now on later
  release `a54fecb`, and a new S5 prestart deadlock occurred.
- The supervisor state temporarily reports `SUPERVISOR_STARTUP_PENDING` for
  the safely aborted run; that projection must not be mistaken for an active
  target operation.

## Unknowns and Evidence Gaps

- The peer PostgreSQL statement and complete historical lock graph were not
  retained in production logs; the deterministic concurrency reproducer now
  supplies the missing transaction-order proof.
- Separating every long J-Link capture from its database transaction remains
  a broader architectural question, not a blocker for the verified lock-order
  correction.

## Findings

- Campaign 009 resumed through the authenticated lane-specific control and is
  working with no unresolved target command.
- The failure is infrastructure/application-method evidence, not SD-media,
  firmware, controller, or card-family failure.
- The exact cycle was the runtime observer holding `Run` while its deferred
  `Evidence.campaign` foreign key requested Campaign key-share at commit, and
  `evaluate_missing_or_stale_campaign` holding Campaign while its supervisor
  projection's deferred `run` foreign key requested Run key-share at commit.
- `evaluate_campaign_dependencies` had the same latent Campaign-to-Run order.
  Both watchdog entry points now rely on the existing per-controller
  `QualificationSupervisorState` lock and do not lock the shared Campaign row.

## Implemented Correction and Verification

- SD Qualifier commits `569dc80` and `8a95ce7` implement the generalized
  watchdog lock policy, unit assertions for both projector paths, and a
  deterministic PostgreSQL two-transaction regression reproducing the
  production Run/Evidence versus Campaign/supervisor relationship.
- Focused PostgreSQL validation passed 41/41, including the concurrency
  regression. Full fixture-isolated PostgreSQL validation passed 422/422 in
  145.984 seconds.
- Backup `/backups/sd-qualifier-20260818T170952Z.dump` is 5,021,549 bytes with
  SHA-256 `16d351cfedbc28eb83b0f46300eead5c64731a450ad30900a08e2461d9359e01`;
  the isolated restore check passed before deployment.
- Release `campaign009-watchdog-deadlock-8a95ce7` at commit
  `8a95ce774198510a024e2dd900d59d1b8372ad6a`, image
  `sha256:6f0141a1f76e0b7c141a22664aab054226fe303e4d8e554fe7690e804113b23b`,
  is deployed to all five application services. The web container is healthy,
  `/healthz` reports `required_now_ready=true`, database/storage/clock/workers
  ready, and the postdeployment watchdog log has no deadlock, traceback, or
  error.
- The candidate deployment manifest is
  `/protected/qualification-supervisor-deployment.c009-watchdog-deadlock.json`
  with SHA-256
  `9fefd2129078afa6cedb9f60350f0be2cb6b16aa84212858ba42485ef27e57d1`.
  The prior image, compose override, canonical manifest, environment file, and
  verified database backup remain available for rollback.
- Pre- and postdeployment campaign snapshots agree: 31 completed, two failed,
  and one pending coordinator task; 208 completed and ten aborted runs; no
  open `Command`; no HPT2 operation in `PREPARED`, `PUBLISHING`, or
  `RECONCILIATION_REQUIRED`; A remains blocked at `matrix.s5.a`; B remains
  paused before `matrix.s4.44640.after.b`. Deployment did not replay or expand
  work.
- Two additional parallel-lane defects discovered through the authenticated
  route were corrected before resumption. Commit `eac3ab6` exposes
  lane-specific reconciliation while another lane keeps the overall
  coordinator running; commit `ffddac1` binds the mutation to the lane's exact
  active node instead of an older historical failed task. Their focused suites
  passed 64 and 14 PostgreSQL-backed tests respectively.
- Release `campaign009-watchdog-deadlock-ffddac1` at commit
  `ffddac1296c24fa299c4cdbe82deccf077b40200`, image
  `sha256:b613ebc07730bbb1fafe3c67a79d9d5f68838081203eea05ada4fc6412eded4c`,
  is deployed to all five services. Its protected manifest SHA-256 is
  `49fadfc7229b91d50abdea8ee3e8b1896e8b33b7094833a59d747ef2631a58ae`.
- After authenticated reconciliation as durable partial progress with no
  active action, the visible Resume A control appended attempt `4/4` for task
  `f5df9729-e23b-470a-b790-038670f75b7b`. Run
  `f232c04a-7dae-4056-9072-5f09dddf9344` persisted S5 BASELINE evidence
  `24294`, crossed the former deferred-FK deadlock point, and reconciled START
  operation `2476af96-9c58-4b53-a143-9fc6feb3c4ad` read-only. No Command or
  unresolved HPT2 operation remains while the bounded observation runs.
- The same-run identity capture `634` records protocol
  `DETECTION_READY; RAW_HPT; DRIVER_OK`, CID-valid and sector-read-valid true,
  MID `0x05`, OID `??`, product `asdfg`, revision `2.2`, serial `7305`,
  manufacturing date `2026-03`, and raw CID
  `05000C61736466672200001C8901A34F`.
- Eight bounded postrepair S5 observations completed, bringing A to `10/10`;
  every baseline persisted without the former deadlock and task
  `f5df9729-e23b-470a-b790-038670f75b7b` completed without replay.
- The subsequent `SUPERVISOR_SNAPSHOT_MISSING` gate was independent of the
  PostgreSQL repair: the extended runner did not bind the host observer.
  Commit `546bf92` adds exact role/run/service binding and cleanup before
  START. Bash syntax and nine focused tests passed, and live S6 transition
  `...-1` to `...-2` proved automatic rebinding with a healthy matching
  supervisor state and zero unresolved command operations.
- Fresh restore-verified backup
  `/backups/sd-qualifier-20260818T203828Z.dump` is 5,054,370 bytes with SHA-256
  `8c8545819f7448555e38a81091d5449095af686250d3ee89db0ea3f3f70e6d9e`;
  the protected current-backup marker now clears the backup-age gate.

## Recommended Engineering Rules

- Establish one documented global lock order across Campaign, Run,
  coordinator, supervisor, Evidence/Event, and deferred FK checks.
- Keep external J-Link I/O outside a database transaction unless a reviewed
  two-phase identity protocol proves the lock is necessary and bounded.
- Add a PostgreSQL concurrency regression that runs the S5 baseline observer
  against every periodic coordinator/watchdog writer seen in production.
- Treat a deadlock as a fail-closed method failure; do not retry a target
  command or grant matrix credit.

## Prohibited Assumptions or Mitigations

- Do not add another isolated `select_for_update` removal without the complete
  lock graph.
- Do not convert PostgreSQL deadlock exceptions into automatic success or
  unbounded retries.
- Do not reset the task attempt budget or resume B to obtain progress while A
  is blocked.

## Validation Implications

The correction passed its deterministic PostgreSQL concurrency reproducer,
focused coordinator/watchdog/HPT2 runtime tests, complete isolated PostgreSQL
suite, deployment backup/rollback verification, authenticated reconciliation,
and eight repeated postdeployment S5 baseline/terminal cycles with no
deadlock, duplicate event, missing Evidence, or command replay.

## Recommendation

The deadlock detour is resolved and Campaign 009 is working again. Preserve
the generalized lock-order and extended-observer regressions while A continues
S6 under its healthy automatically rebound supervisor; keep B paused until
the exact coordinator plan advances that lane.

## Promotion / Disposition Plan

If the transaction policy is general, promote it to the SD Qualifier
operations/architecture documentation in the implementation repository. At
completion, choose `planned` and name the exact repair ticket/workpackage, or
`documented` if the correction is completed within this campaign detour.

## Follow-Up

- [x] Build the deterministic PostgreSQL lock reproducer and capture both
  transaction paths.
- [x] Compare the demonstrated cycle with `2d4d9d1` and `24bdcf7`.
- [x] Select and review one generalized transaction-boundary correction.
- [x] Define focused/full validation, backup, deployment, rollback, and live
  no-deadlock gates.
- [x] Verify the UI-supported exhausted-task reconciliation and no-replay
  campaign-resumption path.
