# Cascade delete lock-scope — bound the parent `FOR UPDATE` hold

Status: **design (parallel track)** · Issue: #3477 · Related: [outbox-redesign.md](outbox-redesign.md) (independent — no ordering dependency)

This is an **L0 `Delete` contract change**, kept on its own surface because it alters all-or-nothing delete atomicity for RESTRICT/rollup and must be reviewed on those merits, not bundled into the outbox queue redesign.

## 1. Problem (current-tree evidence)
A cascade delete of a parent record acquires the parent row `FOR UPDATE` and holds it across the **entire** child recursion inside **one** pessimistic transaction. For a large child graph this holds the parent lock for tens of seconds; concurrent operations needing that row queue on the 10s pessimistic lock-wait, time out with Error 1205, and retry — a lock-contention storm. Observed in #3477: ~30s parent-lock hold, and the cascade continued running well past the point the originating client request had already returned a gateway timeout.

Two current-tree facts that constrain the fix:
- **Request-context cancellation does not fire.** There is no server write deadline and no timeout on the delete request path; an upstream gateway timeout does not cancel the origin request context. So a `ctx.Done()` guard inside the cascade never triggers — the server keeps working after the client is gone.
- **A single enclosing pessimistic transaction holds every child lock until the outer commit.** Per-child lock-acquisition granularity does not shorten the parent-lock hold; only splitting the transaction does.

## 2. Rejected shortcuts
- **`ctx.Done()` guard in the cascade** — inert (no cancellation signal is produced; see §1).
- **Per-child lock acquisition inside the one tx** — inert (locks release only at the outer commit).
- **A hardcoded wall-clock delete budget in the L0 engine** (e.g. a fixed 25s cap on every `Delete`) — hard-fails legitimately large tenant cascades (tenants may declare wide/deep cascade graphs via relationship metadata) and bakes app-shape assumptions into the platform engine. A wall-clock policy, if any, belongs in L1/config as a tunable, not as an engine constant.

## 3. Design — chunked cascade with per-batch transactions
Bound the parent `FOR UPDATE` hold from ~30s to ~1–2s by splitting the cascade:
1. **Plan phase (short tx or lock-free):** lock the parent, run RESTRICT checks + before-delete validators, and **collect the ordered child plan** (the existing hierarchy traversal already walks children without needing the parent row lock — reuse it as proof the lock is not required for traversal). Release the parent lock.
2. **Child phase (many short txns):** delete children **leaf-first in bounded batches**, each batch its own short transaction. No parent row lock is held across batches. Existing per-child idempotency/not-found guards stay.
3. **Finalize phase (short tx):** re-lock the parent, **re-validate the RESTRICT/cascade plan under lock** (defends the re-lock window against a concurrent writer that changed the child set), apply the parent delete + setNull + rollup + audit + event enqueue, commit.

Optionally add an explicit **service-level deadline** at the delete entry (in the records service or REST handler — a *service* concern) so a runaway cannot live forever. This is not an L0 engine constant and is tunable/removable.

## 4. The atomicity contract change (the crux to sign off)
Splitting one transaction into plan → children → finalize **gives up all-or-nothing atomicity** for the whole cascade. Consequences to accept explicitly:
- A crash between the child phase and finalize leaves an **orphaned parent** (children gone, parent still present) — a state that did not exist under the single-tx design.
- RESTRICT correctness must be re-checked in the finalize phase, because the plan was computed earlier and a concurrent writer could have added a restricting child.
- Rollup/derived recomputation must tolerate partial-progress (children deleted in batches).

### Recovery / sweep
- **Delete is made resumable + idempotent:** re-running the delete of an orphaned parent re-plans, finds the children already gone (no-op), re-validates, and finalizes. So a retry self-heals.
- A **sweeper** (or the next delete attempt) reconciles orphaned-parent state; each phase is idempotent so re-entry is safe.

## 5. Rollout
- Behind its own flag `CASCADE_TWO_PHASE`, default OFF.
- a staging environment first; prove parent-lock-hold ~1–2s via the existing transaction stage-timing output.
- Crash-injection test between child phase and finalize → assert the orphan-parent is reclaimed on delete-retry.

## 6. Gate
- [ ] Chunked cascade releases the parent `FOR UPDATE` between child batches (measured hold ~1–2s).
- [ ] RESTRICT re-validated under lock in the finalize phase.
- [ ] Rollup/derived recompute tolerates partial-progress.
- [ ] Crash between phases → orphan-parent reclaimed on retry (idempotent re-plan proven by test).
- [ ] No hardcoded L0 wall-clock budget; any deadline is an L1-tunable service-level concern.
- [ ] Sign-off on the atomicity contract change from a records/L0 owner.

## 7. Residual / out of scope
- Client-abort propagation (giving the delete a real cancellation signal) is a **separate** server-layer concern; it is not required for the lock-scope fix and is not solved here.