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.