Read phenomena (dirty reads, phantom reads) in SQL - Step-by-Step Execution

Concept Flow - Read phenomena (dirty reads, phantom reads)

Transaction 1 starts

↓

Transaction 1 reads data

↓

Transaction 2 starts

↓

Transaction 2 modifies data

↓

Transaction 1 reads again

↓

Check for read phenomena

↓

Dirty Read?

↓

Phantom Read?

↓

Transactions commit or rollback

This flow shows how two transactions interact and how read phenomena like dirty reads and phantom reads can occur when one transaction reads data affected by another uncommitted or committed transaction.

Execution Sample

SQL

BEGIN TRANSACTION T1;
SELECT * FROM Orders WHERE CustomerID = 1;
-- Transaction 2 inserts a new order for CustomerID=1 but does not commit
BEGIN TRANSACTION T2;
INSERT INTO Orders (OrderID, CustomerID) VALUES (103, 1);
-- Transaction 1 reads again
SELECT * FROM Orders WHERE CustomerID = 1;
COMMIT TRANSACTION T1;

Transaction 1 reads orders for CustomerID=1, then Transaction 2 inserts a new order but does not commit, then Transaction 1 reads again to see if new data appears (phantom read scenario).

Execution Table

Step	Transaction	Action	Data Read/Modified	Result/Effect
1	T1	Start transaction	No data changed	Transaction 1 begins
2	T1	Read orders for CustomerID=1	Orders: [OrderID 101, OrderID 102]	Reads existing orders
3	T2	Start transaction	No data changed	Transaction 2 begins
4	T2	Insert new order OrderID 103 for CustomerID=1	Orders: +OrderID 103 (uncommitted)	New order inserted but uncommitted
5	T1	Read orders for CustomerID=1 again	Orders: [OrderID 101, OrderID 102]	Does NOT see uncommitted order (no dirty read)
6	T2	Commit transaction	Orders: +OrderID 103 committed	New order now visible to others
7	T1	Read orders for CustomerID=1 again	Orders: [OrderID 101, OrderID 102, OrderID 103]	Sees new order (phantom read)
8	T1	Commit transaction	No data changed	Transaction 1 ends

💡 Transaction 1 ends after commit; phantom read observed at step 7; no dirty read occurred because uncommitted data was not read.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	After Step 6	After Step 7	Final
Orders for CustomerID=1	[]	[101, 102]	[101, 102, 103 (uncommitted)]	[101, 102]	[101, 102, 103 (committed)]	[101, 102, 103 (committed)]	[101, 102, 103 (committed)]

Key Moments - 2 Insights

Why does Transaction 1 not see the new order inserted by Transaction 2 at step 5?

Because Transaction 2 has not committed yet, so the new order is uncommitted. Most isolation levels prevent dirty reads, so Transaction 1 cannot see uncommitted changes (see execution_table step 5).

What causes the phantom read observed at step 7?

After Transaction 2 commits the new order, Transaction 1 reads again and sees the new row that was not there before. This new row appearing is called a phantom read (see execution_table step 7).

Visual Quiz - 3 Questions

Look at the execution_table at step 5. What orders does Transaction 1 see?

A [OrderID 101, OrderID 102]

B [OrderID 101, OrderID 102, OrderID 103]

C No orders

D [OrderID 103 only]

💡 Hint

Check the 'Data Read/Modified' column at step 5 in execution_table.

At which step does Transaction 1 observe a phantom read?

A Step 2

B Step 5

C Step 7

D Step 4

💡 Hint

Look for when new rows appear in Transaction 1's read results in execution_table.

If Transaction 2 did not commit, what would Transaction 1 see at step 7?

A [OrderID 101, OrderID 102, OrderID 103]

B [OrderID 101, OrderID 102]

C No orders

D [OrderID 103 only]

💡 Hint

Refer to variable_tracker and execution_table steps 5 and 6 about committed vs uncommitted data.

Concept Snapshot

Read Phenomena in SQL Transactions:
- Dirty Read: Reading uncommitted data from another transaction.
- Phantom Read: New rows appear in repeated reads within the same transaction.
- Isolation levels control visibility of uncommitted/committed data.
- Dirty reads are prevented in most isolation levels.
- Phantom reads occur when new rows are committed by others during a transaction.

Full Transcript

This visual execution trace shows how two transactions interact in a database and how read phenomena like dirty reads and phantom reads occur. Transaction 1 starts and reads orders for a customer. Transaction 2 starts, inserts a new order but does not commit yet. Transaction 1 reads again but does not see the uncommitted new order, so no dirty read occurs. After Transaction 2 commits, Transaction 1 reads again and sees the new order, demonstrating a phantom read. The variable tracker shows the orders visible to Transaction 1 at each step. Key moments clarify why uncommitted data is invisible and how phantom reads happen when new rows appear after commit. The quiz questions test understanding of these steps and phenomena.

Practice

1. Which of the following best describes a dirty read in database transactions?

easy

A. Reading data that has been modified but not yet committed by another transaction.

B. Seeing new rows appear in a query result after re-running the same query.

C. Reading only committed data and never uncommitted changes.

D. Locking rows to prevent other transactions from reading them.

Solution

1. Step 1: Understand dirty read definition

   A dirty read happens when a transaction reads data that another transaction has changed but not committed yet.

2. Step 2: Compare options to definition

   Reading data that has been modified but not yet committed by another transaction. matches this exactly. Seeing new rows appear in a query result after re-running the same query. describes phantom reads, C describes a safe read, and D is about locking, not dirty reads.

3. Final Answer:

   Reading data that has been modified but not yet committed by another transaction. -> Option A

4. Quick Check:

   Dirty read = reading uncommitted data [OK]

Hint: Dirty read = reading uncommitted changes [OK]

Common Mistakes:

• Confusing dirty read with phantom read
• Thinking dirty read only reads committed data
• Mixing locking with read phenomena

2. Which SQL transaction isolation level allows dirty reads?

easy

A. SERIALIZABLE

B. REPEATABLE READ

C. READ COMMITTED

D. READ UNCOMMITTED

Solution

1. Step 1: Recall isolation levels and dirty reads

   Only the READ UNCOMMITTED isolation level allows dirty reads, meaning it can read uncommitted changes.

2. Step 2: Match options to isolation levels

   READ COMMITTED and higher levels prevent dirty reads. SERIALIZABLE is the strictest. So, READ UNCOMMITTED is correct.

3. Final Answer:

   READ UNCOMMITTED -> Option D

4. Quick Check:

   Dirty reads allowed only in READ UNCOMMITTED [OK]

Hint: Dirty reads only happen at READ UNCOMMITTED level [OK]

Common Mistakes:

• Choosing READ COMMITTED as allowing dirty reads
• Confusing phantom reads with dirty reads
• Not knowing isolation level hierarchy

3. Consider two transactions running concurrently on a table Orders. Transaction 1 reads all rows where status = 'pending'. Transaction 2 inserts a new row with status = 'pending' and commits. If Transaction 1 repeats the read, what phenomenon might it observe?

medium

A. Dirty read

B. Non-repeatable read

C. Phantom read

D. Lost update

Solution

1. Step 1: Analyze the scenario with inserted rows

   Transaction 1 reads rows with status 'pending'. Transaction 2 inserts a new 'pending' row and commits.

2. Step 2: Identify the phenomenon on repeated read

   When Transaction 1 reads again, it sees the new row that wasn't there before. This is a phantom read, where new rows appear between reads.

3. Final Answer:

   Phantom read -> Option C

4. Quick Check:

   New rows appearing = Phantom read [OK]

Hint: New rows between reads = phantom read [OK]

Common Mistakes:

• Confusing phantom read with dirty read
• Thinking non-repeatable read applies to new rows
• Mixing lost update with read phenomena

4. A developer wrote this SQL transaction code but observes inconsistent reads. Identify the error related to read phenomena:

BEGIN TRANSACTION;
SELECT * FROM Products WHERE price > 100;
-- Another transaction inserts a new product with price 150 and commits
SELECT * FROM Products WHERE price > 100;
COMMIT;

What is the likely cause?

medium

A. Using READ UNCOMMITTED isolation level causing dirty reads

B. Using READ COMMITTED isolation level causing phantom reads

C. Using SERIALIZABLE isolation level causing lost updates

D. Using REPEATABLE READ isolation level causing non-repeatable reads

Solution

1. Step 1: Understand the scenario with repeated SELECTs

   The transaction reads products with price > 100 twice. Between reads, another transaction inserts a new matching row and commits.

2. Step 2: Identify the read phenomenon

   Because the isolation level is likely READ COMMITTED, the second read sees the new row, causing a phantom read.

3. Final Answer:

   Using READ COMMITTED isolation level causing phantom reads -> Option B

4. Quick Check:

   Phantom reads occur at READ COMMITTED [OK]

Hint: Phantom reads happen at READ COMMITTED level [OK]

Common Mistakes:

• Assuming dirty reads cause this behavior
• Confusing lost updates with read phenomena
• Thinking REPEATABLE READ allows phantom reads

5. You want to prevent both dirty reads and phantom reads in your application. Which transaction isolation level should you choose?

hard

A. SERIALIZABLE

B. READ COMMITTED

C. REPEATABLE READ

D. READ UNCOMMITTED

Solution

1. Step 1: Review isolation levels and phenomena prevented

   READ UNCOMMITTED allows dirty reads. READ COMMITTED prevents dirty reads but allows phantom reads. REPEATABLE READ prevents dirty and non-repeatable reads but may allow phantom reads.

2. Step 2: Identify level preventing both dirty and phantom reads

   SERIALIZABLE is the strictest level and prevents dirty reads, non-repeatable reads, and phantom reads by fully isolating transactions.

3. Final Answer:

   SERIALIZABLE -> Option A

4. Quick Check:

   SERIALIZABLE prevents dirty and phantom reads [OK]

Hint: Use SERIALIZABLE to prevent dirty and phantom reads [OK]

Common Mistakes:

• Choosing REPEATABLE READ thinking it blocks phantom reads
• Confusing READ COMMITTED with preventing all read anomalies
• Ignoring performance impact of SERIALIZABLE