# Lost update: SQLite operation boundaries

This is a native, deterministic two-connection SQLite experiment, not a timing
benchmark or a simulated database. Each of four prescribed schedules uses a fresh
temporary, trigger-free, single-row database. Initial value and version are zero.
Both connections use private caches, WAL, synchronous FULL, and read_uncommitted=0.
Python 3.12+ uses explicit autocommit=True; older Python uses isolation_level=None.
Cursors are consumed and closed. Explicit BEGIN/COMMIT/ROLLBACK are SQL statements.

Run with Python 3.11+ and SQLite WAL support, with these files together:

    python3 -B probe.py --check results.json
    python3 -B -m unittest -v test_probe.py
    python3 -B -O -m unittest -v test_probe.py

For a new capture, use --output with a new filename. Existing files are not replaced.
The replay compares complete ordered SQL/parameter/row/error/transaction-state traces,
settings, source hash and outcomes. Runtime differences are reported separately.
results.json records the actual capture time, runtime and SQLite source ID.

## What each case measures

- detached-literal: A and B separately read 0, then each independently writes 1.
  Both updates affect one row; final value is 1. The application increment spans
  transactions. This is not a violation of SQLite's transaction isolation.
- atomic-increment: two UPDATE value=value+1 statements leave value 2.
- version-guard: both read version 0; the first matching update advances it to 1.
  The second matches zero rows without an SQL error. Only one increment completes.
- snapshot-upgrade: A and B retain explicit read transactions. A updates and commits;
  B's attempted write fails with SQLITE_BUSY_SNAPSHOT (517), then B rolls back.
  No retry is performed.

The final integrity_check result is recorded, not treated as proof that application
intent was preserved. There is no crash, device fault, throughput measurement,
random workload or estimate of real-world frequency. These results do not prove
crash durability, general serializability or prevention of multi-row write skew.
All participants must advance a nonreused version for the guarded-write protocol;
the database does not impose that application rule automatically.

## Primary sources

- Berenson et al., A Critique of ANSI SQL Isolation Levels (1995), section 4.1 P4,
  section 4.2 A5B: https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf
- SQLite transactions, sections 2 and 2.3: https://www.sqlite.org/lang_transaction.html
- SQLite UPDATE, section 2: https://www.sqlite.org/lang_update.html
- SQLite WAL snapshot example: https://www.sqlite.org/isolation.html
- Extended error 517: https://www.sqlite.org/rescode.html#busy_snapshot
- Python transaction control and rowcount: https://docs.python.org/3.12/library/sqlite3.html
- Storage lost writes are a distinct failure contract:
  https://www.usenix.org/legacy/event/fast08/tech/full_papers/krioukov/krioukov_html/index.html

Sources accessed 2026-11-13. Source/prose review by a separate agent; native results
are produced by this script. No independent code/release approval is implied.