api: YAML JSON TOON Database
version: 1.0.0
format: yaml
dataset:
  id: 1546
  slug: database-isolation-anomalies
  title: Database Isolation and Concurrency Anomalies
  description: Concurrency anomalies, example interleavings, and mitigation choices with PostgreSQL-specific caveats.
  summary: 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_when:
    - Design a concurrent balance, inventory, or editing workflow.
    - Interpret a serialization or deadlock failure.
  caveats:
    - Isolation names do not imply identical behavior across database engines.
    - Examples are conceptual; test the invariant under real concurrent load.
  related:
    - acid-vs-base
    - sql-index-patterns
  reviewed_at: "2026-10-12"
  sources:
    - title: PostgreSQL transaction isolation
      url: "https://www.postgresql.org/docs/current/transaction-iso.html"
    - title: PostgreSQL explicit locking
      url: "https://www.postgresql.org/docs/current/explicit-locking.html"
  reading:
    - database-isolation-and-lost-updates
  provenance: Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses.
  entry_count: 8
  fields:
    - section: data
      field: anomaly
      type: string
      example: Dirty read
    - section: data
      field: example
      type: string
      example: Read an uncommitted value that is later rolled …
    - section: data
      field: mitigation
      type: string
      example: Use an isolation level that excludes dirty read…
    - section: data
      field: caveat
      type: string
      example: PostgreSQL treats Read Uncommitted as Read Comm…
  category: Database Types
  category_slug: database-types
  tags: database-types,database isolation anomalies,reference,engineering
  view_count: 1
  created_at: 1791798640
  updated_at: 1791798640
data:
  - anomaly: Dirty read
    example: Read an uncommitted value that is later rolled back.
    mitigation: Use an isolation level that excludes dirty reads.
    caveat: PostgreSQL treats Read Uncommitted as Read Committed.
  - anomaly: Nonrepeatable read
    example: Read the same row twice and observe a committed change.
    mitigation: Use a stable snapshot when the transaction requires it.
    caveat: Read Committed gives a new snapshot per statement.
  - anomaly: Phantom read
    example: Repeat a predicate and see a changed matching row set.
    mitigation: Use appropriate snapshot or serializable semantics.
    caveat: Implementations can provide stronger behavior than the SQL minimum.
  - anomaly: Lost update
    example: Two writers overwrite state computed from the same old value.
    mitigation: Use a conditional update, row lock, or atomic expression.
    caveat: Simply wrapping read and write in a transaction is insufficient.
  - anomaly: Write skew
    example: Two transactions each update a different row after reading a shared rule.
    mitigation: Use serializable isolation or lock the invariant’s scope.
    caveat: Snapshot isolation can permit this anomaly.
  - anomaly: Serialization failure
    example: The database rejects a conflicting serializable transaction.
    mitigation: Retry the whole transaction with bounded backoff.
    caveat: Retrying only its last statement can preserve stale decisions.
  - anomaly: Deadlock
    example: Transactions acquire the same locks in opposite order.
    mitigation: Use consistent lock ordering and short transactions.
    caveat: The database may abort one participant.
  - anomaly: Stale application cache
    example: A transaction commits but a reader uses an old cache value.
    mitigation: Define cache invalidation or version checks.
    caveat: SQL isolation does not coordinate an external cache.