Read phenomena (dirty reads, phantom reads) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What output shows a dirty read scenario?

Consider two transactions:

Transaction 1 updates a row but does not commit.
Transaction 2 reads the updated row before commit.

Which query result best illustrates the dirty read phenomenon?

SQL

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- no commit yet

-- In parallel
SELECT balance FROM accounts WHERE id = 1;

A Transaction 2 reads the old balance before update.

B Transaction 2 reads a balance from a different account.

C Transaction 2 waits until Transaction 1 commits before reading.

D Transaction 2 reads the new balance even though Transaction 1 has not committed.

💡 Hint

Dirty reads happen when a transaction reads uncommitted changes from another transaction.

A dirty read occurs when a transaction reads data that has been modified by another transaction but not yet committed. Option D describes this situation.

❓ query_result

intermediate

Which output best illustrates a phantom read?

Transaction 1 runs a query to count rows matching a condition.
Transaction 2 inserts a new row matching that condition and commits.
Transaction 1 runs the same query again.

What is the expected result?

SQL

BEGIN TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- returns 5

-- In parallel
INSERT INTO orders (id, status) VALUES (101, 'pending');
COMMIT;

-- Back in Transaction 1
SELECT COUNT(*) FROM orders WHERE status = 'pending';

A First query returns 5, second query returns 6 rows.

B Both queries return 5 rows.

C Both queries return 6 rows.

D Second query returns fewer rows than the first.

💡 Hint

Phantom reads happen when new rows appear in repeated queries within the same transaction.

Phantom reads occur when a transaction re-executes a query and sees new rows inserted by another committed transaction. Here, the count changes from 5 to 6.

🧠 Conceptual

advanced

Which isolation level prevents dirty reads but allows phantom reads?

Choose the SQL transaction isolation level that stops dirty reads but still permits phantom reads.

A READ UNCOMMITTED

B SERIALIZABLE

C READ COMMITTED

D REPEATABLE READ

💡 Hint

Think about which isolation level blocks reading uncommitted data but does not lock range scans.

READ COMMITTED prevents dirty reads by only reading committed data but does not prevent phantom reads because new rows can appear in repeated queries.

📝 Syntax

advanced

Which SQL snippet correctly sets the isolation level to prevent phantom reads?

You want to set the transaction isolation level to SERIALIZABLE to avoid phantom reads.
Which SQL command is correct?

A SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B SET ISOLATION LEVEL TRANSACTION SERIALIZABLE;

C SET TRANSACTION LEVEL ISOLATION SERIALIZABLE;

D SET ISOLATION SERIALIZABLE TRANSACTION LEVEL;

💡 Hint

The correct syntax follows the pattern: SET TRANSACTION ISOLATION LEVEL ...

Option A uses the correct standard SQL syntax to set the isolation level to SERIALIZABLE.

🔧 Debug

expert

Why does this transaction still experience phantom reads despite using REPEATABLE READ?

Transaction 1:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM products WHERE category = 'books';
-- returns 10 rows

Transaction 2:
INSERT INTO products (id, category) VALUES (101, 'books');
COMMIT;

Transaction 1:
SELECT * FROM products WHERE category = 'books';
-- returns 11 rows

Why does Transaction 1 see the new row?

A The isolation level was not set correctly before the transaction started.

B REPEATABLE READ does not prevent phantom reads in all databases.

C Transaction 1 did not lock the table explicitly.

D The new row was inserted with a different category.

💡 Hint

Some databases implement REPEATABLE READ differently regarding phantom reads.

In some database systems like MySQL InnoDB, REPEATABLE READ prevents non-repeatable reads but phantom reads can still occur because it does not lock range scans fully.