Skip to main content

Database boundaries: process exit and overlapping transactions

The existing outbox already runs on SQLite. This clinic adds what that harness explicitly excludes: abrupt process exit and overlapping database connections. It adds 12–18 hours: prediction and explanation 3–4, native experiments and changed cases 6–9, assessment/repair 3–5. Transactions becomes 102–148 hours; Semester 06 becomes 512–743. These estimates await pilots.

Prerequisites and existing work​

Bring the outbox atomic-intent invariant, explicit SQL transactions, Semester 05's process-versus-machine failure distinction and basic subprocess execution. Explain why catching a Python exception is not the same as ending the process without cleanup. If that explanation is weak, trace the existing outbox first. Keep PostgreSQL query plans, anomaly sessions, WAL model, saga work and privacy/retrieval evidence; this clinic does not replace any of them.

Lesson 1: committed, observed and acknowledged are separate events​

Use one account row and one event row in the same database transaction. The experiment begins at balance zero with no events, adds an amount, and inserts event e1 carrying that amount. The invariant is that a committed addition and its event appear together. A file existing on disk is not evidence of this invariant; inspect both rows after reopening.

Predict three cuts: after updating the account but before inserting the event; after both statements but before COMMIT; after COMMIT returns but before any application reply. The first two should reopen with zero and no event. The third should reopen with the amount and one event. An event in a different database would need a different atomicity argument; do not assume the transaction covers every effect in the application.

The child calls os._exit(17) at the cut, bypassing normal connection cleanup. The parent waits for that exact exit code before inspecting the file. A timeout, syntax error or a different exit code is a harness failure, not a successful injected crash. There is no timed sleep or assumption that an update finished “by now.” The cut is located in program order.

The third cut has committed state but no application reply. A caller cannot infer failure merely from a lost response. Conversely, this test's parent knows the chosen cut because it controls the experiment; an ordinary remote caller would not. Reuse the existing stable event-ID/inbox contract to reason about retries. Do not blindly issue another additive update after an uncertain response.

Practice: move COMMIT between account update and event insertion. Predict the invariant violation at the first cut, then run only against a fresh temporary database. Preserve the failing result before repairing it. This is an intentional broken transaction boundary, not evidence that SQLite lost atomicity.

Lesson 2: a retry may need a new decision​

The two-connection experiment establishes A's WAL read snapshot at balance zero. B commits balance seven. A reads again and still sees zero, then tries to write. Its stale upgrade fails. A rolls back, begins a new transaction, reads seven, adds one and commits eight. Keeping the cached zero after restarting would produce one and overwrite B's effect.

Write the schedule as numbered calls, not approximate timestamps. Identify the read that establishes the old snapshot, B's commit, A's rejected update and A's fresh read. The experiment uses separate connections with ordinary private caches and an explicit zero wait timeout. It reports the driver error code when exposed; older Python reports only a lock-error message. Do not invent an extended error code from that message alone.

A second schedule holds A's BEGIN IMMEDIATE write transaction while B attempts its own. B fails immediately under this timeout configuration. After A rolls back, B can begin. This is a distinct cause of a busy result from stale snapshot promotion. Waiting may help transient writer contention, but cannot make an obsolete read snapshot current. Always tie remediation to the schedule and transaction state.

Whole-transaction retry means rerunning reads and decisions under a fresh transaction, with bounded retry/backoff and a termination policy in a real service. It does not mean retrying every exception, repeating external payments or bypassing constraints. Our controlled schedule resolves contention explicitly; it does not implement a production retry policy.

Practice: change B's committed balance to 11. Predict A's old read, fresh read and final incremented value. Then alter only the retry to reuse the old value. State which observation detects the fault and why row counts alone do not detect it.

Lesson 3: what the engine experiment cannot prove​

These are actual SQLite transactions and files, but only one host remains alive throughout. The child exits; the operating system and storage remain running. synchronous=FULL records a requested configuration, not evidence that every device honors flushes. No power-loss, disk-corruption, network-filesystem or replica guarantee follows from this result. Ordinary reopening is not a verified backup procedure.

The SQLite concurrency schedule is not a PostgreSQL isolation-level comparison. Keep the required PostgreSQL anomaly lab and record its actual engine version and sessions. PostgreSQL maps READ UNCOMMITTED to READ COMMITTED, so dirty reads are prevented there; a request to reproduce them should become an explanation plus a prevention observation. Its REPEATABLE READ also prevents phantoms but can admit write skew. Do not transfer SQLite's single-writer behavior into an explanation of PostgreSQL's implementation.

A finite successful run supports the specific schedule and environment. It does not prove all interleavings, crash positions or hardware failures. Use the prior model for exhaustive bounded cuts, these engine experiments for observed semantics, and independent reasoning for the scope of the claim. State each kind of evidence separately.

Runnable experiment​

Download database-boundaries.zip. Python 3.9+ with sqlite3 and a local writable temporary directory are required. Run python3 experiment.py > observations.json. No server or network is used. Read the README before execution. The reference creates isolated temporary databases, runs 12 child-exit cases and two ordered connection schedules, records observations and cleans up afterward. It is a worked reference, not learner evidence.

Before reading the implementation, predict each state and sketch the session schedule. Run from a fresh extraction; retain the JSON, runtime versions, command and assistance record. Implement the changed-amount and changed-balance cases independently. Add a negative test that detects a split transaction and a stale-value retry. Compare with the prior outbox exception/reopen checks; identify the new evidence rather than claiming to have built a second outbox.

Independent gates and calibration​

Score DB1–DB3 separately: 0 invalid/missing, 1 assisted or incomplete, 2 independently correct with inspectable evidence, 3 also adapts and defends limits. Each requires 2 alongside TX1–TX6 and all earlier semester requirements.

  • DB1: abrupt exit and atomicity. Predict and reproduce all three cuts with a changed amount; check both account and event. Calibration: precommit cuts yield (0, no event); postcommit cut yields (amount, e1). A different child exit code requires investigation, not a pass. Defend why this is not a power-loss experiment.
  • DB2: overlapping connections and retry. Trace both schedules and independently change B's balance. Calibration: A's established snapshot stays old; restart and reread before deciding. For B=11, final value is 12. Reusing zero fails. Distinguish an observed error category from an extended code unavailable in the driver.
  • DB3: guarantee and engine comparison. Explain missing-response ambiguity, idempotent recovery, the distinct PostgreSQL gates and limits of evidence. Calibration: SQLite success does not close PostgreSQL work; a process exit does not establish durability after power failure. Present at least one claim rejected by the evidence and a concrete experiment needed to evaluate it.

Record reviewer status honestly. Repair only failed dimensions and repeat one changed schedule after 7–14 days without the key; active recheck time belongs in the 3–5-hour assessment allocation. PostgreSQL execution pending means that original requirement remains incomplete, even if DB1–DB3 pass.

Selective primary sources​

  • SQLite isolation: inspect connection visibility and WAL snapshot promotion to resolve a specific prediction.
  • SQLite WAL: distinguish commit, checkpoint and deployment constraints; this clinic does not exercise every mechanism.
  • Python sqlite3 transaction control: interpret the wrapper as well as the engine. This reference uses isolation_level=None with explicit SQL BEGIN/COMMIT/ROLLBACK and avoids changing newer autocommit defaults implicitly.
  • PostgreSQL transaction isolation: compare actual implementation guarantees and whole-transaction retry requirements.

Sources checked 2026-10-23. Existing database books remain the selective backbone; no new purchase or full-book transcription. Independent review and learner workload pilots remain pending.