Database Anomalies: How SQL and NoSQL Handle Dirty Reads, Phantom Reads, and Write Skew

Scenario context

Database anomalies are the predictable side-effects of concurrent transactions — dirty reads, phantom reads, write skew, and lost updates. SQL databases use MVCC and isolation levels to prevent them; PostgreSQL's Serializable Snapshot Isolation (SSI) stops even write skew. NoSQL databases — Cassandra, DynamoDB, MongoDB, Cosmos DB — deliberately accept some anomalies in exchange for throughput and availability. The r…

This walkthrough focuses on The Banking Scenario That Exposes Every Hidden Database Assumption.

Before making a design choice, clarify:

• Imagine a bank account with a balance of $200.
• Two wire transfers are initiated at the same moment: Alice wants to send $150 to her landlord, and Bob's payroll system is deducting $100 for a loan payment.
• Both transactions read the balance — both see $200 and decide there is enough money.

Concepts in play: database-isolation-levels-anomalies, caching, consistency-replication, database-indexing

Stage 1 — ACID and Isolation: The Contract That Keeps Concurrent Transactions Honest

• Before cataloguing anomalies, we need to understand the contract that prevents them: ACID .
• • Atomicity: A transaction either commits all its writes or rolls all of them back.
• • Consistency: A transaction transitions the database from one valid state to another.
• Application-defined invariants — like "account balance must never go negative" — must hold before and after every transaction.

Observable outcome: Application-defined invariants — like "account balance must never go negative" — must hold before and after every transaction.

Stage 2 — The Seven Core Database Anomalies That Break Production Systems

• Each anomaly below has a precise definition, a timeline showing how it occurs, and a concrete consequence in a real system.
• Dirty Read What happens: Transaction T2 reads a row that Transaction T1 has written but not yet committed.
• If T1 then rolls back, T2 has acted on data that was never durably stored — data that never officially existed.
• Concrete scenario: The following sequence diagram shows T1 writing an account balance that T2 reads before the commit.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2
    T1->>DB: BEGIN
    T1->>DB: UPDATE account SET balance=50 WHERE id=1
    Note over DB: Row written, not yet committed
    T2->>DB: BEGIN
    T2->>DB: SELECT balance FROM account WHERE id=1
    DB-->>T2: Returns 50 (dirty read)
    T1->>DB: ROLLBACK
    Note over DB: Balance restored to original 100
    Note over T2: T2 acted on value 50 which never committed

Observable outcome: Concrete scenario: The following sequence diagram shows T1 writing an account balance that T2 reads before the commit.

Stage 3 — Deep Dive: How Databases Detect and Stop Anomalies Before They Corrupt Data

• The Internals: MVCC Row Versioning, Snapshot Reads, and Lock Acquisition The dominant technique for preventing read anomalies without blocking all concurrent reads is Multi-Version Concurrency Control (MVCC) .
• Instead of storing a single current version of each row, MVCC stores multiple versions, each tagged with the transaction that created or deleted it.
• In PostgreSQL, every row has two hidden system columns: - xmin : the transaction ID that inserted this version of the row - xmax : the transaction ID that deleted or updated this row (zero if the row is still current) W…
• The visibility rule is: a row version is visible to a transaction if its xmin is committed and less than the transaction's XID, and its xmax is either zero or not yet committed when the snapshot was taken.

Observable outcome: The visibility rule is: a row version is visible to a transaction if its xmin is committed and less than the transaction's XID, and its xmax is either zero or not yet committed when the snapshot was taken.

Stage 4 — How MongoDB, Cassandra, DynamoDB, and Cosmos DB Approach Anomaly Prevention

• NoSQL databases were built to scale horizontally across commodity hardware and to favour availability over strict consistency.
• That design choice has direct consequences for anomaly prevention.
• MongoDB: Document Atomicity With Optional Multi-Document Transactions MongoDB guarantees atomic operations on a single document always — even without transactions.
• A document update that modifies multiple fields is either fully applied or not applied at all.

const session = client.startSession();
session.startTransaction({
  readConcern: { level: 'snapshot' },
  writeConcern: { w: 'majority' }
});
// All reads in this session see the same snapshot
// All writes are all-or-nothing
await session.commitTransaction();

INSERT INTO seats (id, holder) VALUES (5, 'alice') IF NOT EXISTS;
UPDATE accounts SET balance = 150 WHERE id = 1 IF balance = 200;

Observable outcome: A document update that modifies multiple fields is either fully applied or not applied at all.

Stage 5 — Choosing the Right Database for Your Anomaly Tolerance

• The right database choice depends on answering two questions first: which anomalies does your workload generate?
• And which anomalies can your workload afford to accept?
• The following flowchart guides the database decision for workloads where anomaly prevention is a primary concern: Follow the flowchart from top to bottom.
• The left branch captures workloads that cannot tolerate write skew — financial systems, booking systems, any domain with a shared invariant across multiple rows.

flowchart TD
    A[What anomaly risk does your workload have?] --> B{Can your workload tolerate write skew?}
    B -->|No - invariant must hold across rows| C{Multi-region required?}
    B -->|Yes - single-row or LWW is fine| D{High write throughput required?}
    C -->|Yes| E[Cosmos DB Strong or Google Spanner]
    C -->|No| F[PostgreSQL Serializable with retry on abort]
    D -->|Yes - millions of writes per second| G[Cassandra with LWT on critical paths]
    D -->|No - thousands of writes per second| H{Need multi-item atomicity?}
    H -->|Yes - up to 25 items| I[DynamoDB TransactWriteItems]
    H -->|No - single document sufficient| J[MongoDB default or DynamoDB single-item]

Observable outcome: The left branch captures workloads that cannot tolerate write skew — financial systems, booking systems, any domain with a shared invariant across multiple rows.

Stage 6 — PostgreSQL SSI: How Serializable Snapshot Isolation Prevents Write Skew Without Locking Rows

• PostgreSQL's Serializable Snapshot Isolation (SSI), introduced in PostgreSQL 9.1, is one of the most significant advances in practical database concurrency control.
• It delivers full serializability — preventing all seven anomaly classes — without the blocking overhead of two-phase locking (2PL).
• 2PL: Traditional serializable isolation using 2PL acquires a shared lock on every row read and an exclusive lock on every row written.
• Lock contention under concurrent read-heavy workloads is severe.

-- Set isolation level for the session or transaction
SET default_transaction_isolation = 'serializable';

-- Or per-transaction:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT count(*) FROM doctors WHERE on_call = true;
  UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;
-- If write skew is detected, PostgreSQL returns:
-- ERROR: could not serialize access due to read/write dependencies
-- SQLSTATE: 40001 (serialization_failure)
-- Application must catch this error and retry the transaction.

Observable outcome: Lock contention under concurrent read-heavy workloads is severe.

End-to-end outcome

After working through the stages, you should be able to explain:

• Most systems use Read Committed for 95% of their transactions — and pay for it occasionally.
• The default isolation level in PostgreSQL, MySQL, and Oracle is Read Committed.

Alternatives considered

SQL Isolation Levels vs. Anomaly Prevention: PostgreSQL and MySQL InnoDB

The table below maps each isolation level to the anomalies it prevents or allows for the two dominant SQL MVCC implementations.

Failure and degraded modes

Consistency vs. Throughput: What Every NoSQL Trade-off Actually Costs You

• Detection: Understanding the anomaly table above is only half the picture.
• User or operator impact: Understanding the anomaly table above is only half the picture.