READ COMMITTED vs REPEATABLE READ in MySQL
Under one isolation level, a transaction reads 50 twice while another session has already saved 40. Under the other, its second read says 40. Neither is a bug, and the right choice depends on what your code does with the number. Here are the two levels running side by side, and what a write does under both.
Prerequisites
- MySQL, and two sessions. The examples use MySQL 9.7.4 in Docker, and the
mysqlclient from How to Connect to MySQL in a Docker Container. - An InnoDB table. The examples use
products, and a row with a quantity of50.
REPEATABLE READ: You Keep Your First Answer
This is MySQL's default. Open a transaction, and read:
START TRANSACTION;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 50 |
+----+-------------+----------------+
1 row in set (0.001 sec)
Someone else changes it, and their change is committed at once:
UPDATE products SET QuantityOnHand = 40 WHERE Id = 3;
Read again in the first transaction:
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 50 |
+----+-------------+----------------+
1 row in set (0.001 sec)
Still 50. MySQL's isolation levels page says: "Consistent reads within the same transaction read the snapshot established by the first read."
READ COMMITTED: Every Read Is Fresh
The same story, starting from 50 again:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 50 |
+----+-------------+----------------+
1 row in set (0.000 sec)
UPDATE products SET QuantityOnHand = 40 WHERE Id = 3;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 40 |
+----+-------------+----------------+
1 row in set (0.000 sec)
40. The page says of this level: "Each consistent read, even within the same transaction, sets and reads its own fresh snapshot."
Writes Don't Use the Snapshot, Under Either Level
Go back to REPEATABLE READ, with the transaction holding its snapshot of 50 and the row really at 40. A locking read shows a different number from a plain one, in the same transaction:
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3 FOR UPDATE;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 40 |
+----+-------------+----------------+
1 row in set (0.001 sec)
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 50 |
+----+-------------+----------------+
1 row in set (0.002 sec)
An UPDATE works from the real value too:
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 39 |
+----+-------------+----------------+
1 row in set (0.002 sec)
39, which is 40 - 1, not 50 - 1. The consistent read page warns about exactly this: "The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements."
This is what keeps a statement such as QuantityOnHand = QuantityOnHand - 1 correct. It's also why code that reads a value and writes a computed number is unsafe under both levels: Fixed: Two Requests Buy the Last Item in EF Core shows it going wrong.
When the Snapshot Starts
The snapshot starts at the first read, not at START TRANSACTION:
START TRANSACTION;
UPDATE products SET QuantityOnHand = 41 WHERE Id = 3;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 41 |
+----+-------------+----------------+
1 row in set (0.001 sec)
It saw the 41, because it hadn't read anything yet. To fix the snapshot at the start, use START TRANSACTION WITH CONSISTENT SNAPSHOT:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
UPDATE products SET QuantityOnHand = 42 WHERE Id = 3;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 41 |
+----+-------------+----------------+
1 row in set (0.001 sec)
Which One to Use
| REPEATABLE READ | READ COMMITTED | |
|---|---|---|
| Default in MySQL | Yes | No |
| Two reads in one transaction | The same answer | Each sees the newest committed data |
| Good for | Reports that must add up across several queries | Long reads that should see recent data |
| Gap locks | Yes: How Gap Locks Block an INSERT | Rarely |
UPDATE and FOR UPDATE | Read the newest value | Read the newest value |
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Reading a value, then writing a number computed from it | Another session's change is overwritten | Let the UPDATE compute it, or lock with FOR UPDATE |
| Assuming a transaction sees others' changes | Under REPEATABLE READ, it doesn't | Commit and read again, or use a locking read |
Assuming START TRANSACTION fixes the snapshot | It's fixed at the first read | Use WITH CONSISTENT SNAPSHOT |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
A SELECT keeps returning an old value | The transaction is still using its snapshot | Commit, then read again |
A plain SELECT and a FOR UPDATE disagree | The first reads the snapshot, and the second the newest value | Decide which you need |
An INSERT waits, with no row in conflict | A gap lock | Use READ COMMITTED, or look at the lock |
FAQ
Does READ COMMITTED make it safe to read, then write?
No. Between your read and your write, someone else can still change the row. Lock it, or put the check in the UPDATE: How to Reduce a Quantity Only If There Is Enough in MySQL.
Conclusion
Under REPEATABLE READ, your plain reads keep the first answer, and under READ COMMITTED each one sees the newest. Under both, an UPDATE and a FOR UPDATE read the real, latest row. So the level decides what your SELECT shows you, and never makes a read followed by a write safe.
Additional Resources
- Transaction Isolation Levels — all four levels.
- Consistent Nonlocking Reads — how snapshots work.
