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

Need a 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

Need a 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

Need a 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

Need a hint?

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