Read phenomena (dirty reads, phantom reads) in SQL - Mini Project: Build & Apply

Understanding Read Phenomena: Dirty Reads and Phantom Reads

📖 Scenario: You are working as a junior database administrator for a small online bookstore. Your manager wants you to demonstrate how different read phenomena like dirty reads and phantom reads can occur in SQL transactions.

🎯 Goal: Build a simple SQL setup with a books table and simulate transactions that show dirty reads and phantom reads.

📋 What You'll Learn

Create a books table with columns id (integer), title (text), and price (decimal).

Insert three specific book records into the books table.

Set the transaction isolation level to READ UNCOMMITTED to demonstrate dirty reads.

Write a transaction that reads uncommitted data from another transaction.

Write a transaction that demonstrates phantom reads by inserting a new row during a repeatable read.

💡 Why This Matters

🌍 Real World

Understanding read phenomena helps database developers and administrators ensure data consistency and correctness in multi-user environments.

💼 Career

Knowledge of transaction isolation levels and read phenomena is essential for roles like database administrator, backend developer, and data engineer.

1

Create the books table and insert initial data

Create a table called books with columns id (integer), title (text), and price (decimal). Then insert these three rows exactly: (1, 'Learn SQL', 29.99), (2, 'Database Basics', 39.99), and (3, 'Advanced SQL', 49.99).

SQL

-- Create the books table and insert three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set transaction isolation level to READ UNCOMMITTED

Write a SQL statement to set the transaction isolation level to READ UNCOMMITTED so that dirty reads are allowed.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    title TEXT,
    price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99),
(3, 'Advanced SQL', 49.99);

-- Set transaction isolation level to READ UNCOMMITTED
-- Your code here

Hint

Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; to allow dirty reads.

3

Write a transaction to demonstrate a dirty read

Write a SQL transaction that updates the price of the book with id = 1 to 19.99 but does not commit yet. Then write another transaction that reads the price of the book with id = 1 while the first transaction is still open, showing a dirty read.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    title TEXT,
    price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99),
(3, 'Advanced SQL', 49.99);

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Begin first transaction to update price but do not commit
-- Begin second transaction to read price showing dirty read
-- Your code here

Hint

Use BEGIN TRANSACTION; to start transactions and do not commit the first update before reading.

4

Demonstrate phantom reads with repeatable read isolation

Set the transaction isolation level to REPEATABLE READ. Then write a transaction that selects all books with price less than 40. Before committing, write another transaction that inserts a new book with price 35.00. Finally, repeat the select in the first transaction to show the phantom read.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    title TEXT,
    price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99),
(3, 'Advanced SQL', 49.99);

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Transaction 1: Update price but do not commit
BEGIN TRANSACTION;
UPDATE books SET price = 19.99 WHERE id = 1;
-- Transaction 2: Read price showing dirty read
BEGIN TRANSACTION;
SELECT price FROM books WHERE id = 1;
-- Note: Transaction 1 is still open, so this read is dirty

-- Now demonstrate phantom reads
-- Your code here

Hint

Use SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; and transactions with selects and inserts to show phantom reads.

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