Database Isolation and Concurrency Anomalies
Concurrency anomalies, example interleavings, and mitigation choices with PostgreSQL-specific caveats.
Concurrency anomalies, example interleavings, and mitigation choices with PostgreSQL-specific caveats. Each row includes a practical application and a failure to check before using it.
Use it when
- Design a concurrent balance, inventory, or editing workflow.
- Interpret a serialization or deadlock failure.
Watch out for
- Isolation names do not imply identical behavior across database engines.
- Examples are conceptual; test the invariant under real concurrent load.
The data
8 rows
| Anomaly | Example | Mitigation | Caveat |
|---|---|---|---|
| Dirty read | Read an uncommitted value that is later rolled back. | Use an isolation level that excludes dirty reads. | PostgreSQL treats Read Uncommitted as Read Committed. |
| Nonrepeatable read | Read the same row twice and observe a committed change. | Use a stable snapshot when the transaction requires it. | Read Committed gives a new snapshot per statement. |
| Phantom read | Repeat a predicate and see a changed matching row set. | Use appropriate snapshot or serializable semantics. | Implementations can provide stronger behavior than the SQL minimum. |
| Lost update | Two writers overwrite state computed from the same old value. | Use a conditional update, row lock, or atomic expression. | Simply wrapping read and write in a transaction is insufficient. |
| Write skew | Two transactions each update a different row after reading a shared rule. | Use serializable isolation or lock the invariant’s scope. | Snapshot isolation can permit this anomaly. |
| Serialization failure | The database rejects a conflicting serializable transaction. | Retry the whole transaction with bounded backoff. | Retrying only its last statement can preserve stale decisions. |
| Deadlock | Transactions acquire the same locks in opposite order. | Use consistent lock ordering and short transactions. | The database may abort one participant. |
| Stale application cache | A transaction commits but a reader uses an old cache value. | Define cache invalidation or version checks. | SQL isolation does not coordinate an external cache. |
Fields
The shape of this dataset, read from the data itself: every column, its type and a real value. Agents can use this to plan a query without fetching the whole document.
| Section | Field | Type | Example |
|---|---|---|---|
| data | anomaly | string | Dirty read |
| data | example | string | Read an uncommitted value that is later rolled … |
| data | mitigation | string | Use an isolation level that excludes dirty read… |
| data | caveat | string | PostgreSQL treats Read Uncommitted as Read Comm… |
Fetch the same bytes
The static files contain the same reference rows as the API, with no API rate limit. Live metadata, such as view counts, may differ. Use the API for content negotiation; use the static files for a cacheable snapshot.
curl "https://yjtoon.com/api/dataset/database-isolation-anomalies?format=toon"
const res = await fetch( "https://yjtoon.com/static-data/dataset/database-isolation-anomalies.toon" ); const toon = await res.text();
Rate limit: 120 requests per minute per IP, no key and no signup. API reference →
Sources and editorial notes
Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses.
Editorial review: . Source documents keep their own licenses.