Read phenomena (dirty reads, phantom reads) in SQL - Deep Dive

Overview - Read phenomena (dirty reads, phantom reads)

What is it?

Read phenomena are unexpected behaviors that happen when multiple database transactions access data at the same time. Dirty reads occur when a transaction reads data that another transaction has changed but not yet saved. Phantom reads happen when a transaction reads a set of rows, and another transaction inserts or deletes rows that change the result if read again. These phenomena can cause confusion and errors in data processing.

Why it matters

Without understanding read phenomena, applications can make decisions based on incomplete or incorrect data, leading to bugs, wrong reports, or financial mistakes. For example, a bank might show a wrong balance if it reads uncommitted changes. Knowing these helps design systems that keep data accurate and trustworthy even when many users work simultaneously.

Where it fits

Before learning read phenomena, you should understand basic database transactions and the concept of isolation levels. After this, you can explore how databases prevent these issues using locking, multiversion concurrency control, and how to choose the right isolation level for your application.

Mental Model

Core Idea

Read phenomena are unexpected data changes seen by a transaction because other transactions are running at the same time and not fully isolated.

Think of it like...

Imagine you and a friend are writing on the same whiteboard. If your friend erases and rewrites parts while you are reading, you might see half-finished or changing messages. Dirty reads are like reading your friend's erased but not finalized notes. Phantom reads are like your friend adding new notes after you looked, changing what you see next time.

┌───────────────┐       ┌───────────────┐
│ Transaction A │       │ Transaction B │
├───────────────┤       ├───────────────┤
│ Read data     │◄──────│ Modify data   │
│ (dirty read)  │       │ (uncommitted) │
└───────────────┘       └───────────────┘

Phantom read example:

Transaction A reads rows matching a condition
Transaction B inserts new rows matching that condition
Transaction A reads again and sees new rows (phantoms)

Build-Up - 6 Steps

1

Foundation

Understanding Transactions and Isolation

Concept: Introduce what a database transaction is and the idea of isolation between transactions.

A transaction is a group of database operations treated as one unit. Isolation means each transaction should not see partial changes from others. Without isolation, data can be inconsistent.

Result

You know that transactions bundle operations and isolation controls how visible changes are between them.

Understanding transactions and isolation is the base to grasp why read phenomena happen.

2

Foundation

What is a Dirty Read?

Concept: Explain dirty reads as reading uncommitted changes from another transaction.

If Transaction A changes data but hasn't saved (committed) yet, and Transaction B reads that changed data, Transaction B sees a dirty read. If Transaction A later cancels (rolls back), Transaction B read data that never officially existed.

Result

Dirty reads can cause Transaction B to act on data that might disappear.

Knowing dirty reads shows how lack of isolation can cause wrong data to be read.

3

Intermediate

What is a Non-Repeatable Read?

🤔Before reading on: do you think reading the same row twice in a transaction always returns the same data? Commit to yes or no.

Concept: Non-repeatable read happens when a transaction reads the same row twice but sees different data because another transaction changed it in between.

Transaction A reads a row. Transaction B updates or deletes that row and commits. Transaction A reads the same row again and sees the new data or finds it missing. This breaks the expectation that data stays stable during a transaction.

Result

Data read twice in one transaction can differ if isolation is weak.

Understanding non-repeatable reads reveals how concurrent updates affect data stability.

4

Intermediate

What is a Phantom Read?

🤔Before reading on: do you think phantom reads affect single rows or sets of rows? Commit to your answer.

Concept: Phantom reads occur when a transaction reads a set of rows matching a condition, and another transaction inserts or deletes rows that change the set on a second read.

Transaction A queries rows where age > 30. Transaction B inserts a new row with age 35 and commits. Transaction A queries again and sees the new row, a phantom that wasn't there before.

Result

The set of rows returned by the same query can change during a transaction.

Phantom reads show how inserts and deletes affect query results beyond single rows.

5

Advanced

Isolation Levels and Read Phenomena

🤔Before reading on: do you think higher isolation levels always prevent all read phenomena? Commit to yes or no.

Concept: Databases offer isolation levels that control which read phenomena can happen. Lower levels allow more concurrency but more phenomena; higher levels reduce phenomena but may slow performance.

Read Uncommitted allows dirty reads. Read Committed prevents dirty reads but allows non-repeatable and phantom reads. Repeatable Read prevents dirty and non-repeatable reads but may allow phantom reads. Serializable prevents all phenomena by fully isolating transactions.

Result

Choosing isolation levels balances data accuracy and system speed.

Knowing isolation levels helps control which read phenomena your application can tolerate.

6

Expert

How Databases Prevent Read Phenomena

🤔Before reading on: do you think locking or versioning is the main method databases use to prevent read phenomena? Commit to your answer.

Concept: Databases use locking or multiversion concurrency control (MVCC) to prevent read phenomena. Locking blocks conflicting operations; MVCC provides snapshots of data to readers.

Locking means transactions wait if others hold locks on data. MVCC lets readers see a consistent snapshot without waiting, improving performance. Different databases use different methods or combinations.

Result

Understanding these methods explains why some databases behave differently under concurrency.

Knowing internal prevention methods clarifies trade-offs between consistency and performance.

Under the Hood

Read phenomena occur because transactions run concurrently and share data. Without strict isolation, one transaction can see changes another made but not finalized. Locking mechanisms block access to data being changed, preventing dirty reads and non-repeatable reads. MVCC creates snapshots of data at transaction start, so reads see a stable view even if others change data later. Phantom reads are harder to prevent because they involve new rows matching a query condition, requiring range locks or serializable isolation.

Why designed this way?

Databases balance correctness and speed. Strict isolation (serializable) prevents all phenomena but slows down many users. Lower isolation levels allow more users to work simultaneously but risk anomalies. MVCC was designed to improve concurrency by avoiding locks for reads, making systems faster while still providing consistent views. Trade-offs depend on application needs.

┌─────────────────────────────┐
│        Transaction A        │
│  ┌───────────────┐          │
│  │ Reads snapshot│◄─────────┤
│  │ of data       │          │
│  └───────────────┘          │
│                             │
│        Transaction B        │
│  ┌───────────────┐          │
│  │ Writes data   │─────────►│
│  │ (locks or MVCC)│          │
│  └───────────────┘          │
└─────────────────────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Do dirty reads only happen if data is permanently saved? Commit yes or no.

Common Belief: Dirty reads happen only when data is saved to the database.

Reality: Dirty reads happen when data is read before it is saved (committed), so the data might be rolled back and never actually saved.

Why it matters: Assuming dirty reads only happen after saving can cause ignoring uncommitted data risks, leading to wrong decisions.

Quick: Do phantom reads affect individual rows or sets of rows? Commit your answer.

Common Belief: Phantom reads are just another form of non-repeatable read on single rows.

Reality: Phantom reads involve changes in the set of rows returned by a query, not just changes to individual rows.

Why it matters: Confusing phantom reads with non-repeatable reads can lead to wrong isolation level choices and data anomalies.

Quick: Does the highest isolation level always guarantee no read phenomena? Commit yes or no.

Common Belief: Serializable isolation always prevents all read phenomena without exceptions.

Reality: Serializable isolation prevents read phenomena but can cause performance issues and deadlocks; some systems implement it with different methods that may allow subtle anomalies.

Why it matters: Believing serializable is perfect can cause ignoring performance trade-offs and unexpected behavior in complex systems.

Expert Zone

1

Some databases implement Repeatable Read differently; for example, MySQL's InnoDB prevents phantom reads by using next-key locking, while others do not.

2

MVCC readers never block writers, but writers may block each other, affecting concurrency in subtle ways.

3

Phantom reads can be prevented by predicate locking, which is complex and rarely used outside serializable isolation.

When NOT to use

Avoid using low isolation levels like Read Uncommitted in financial or critical systems where data accuracy is essential. Instead, use Repeatable Read or Serializable. For high-performance analytics where stale data is acceptable, lower isolation or snapshot isolation may be better.

Production Patterns

In production, many systems use Read Committed for balance, adding application-level checks to handle anomalies. Banking systems often use Serializable or Repeatable Read to avoid errors. Some use optimistic concurrency control to detect conflicts rather than prevent them upfront.

Connections

Concurrency Control

Read phenomena are specific problems that concurrency control techniques aim to solve.

Understanding read phenomena clarifies why concurrency control is essential to keep data consistent when many users work at once.

Version Control Systems

Both databases and version control systems manage concurrent changes and conflicts.

Seeing how version control merges changes helps understand how MVCC provides consistent snapshots despite concurrent edits.

Legal Contract Negotiations

Like transactions, contract negotiations require isolation to avoid acting on incomplete or changing information.

Recognizing that premature decisions on draft contracts cause errors helps appreciate why databases prevent dirty reads.

Common Pitfalls

#1 Reading uncommitted data causing inconsistent results.

Wrong approach:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM accounts WHERE balance > 1000;

Correct approach:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE balance > 1000;

Root cause:

Using the lowest isolation level allows dirty reads, which can cause reading data that might be rolled back.

#2 Assuming repeated reads return the same data without proper isolation.

Wrong approach:

BEGIN TRANSACTION;
SELECT * FROM orders WHERE status = 'pending';
-- some time passes
SELECT * FROM orders WHERE status = 'pending';
COMMIT;

Correct approach:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM orders WHERE status = 'pending';
-- some time passes
SELECT * FROM orders WHERE status = 'pending';
COMMIT;

Root cause:

Not setting isolation level to prevent non-repeatable reads allows data to change between reads.

#3 Ignoring phantom reads in range queries.

Wrong approach:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM employees WHERE salary > 50000;
-- another transaction inserts a new employee with salary 60000
SELECT * FROM employees WHERE salary > 50000;
COMMIT;

Correct approach:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM employees WHERE salary > 50000;
-- phantom inserts blocked
SELECT * FROM employees WHERE salary > 50000;
COMMIT;

Root cause:

Repeatable Read does not prevent phantom reads; only Serializable does.

Key Takeaways

Read phenomena happen because transactions run at the same time and can see each other's partial or changing data.

Dirty reads occur when a transaction reads data another transaction has changed but not saved, risking reading data that might be undone.

Non-repeatable reads happen when data changes between two reads in the same transaction, breaking data stability.

Phantom reads involve changes in the set of rows returned by a query due to inserts or deletes by other transactions.

Isolation levels control which read phenomena can happen, balancing data accuracy and system performance.