Read phenomena (dirty reads, phantom reads) in SQL

Introduction

Read phenomena happen when database transactions see data changes in unexpected ways. Understanding them helps keep data correct and reliable.

When multiple people or programs change data at the same time.

When you want to know why your query shows different results if run twice quickly.

When you want to avoid errors caused by reading unfinished changes.

When designing how your database handles many users safely.

When debugging strange data results in your application.

Syntax

SQL

-- No specific syntax, but these phenomena happen during transactions
BEGIN TRANSACTION;
-- Read or write queries here
COMMIT;

Read phenomena occur during transactions when isolation levels are low.

You control them by setting transaction isolation levels like READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, or SERIALIZABLE.

Examples

Transaction 2 reads data that Transaction 1 changed but did not save permanently yet.

SQL

-- Dirty Read example
-- Transaction 1 updates a row but does not commit
BEGIN TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Transaction 2 reads the updated balance before commit
SELECT balance FROM accounts WHERE id = 1;
-- If Transaction 1 rolls back, Transaction 2 read wrong data (dirty read)

Transaction 1 sees new rows appear between reads because another transaction added them.

SQL

-- Phantom Read example
-- Transaction 1 reads rows matching a condition
BEGIN TRANSACTION;
SELECT * FROM orders WHERE status = 'pending';
-- Transaction 2 inserts a new 'pending' order and commits
BEGIN TRANSACTION;
INSERT INTO orders (id, status) VALUES (101, 'pending');
COMMIT;
-- Transaction 1 reads again and sees new row (phantom)

Sample Program

This shows a dirty read if Transaction 2 reads the updated price before Transaction 1 commits or rolls back.

SQL

-- Setup sample table
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(20), price INT);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 20);

-- Transaction 1 starts
BEGIN TRANSACTION;
UPDATE products SET price = 15 WHERE id = 1;
-- Transaction 2 reads before Transaction 1 commits
SELECT price FROM products WHERE id = 1;
-- Transaction 1 rolls back
ROLLBACK;

Important Notes

Dirty reads happen when you read data that another transaction changed but not yet saved.

Phantom reads happen when new rows appear or disappear between reads in the same transaction.

Using higher isolation levels can prevent these phenomena but may slow down the database.

Summary

Read phenomena cause unexpected data views during concurrent transactions.

Dirty reads read uncommitted changes; phantom reads see new or missing rows between reads.

Control these by choosing the right transaction isolation level.