hesela.devDirty read
A dirty read occurs when one transaction reads data written by another transaction before the writer commits.
The writer need not eventually abort for the read to be dirty. The relevant boundary is its uncommitted state at the time of the read. A later rollback makes the practical risk explicit, because the reader may already have acted on a value that never became committed. [1]
This differs from a nonrepeatable read, where a later statement can observe another transaction's committed change. Reading one's own previous writes is also not a cross-transaction dirty read. Read Committed excludes the phenomenon for ordinary transactional data; Read Uncommitted permits it but an implementation can be stronger. [1]
In SQLite, a shared cache and PRAGMA read_uncommitted on the reader are both needed to expose another connection's pending writes. Private-cache readers remain isolated even with the pragma enabled. [2] [3]
The companion example observes a pending value and then the original value after rollback. That is an isolation demonstration, not a test of storage persistence or a recommendation to enable shared-cache mode. [5] [4]
Berenson et al. distinguish broad P1 from narrow A1: P1 does not require abort, whereas A1 includes a writer abort and reader commit. Checking only rolled-back writes misses the broader phenomenon. [6]
Relations
Citations
- PostgreSQL 18: Transaction Isolation, Table 13.1 and section 13.2.1.
- SQLite: Isolation Between Database Connections.
- SQLite: PRAGMA read_uncommitted.
- Hesela: Read Uncommitted depends on the engine and connection setup.
- Berenson et al. A Critique of ANSI SQL Isolation Levels. SIGMOD 1995; MSR-TR-95-51, section 2.2 and section 3, H1.
Sources & caveats
- Primary-source explanation of cross-transaction visibility. No claim that all engines expose dirty reads when the level is requested; own writes and nontransactional engine objects require separate interpretation.