Problems Introduction

The previous page extolled the dramatic benefits of concurrent execution—throughput improvements measured in orders of magnitude, response times that enable real-time interactive systems, and economics that make large-scale computing viable. But this power comes with a dangerous caveat.

When multiple transactions execute concurrently and access shared data, a class of subtle but devastating problems can emerge. These problems don't crash the system; they corrupt it silently. Money disappears from bank accounts. Inventory counts become inconsistent. Decisions are made on data that was never valid.

The fundamental tension:

We want the performance benefits of concurrent execution, but we need the correctness guarantees of serial execution. This tension—between performance and correctness—is the central challenge of database concurrency control.

This page introduces the categories of problems that uncontrolled concurrent execution creates. Understanding these problems is essential before we can appreciate the mechanisms designed to prevent them.

To understand concurrency problems, we must first understand why they exist at all. The answer lies in the interaction between transaction isolation expectations and actual execution behavior.

The Isolation Expectation:

The ACID property of Isolation promises that transactions appear to execute in isolation from one another. From any transaction's perspective, it should seem as though it were the only transaction running. Other transactions' modifications should not be visible until they commit, and the transaction's own modifications should not interfere with others.

The Reality of Concurrent Execution:

In reality, concurrent transactions share physical resources and data structures. Operations from different transactions interleave in time. This interleaving can create situations where:

1. One transaction reads data that another transaction is in the middle of modifying
2. Multiple transactions read the same data and attempt to modify it based on stale information
3. A transaction's partial modifications become visible to other transactions
4. The order of operations creates dependencies that couldn't exist in serial execution

The Core Issue: Non-atomic Operations

Even a simple SQL statement like UPDATE accounts SET balance = balance - 100 WHERE id = 1 actually involves multiple steps:

1. Find the row with id = 1
2. Read the current balance value
3. Subtract 100 from that value
4. Write the new value back

Between these steps, other transactions can execute their own operations on the same data. This is where problems arise—in the gaps between read and write, between logical operation and physical completion.

Database concurrency problems fall into distinct categories based on their nature and the operations that cause them. Understanding this taxonomy is essential for selecting appropriate control mechanisms.

The Four Classic Concurrency Problems:

1. Lost Update (Write-Write Conflict) — Two transactions read the same data and both try to update it. The update from one transaction is lost because the other overwrites it without seeing the first update.

2. Dirty Read (Read Uncommitted Write) — A transaction reads data that another transaction has modified but not yet committed. If the modifying transaction rolls back, the reading transaction has used data that was never valid.

3. Non-Repeatable Read (Read Inconsistency) — A transaction reads the same data item twice and gets different values because another transaction modified and committed between the reads.

4. Phantom Read (New Data Appearance) — A transaction re-executes a query and gets additional rows that weren't present before because another transaction inserted (and committed) matching data.

Relationship to Isolation Levels:

These four problems correspond to violations of different isolation guarantees. The SQL standard defines isolation levels based on which problems they permit:

Note: Lost Update is prevented at all standard isolation levels through different mechanisms.

The Lost Update problem is perhaps the most intuitive and immediately dangerous of the concurrency problems. It occurs when two transactions read the same data item, then both write to it based on what they read. The second write overwrites the first, and the first transaction's modification is permanently lost.

The Fundamental Pattern:

1. Transaction T₁ reads item X (value = V)
2. Transaction T₂ reads item X (value = V) — same value, T₁ hasn't written yet
3. Transaction T₁ writes item X (new value based on V)
4. Transaction T₂ writes item X (new value based on V) — overwrites T₁!

The key insight is that both transactions base their write on the same original value, unaware of the other's modification.

A Dirty Read occurs when a transaction reads data that has been modified by another transaction that has not yet committed. This data is considered 'dirty' because it may not survive—if the writing transaction rolls back, the dirty data never existed from a committed database perspective.

The Fundamental Pattern:

1. Transaction T₁ writes item X (new value = V')
2. Transaction T₂ reads item X (sees V') — this is the dirty read!
3. Transaction T₁ rolls back (X reverts to original value V)
4. Transaction T₂ has now used a value (V') that was never valid

The danger is that T₂ may make decisions, perform calculations, or write other data based on a value that the database never actually contained in any committed state.

Why Dirty Reads Are Dangerous:

Dirty reads cause transactions to operate on data that may never have been valid:

1. Decision Making — Business logic may approve or deny actions based on uncommitted data
2. Cascading Errors — The dirty-read transaction may write data based on the invalid read, propagating corruption
3. Inconsistent Reporting — Reports may include data that was rolled back
4. Constraint Violations — A transaction might see a state that passes validation but results in violations when combined with rollback

Most business applications cannot tolerate dirty reads, which is why Read Committed (or higher) isolation is the default in most database systems.

A Non-Repeatable Read (also called Fuzzy Read or Read Skew) occurs when a transaction reads the same data item twice and gets different values because another transaction modified and committed that data between the reads.

The Fundamental Pattern:

1. Transaction T₁ reads item X (value = V)
2. Transaction T₂ updates item X to V' and commits
3. Transaction T₁ reads item X again (value = V') — different value!
4. Transaction T₁ has now seen two different values for X within the same transaction

This violates the expectation that a transaction sees a consistent snapshot of the database throughout its execution.

Why Non-Repeatable Reads Are Problematic:

1. Logical Inconsistency — Application logic that assumes data stability can produce incorrect results
2. Decision Reversal — A check at the start of a transaction doesn't guarantee the condition holds at the end
3. Calculation Errors — Aggregations or multi-step computations may use inconsistent values
4. User Confusion — Users may see different values within what appears to be a single operation

Distinguishing from Dirty Reads:

Unlike dirty reads, non-repeatable reads involve only committed data. The modifying transaction has fully committed before the second read. This makes the problem more subtle—both values are 'valid' in some sense, just from different points in time.

A Phantom Read occurs when a transaction executes a query twice and gets different result sets because another transaction inserted or deleted rows that match the query's predicates between the two executions.

The Fundamental Pattern:

1. Transaction T₁ executes a query with a WHERE clause, gets N rows
2. Transaction T₂ inserts a new row matching T₁'s WHERE clause and commits
3. Transaction T₁ re-executes the same query, gets N+1 rows
4. The new row is a 'phantom'—it appeared out of nowhere from T₁'s perspective

Phantom reads differ from non-repeatable reads in that they involve the set of matching rows rather than the value of a specific row. Non-repeatable reads happen when existing data changes; phantom reads happen when the set membership changes.

Why Phantom Reads Are Particularly Tricky:

Phantom reads are harder to prevent than other anomalies because they involve predicates, not just data items:

1. Traditional row-level locking is insufficient — You can't lock rows that don't exist yet
2. Range predicates are vulnerable — Any INSERT or DELETE matching a predicate can create a phantom
3. INDEX scans are affected — Even index-based access can't prevent phantoms without special mechanisms

Preventing phantoms typically requires either predicate locking (locking the condition, not just rows) or serializable snapshot isolation (ensuring the transaction sees a fixed point-in-time view).

The four classic concurrency problems are not independent phenomena—they represent different manifestations of a single underlying issue: the violation of serializability. Understanding their relationships helps in selecting appropriate concurrency control mechanisms.

The Severity Hierarchy:

The problems can be ranked by severity, which corresponds to the isolation levels that prevent them:

1. Dirty Read — Most severe; involves uncommitted data that may never exist
2. Non-Repeatable Read — Data changes between reads within a transaction
3. Phantom Read — Set membership changes between queries
4. Lost Update — Special case; prevented differently than the others

Preventing higher-severity problems generally also prevents lower-severity ones (preventing dirty reads automatically prevents non-repeatable reads of uncommitted data).

The Lost Update Special Case:

Lost updates sit somewhat outside the standard hierarchy. They're caused by the read-modify-write pattern without proper synchronization. Different isolation levels handle them differently:

• Locking-based systems: Prevent via write locks that block concurrent reads needed for the modify step
• MVCC systems: May allow (then detect/abort) or prevent through first-committer-wins rules
• Application level: Can also be prevented using optimistic concurrency (version numbers, ETags)

Practical Trade-offs:

Higher isolation prevents more problems but costs more in terms of performance:

Most applications use Read Committed as a reasonable balance. Critical operations may escalate to Serializable for specific transactions.