hesela.devLost update
A lost update occurs when a write based on an earlier read overwrites another actor's intervening update to the same data item.
Berenson et al. describe this as phenomenon P4. Write skew is different: transactions can change different items while jointly violating a constraint. [1]
A database can serialize individual statements correctly while an application still loses an increment across separate read and write transactions. Hesela's native SQLite WAL experiment has two callers read zero and later each write one; both writes succeed but the final counter is one. This is not a failure of SQLite transaction isolation. [2] [6]
An UPDATE that computes value=value+1 inside the statement preserves both increments in this one-row experiment. A version-qualified UPDATE instead rejects the second stale request with zero affected rows and no SQL error. That rejection does not complete the requested increment. Engineering condition: every participating writer must advance a version that is not reused; the database does not enforce this application protocol automatically. [3] [6]
With both read transactions kept open, the WAL control rejects promotion of the stale snapshot with SQLITE_BUSY_SNAPSHOT (517), then explicitly rolls back; it does not retry. A storage-stack lost write is a different problem: completion was reported but the intended storage update is absent. No crash or storage fault is injected here. [4] [5] [7]
Retry implication, not another measured schedule: restart the read-compute-write operation from a fresh read; do not replay an old literal after rollback. A zero-row guard must be handled as a conflict. [4] [3]
Relations
- related
- Lost write, Compare-and-swap, Write skew, Serializability, SQLITE_BUSY_SNAPSHOT, Read Committed isolation
Citations
- Berenson et al. A Critique of ANSI SQL Isolation Levels. SIGMOD 1995, sections 4.1 (P4) and 4.2 (A5B).
- SQLite Transaction: sections 2 and 2.3, implicit transactions and completed statements.
- SQLite UPDATE: section 2, row predicates and assignment expressions.
- SQLite Isolation: WAL snapshot isolation and SQLITE_BUSY_SNAPSHOT.
- SQLite result codes: SQLITE_BUSY_SNAPSHOT (517).
- Hesela: native SQLite lost-update schedules, source, method and results.
- Hesela: Lost write, storage-stack completion versus recorded data.
Sources & caveats
- Primary-source synthesis and a native, deterministic SQLite 3.45.1 experiment. Four prescribed schedules, not a benchmark, prevalence estimate, crash test or general isolation-conformance test.