Skip to content

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

✗ Broken code
// 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!
✓ Fixed code
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 guaranteed

Setting 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

← Back to language