Transaction isolation level: dirty read or phantom
Quick summary
Your transaction isolation level is too low — concurrent transactions can read uncommitted data or see phantom rows.
Why this happens
Read Uncommitted isolation allows dirty reads where uncommitted changes from other transactions are visible
Read Committed isolation allows non-repeatable reads and phantom rows within the same transaction
The default isolation level is too low for the consistency requirements of the operation
Minimal example
// Default isolation (Read Committed) — non-repeatable read
const tx = await db.beginTransaction();
const balance1 = await tx.query('SELECT balance FROM accounts WHERE id = 1');
// Another transaction commits a change to balance here
const balance2 = await tx.query('SELECT balance FROM accounts WHERE id = 1');
// balance1 !== balance2 — non-repeatable read!const tx = await db.beginTransaction();
await tx.query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
const balance1 = await tx.query('SELECT balance FROM accounts WHERE id = 1');
// Same transaction — balance cannot change
const balance2 = await tx.query('SELECT balance FROM accounts WHERE id = 1');
// balance1 === balance2 — repeatable read guaranteedSetting the isolation level to Repeatable Read ensures that reads within the same transaction always return the same data, preventing non-repeatable reads.
How to diagnose
Check the current isolation level with SHOW TRANSACTION ISOLATION LEVEL
Review the consistency requirements — does the transaction need repeatable reads or serializable?
Test with concurrent transactions to detect dirty reads, non-repeatable reads, or phantoms
How to fix
Set the isolation level to Repeatable Read or Serializable for stronger consistency guarantees
Use SELECT FOR UPDATE to lock rows that must not change during the transaction
Use optimistic concurrency control with version numbers if pessimistic locking is too costly
How to prevent
Choose the isolation level based on the consistency requirements of each transaction
Test concurrent transaction scenarios to verify the isolation level prevents anomalies
Related Resources
Related Glossary
Related Lessons
- Transactions
Learn about isolation levels.