Dirty, non-repeatable and phantom reads
45 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)Two clerks use the same database at the same moment, and each one's work is correct alone. Yet a total comes out wrong, or a number changes while being read. What goes wrong?
Two sessions at once in two terminals
A concurrency anomaly is a wrong result that appears only when two transactions run at the same time, because their steps mix in an unlucky order. Each transaction is correct on its own.
To see an anomaly you need two sessions working at once: two terminal windows on the same database. This course calls them Session A and Session B.
# Terminal 1: Session Amysql -u root -p practice# Terminal 2: Session Bmysql -u root -p practiceEach demo is a list of numbered steps. Run step 1 in the window it names, then step 2 in the window it names, and so on, strictly in order. The order is the whole point: the same statements in a different order give a different result.
When a step seems to hang with no new prompt, it is waiting for a lock held by the other session. It continues by itself as soon as the other session commits or rolls back. The output panels mark such steps with "waited until step N".
Example 1 · Two windows, two sessions
-- Step 1 · Session ASELECT CONNECTION_ID() AS my_id, @@transaction_isolation AS level;
-- Step 2 · Session BSELECT CONNECTION_ID() AS my_id, @@transaction_isolation AS level;-- Step 1 · Session A +-------+-----------------+ | my_id | level | +-------+-----------------+ | 4424 | REPEATABLE-READ | +-------+-----------------+ -- Step 2 · Session B +-------+-----------------+ | my_id | level | +-------+-----------------+ | 4425 | REPEATABLE-READ | +-------+-----------------+
What this code does
CONNECTION_ID()returns the id the server gave this connection. Two different ids prove that the windows are two separate sessions; your numbers will differ.- Both sessions start at
REPEATABLE-READ, MySQL's default isolation level.
Dirty reads
A dirty read happens when a transaction reads a change that another transaction has not committed yet. If that transaction rolls back, the reader has used a value that never officially existed.
An admin's transaction cuts the iPhone price to Rs. 59,900, and the shop page reads it before the admin cancels. The customer sees a price the shop never offered.
Example 2 · A dirty read of a price that never existed
-- Step 1 · Session BSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Step 2 · Session A: an admin starts a price cutSTART TRANSACTION;UPDATE products SET price = 59900.00 WHERE product_id = 3;
-- Step 3 · Session B: the shop page reads the priceSELECT product_id, name, price FROM products WHERE product_id = 3;
-- Step 4 · Session A: the admin cancelsROLLBACK;
-- Step 5 · Session BSELECT product_id, name, price FROM products WHERE product_id = 3;-- Step 3 · Session B +------------+-----------+----------+ | product_id | name | price | +------------+-----------+----------+ | 3 | iPhone 15 | 59900.00 | +------------+-----------+----------+ -- Step 5 · Session B +------------+-----------+----------+ | product_id | name | price | +------------+-----------+----------+ | 3 | iPhone 15 | 69900.00 | +------------+-----------+----------+
What this code does
- In step 3, B reads 59900.00, a change A has not committed. After A rolls back, the price is 69900.00 again: B used a value that was never saved.
- With the default level, step 3 would show 69900.00. Run
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;in B afterwards, or close the window.
MySQL's default settings never allow dirty reads. To see one, Session B lowers its isolation level, the setting that decides how much a transaction sees of other transactions' work, to READ UNCOMMITTED. The next lesson covers all four levels.
Predict the output
Predict the dirty read
Session A has not committed. What does the SELECT in Session B return?
Non-repeatable reads
A non-repeatable read happens when a transaction reads the same row twice and gets different values, because another transaction changed that row and committed in between.
A billing transaction checks the stock, then reads it again to print the invoice, and the two numbers disagree.
Example 3 · A non-repeatable read of the stock
-- Step 1 · Session BSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;SELECT product_id, name, stock FROM products WHERE product_id = 3;
-- Step 2 · Session A: a stock correction, committed at onceUPDATE products SET stock = 5 WHERE product_id = 3;
-- Step 3 · Session B: the same query, same transactionSELECT product_id, name, stock FROM products WHERE product_id = 3;COMMIT;-- Step 1 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 8 | +------------+-----------+-------+ -- Step 3 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 5 | +------------+-----------+-------+
What this code does
- Inside one transaction, B reads the stock as 8 and then as 5. A's UPDATE ran with autocommit on, so it was committed between B's two reads.
- At READ COMMITTED, every SELECT sees the newest committed data, so a repeated read can change.
A decision made on the first read may now be wrong. A non-repeatable read appears at the READ COMMITTED level, where every SELECT sees the latest committed data. MySQL's default level, REPEATABLE READ, prevents it for plain SELECTs, as the next lesson shows.
Phantom reads and lost updates
A phantom read is a new matching row that appears when the same search runs twice, because another transaction inserted it. A lost update is when two transactions overwrite each other, so one change disappears with no error.
Two more problems: a new row that appears between two searches, and two buyers whose updates overwrite each other.
Example 4 · A phantom order appears
-- Step 1 · Session BSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;SELECT COUNT(*) AS placed_orders FROM orders WHERE status = 'PLACED';
-- Step 2 · Session A: a new order arrivesINSERT INTO orders (customer_id, order_date) VALUES (3, '2025-06-02');
-- Step 3 · Session BSELECT COUNT(*) AS placed_orders FROM orders WHERE status = 'PLACED';COMMIT;-- Step 1 · Session B +---------------+ | placed_orders | +---------------+ | 3 | +---------------+ -- Step 3 · Session B +---------------+ | placed_orders | +---------------+ | 4 | +---------------+
What this code does
- B's same search finds 3 placed orders, then 4: the new row is a phantom. No existing row changed; a new one joined the result.
- A report that counts orders and then lists them in one transaction could print a count that does not match its own list.
Example 5 · A lost update: two tickets sold, one seat counted
-- Setup, once, in either session:DROP TABLE IF EXISTS train_quota;CREATE TABLE train_quota ( train_no INT PRIMARY KEY, train_name VARCHAR(40) NOT NULL, seats_left INT NOT NULL);INSERT INTO train_quota (train_no, train_name, seats_left) VALUES (12627, 'Karnataka Express', 10);
-- Step 1 · Session A: booking 1 reads the seatsSTART TRANSACTION;SELECT seats_left FROM train_quota WHERE train_no = 12627;
-- Step 2 · Session B: booking 2 reads the seatsSTART TRANSACTION;SELECT seats_left FROM train_quota WHERE train_no = 12627;
-- Step 3 · Session A: the app computed 10 - 1 = 9UPDATE train_quota SET seats_left = 9 WHERE train_no = 12627;COMMIT;
-- Step 4 · Session B: the app also computed 10 - 1 = 9UPDATE train_quota SET seats_left = 9 WHERE train_no = 12627;COMMIT;SELECT seats_left FROM train_quota WHERE train_no = 12627;-- Step 1 · Session A +------------+ | seats_left | +------------+ | 10 | +------------+ -- Step 2 · Session B +------------+ | seats_left | +------------+ | 10 | +------------+ -- Step 4 · Session B +------------+ | seats_left | +------------+ | 9 | +------------+
What this code does
- Both bookings read 10 and wrote 9, so two tickets were sold but only one seat was counted. No error appeared, which is what makes lost updates dangerous.
- This ran at MySQL's default level, REPEATABLE READ: isolation levels alone do not stop a lost update when the new value is computed in the application.
Example 6 · The fix for a counter: let MySQL do the arithmetic
-- Setup, once, in either session:DROP TABLE IF EXISTS train_quota;CREATE TABLE train_quota ( train_no INT PRIMARY KEY, train_name VARCHAR(40) NOT NULL, seats_left INT NOT NULL);INSERT INTO train_quota (train_no, train_name, seats_left) VALUES (12627, 'Karnataka Express', 10);
-- Step 1 · Session ASTART TRANSACTION;UPDATE train_quota SET seats_left = seats_left - 1 WHERE train_no = 12627;
-- Step 2 · Session BSTART TRANSACTION;UPDATE train_quota SET seats_left = seats_left - 1 WHERE train_no = 12627;
-- Step 3 · Session ACOMMIT;
-- Step 4 · Session BCOMMIT;SELECT seats_left FROM train_quota WHERE train_no = 12627;-- Step 2 · Session B (waited until step 3) -- Step 4 · Session B +------------+ | seats_left | +------------+ | 8 | +------------+
What this code does
- A's UPDATE locks the row until A commits, so B's UPDATE in step 2 waits. When A commits, B's UPDATE reads the newest value, 9, and writes 8.
- Two bookings, two seats: 10 - 2 = 8. The calculation happens inside the UPDATE, so no value read earlier can go stale.
The new row in a phantom read is the "phantom". Unlike a non-repeatable read, the set of rows changes, not a value inside a row. A lost update comes from two transactions that read the same value, compute a new one from it in the application and write it back; the second write overwrites the first. This one happens even at MySQL's default level.
The simplest fix for a counter is to let the database do the arithmetic: SET seats_left = seats_left - 1, not a number computed earlier. The lesson on SELECT ... FOR UPDATE shows the general fix.
Next step
Check your understanding
12 quick questions