Skip to main content
MySQL · lesson 4 of 8

Beginner

1

Intermediate

6

Advanced

1

Also part of MySQL

1
Open the MySQL page →
MySQL intermediate Lesson 4 of 8

Also part of: Databases

Isolation Levels: The Same Transaction Reading Two Different Values

Under MySQL's default REPEATABLE READ, a transaction reads the same value twice even after another session commits a change in between. Switch to READ COMMITTED and the second read sees the new value — a real, measured non-repeatable read.

By SkillByExample · Published

mysql transactions isolation-levels repeatable-read read-committed

The same query, run twice inside the same transaction — one isolation level guarantees they match, the other doesn’t.

REPEATABLE READ (the default): two reads, same answer

-- Session A
START TRANSACTION;
SELECT balance AS first_read FROM accounts WHERE id = 2;
first_read
300.00
-- Session B, while session A's transaction is still open
UPDATE accounts SET balance = 999.00 WHERE id = 2;
-- back in session A, same still-open transaction
SELECT balance AS second_read_same_txn FROM accounts WHERE id = 2;
COMMIT;
second_read_same_txn
300.00

Session B’s update genuinely committed — a fresh connection querying accounts right now would see 999.00. But session A’s transaction reads 300.00 both times, because REPEATABLE READ (MySQL’s default) serves every plain read in a transaction from the same consistent snapshot, taken when the transaction’s first read happened.

READ COMMITTED: the same setup, a different answer the second time

-- Session A
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance AS first_read FROM accounts WHERE id = 2;
first_read
300.00
-- Session B commits the same change while A's transaction is open
UPDATE accounts SET balance = 999.00 WHERE id = 2;
-- back in session A, same transaction
SELECT balance AS second_read_same_txn FROM accounts WHERE id = 2;
COMMIT;
second_read_same_txn
999.00

Same transaction, same query, run twice — and this time the second read sees 999.00, the value session B committed in between. READ COMMITTED takes a fresh snapshot for every individual statement rather than one snapshot for the whole transaction, so a commit that happens between two reads becomes visible to the second one. This is a real non-repeatable read: the same row, read twice in one transaction, disagreeing with itself.

Practice

1. Check your current session's isolation level directly. 
SELECT @@transaction_isolation;
@@transaction_isolation
REPEATABLE-READ

REPEATABLE-READ is MySQL’s compiled-in default — every new connection starts here unless a session or the server config sets something else.

2. Confirm a fresh connection (not inside either transaction) always sees the current committed value, regardless of isolation level. 
-- a brand-new connection, no open transaction
SELECT balance FROM accounts WHERE id = 2;
balance
999.00

Isolation levels only change what an already-open transaction sees across multiple reads — a fresh, standalone query with no transaction of its own always reads the latest committed value, under either isolation level. The snapshot behavior only kicks in once START TRANSACTION has begun one.

3. Reset the session back to the default and confirm the guarantee returns. 
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT balance AS first_read FROM accounts WHERE id = 2;
-- (another session updates and commits to 1500.00 here)
SELECT balance AS second_read FROM accounts WHERE id = 2;
COMMIT;
first_read
999.00
second_read
999.00

Back to matching reads, even though a concurrent session committed 1500.00 in between (a fresh connection now sees that value) — SET SESSION changes are session-scoped and don’t persist as a new server default, so explicitly resetting it is the reliable way to return to REPEATABLE READ behavior within the same long-lived connection.

Next: locking and deadlocks — a real blocked write, and a real deadlock with the exact error MySQL raises.

Frequently Asked Questions

What does REPEATABLE READ actually guarantee?
Every plain (non-locking) read within one transaction sees the same consistent snapshot of the data, taken at the start of that transaction's first read — no matter what other transactions commit in the meantime. That's why this lesson's transaction read 300.00 twice even though another session committed 999.00 in between: the second read was served from the same snapshot as the first, not from the current committed state.
What does READ COMMITTED guarantee instead?
Each individual statement gets its own fresh snapshot, taken at the moment that statement runs — so two reads of the same row in one transaction can return different values if another transaction committed a change in between them. This is what this lesson calls a non-repeatable read: the same query, run twice in the same transaction, returning two different answers.
Why is MySQL's default REPEATABLE READ when other databases (like PostgreSQL) default to READ COMMITTED?
It's a genuine design tradeoff, not a right-or-wrong answer — REPEATABLE READ gives a transaction a consistent view of the world for its whole duration, which is often what report-style or multi-step logic actually wants, at the cost of not seeing concurrent commits until the next transaction starts. READ COMMITTED gives fresher data per statement, at the cost of a transaction's own logic potentially seeing inconsistent in-between states across multiple reads.
How do I check or change a session's isolation level?
SELECT @@transaction_isolation reads the current session's level. SET SESSION transaction_isolation = 'READ-COMMITTED' changes it for the current session going forward; the SQL-standard SET TRANSACTION ISOLATION LEVEL READ COMMITTED syntax is also available and is meant to apply to the next transaction, though setting the session variable directly is the more reliably observable method to confirm from a script.
Is a non-repeatable read always a bug in application logic?
Not always — plenty of code only ever reads a value once per transaction, where the distinction never matters. It becomes a real bug specifically when logic reads the same row twice and assumes both reads agree (a total computed from an early read, then re-validated against a later read of the same row) — that's exactly the shape of bug REPEATABLE READ prevents and READ COMMITTED permits.