MySQL Isolation Levels: Common Race Conditions and How to Fix Them

Symptom

A balance is deducted incorrectly. Stock goes negative. Two users both purchase the last ticket. A confirmation email fires twice. No errors in the logs — every query succeeded — just wrong results.

These are symptoms of database race conditions.

---

The 4 InnoDB isolation levels

InnoDB supports 4 isolation levels, defaulting to REPEATABLE READ:

-- Check current isolation level
SELECT @@transaction_isolation;

-- Change for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change for a single transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ (default)	Prevented	Prevented	Possible*
SERIALIZABLE	Prevented	Prevented	Prevented

*InnoDB uses MVCC and gap locks to reduce phantom reads under REPEATABLE READ, but doesn't eliminate them in all cases.

---

Common race conditions

1. Lost Update

Two transactions read the same value, compute a new value independently, then both write — one write overwrites the other.

-- Initial balance: 1000
TX A: SELECT balance FROM accounts WHERE id = 1; -- reads: 1000
TX B: SELECT balance FROM accounts WHERE id = 1; -- reads: 1000

TX A: UPDATE accounts SET balance = 1000 - 200 WHERE id = 1; -- writes: 800
TX B: UPDATE accounts SET balance = 1000 - 300 WHERE id = 1; -- writes: 700
-- Result: 700, but it should be 1000 - 200 - 300 = 500

No isolation level prevents lost update by itself. Even SERIALIZABLE can't help if the code does a separate SELECT followed by a separate UPDATE in the same transaction — isolation only protects reads, not the gap between reading and writing.

Fixes:

-- Option 1: Atomic update — skip the read entirely
UPDATE accounts SET balance = balance - 200 WHERE id = 1;

-- Option 2: SELECT FOR UPDATE — lock the row on read
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions trying to UPDATE or SELECT FOR UPDATE will wait here
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
COMMIT;

-- Option 3: Optimistic locking — check version on write
UPDATE accounts
SET balance = balance - 200, version = version + 1
WHERE id = 1 AND version = 5; -- 5 is the version that was read
-- If version changed → affected rows = 0 → retry

2. Dirty Read

A transaction reads data that another transaction has modified but not yet committed — if the other transaction rolls back, the data that was read never existed.

TX A: BEGIN;
TX A: UPDATE products SET stock = 0 WHERE id = 10;

TX B: SELECT stock FROM products WHERE id = 10;
-- With READ UNCOMMITTED → reads stock = 0 (not committed yet!)
-- With READ COMMITTED or higher → still reads the old committed value

TX A: ROLLBACK; -- stock reverts to its original value
-- TX B already used stock = 0 to make a decision → wrong

Fix: use READ COMMITTED or higher. READ UNCOMMITTED is almost never used in production.

InnoDB defaults to REPEATABLE READ, so dirty reads don't occur in most codebases.

3. Non-repeatable Read

Within the same transaction, reading the same row twice returns different values because another transaction committed a change in between.

TX A: BEGIN;
TX A: SELECT price FROM products WHERE id = 1; -- reads: 100

TX B: UPDATE products SET price = 150 WHERE id = 1; COMMIT;

TX A: SELECT price FROM products WHERE id = 1; -- reads: 100 or 150?

• READ COMMITTED: TX A reads 150 on the second SELECT (always reads the latest committed snapshot)
• REPEATABLE READ: TX A still reads 100 (snapshot from the start of the transaction)

When this matters: if TX A uses a price to compute something across multiple statements — with READ COMMITTED, a later computation might use 150 while an earlier one used 100, causing inconsistency within the same transaction.

Fix: use REPEATABLE READ (already the default) to guarantee consistent reads throughout a transaction.

4. Phantom Read

Within the same transaction, a query counting rows returns a different number on the second run because another transaction inserted new rows in between.

TX A: BEGIN;
TX A: SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 10

TX B: INSERT INTO orders (status, ...) VALUES ('pending', ...); COMMIT;

TX A: SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 11?

• REPEATABLE READ: InnoDB uses MVCC, so TX A typically still sees 10 (old snapshot). However, if TX A uses SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, InnoDB applies a gap lock and TX B's INSERT will block.
• SERIALIZABLE: completely prevents phantom reads by automatically adding shared locks to every SELECT

5. Write Skew

Less common but dangerous. Two transactions read the same dataset, each makes a decision based on that snapshot, then both write — and the combined result violates a constraint.

-- Constraint: at least one doctor must be on call
-- Current state: Dr A and Dr B are both on call

TX A (Dr A requests leave):
  SELECT COUNT(*) FROM doctors WHERE on_call = true; -- 2, OK
  UPDATE doctors SET on_call = false WHERE id = 'A';

TX B (Dr B requests leave, concurrent):
  SELECT COUNT(*) FROM doctors WHERE on_call = true; -- 2, OK
  UPDATE doctors SET on_call = false WHERE id = 'B';

-- Result: no doctors on call — constraint violated

REPEATABLE READ doesn't prevent write skew because the two transactions modify different rows.

Fix: SELECT ... FOR UPDATE covering the full relevant dataset, or SERIALIZABLE.

---

SELECT FOR UPDATE and LOCK IN SHARE MODE

SELECT FOR UPDATE — exclusive lock

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row id=1 is now exclusively locked
-- Any other transaction's SELECT FOR UPDATE or UPDATE on this row will wait
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
COMMIT;

Use when:

• Doing a read followed by a write and the read must be stable
• Check-then-act patterns (verify a condition, then act on it)

SELECT LOCK IN SHARE MODE — shared lock

BEGIN;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- Multiple transactions can hold shared locks on the same row
-- But no transaction can UPDATE until all shared locks are released
COMMIT;

Use when:

• Need to ensure a row doesn't change while computing something based on it
• Won't be writing, but need consistency guarantees during reads

FOR UPDATE only matters inside a transaction

-- Useless: FOR UPDATE without a transaction
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Correct: must be inside BEGIN...COMMIT
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- process...
COMMIT;

---

Optimistic locking — when contention is rare

When race conditions are uncommon, pessimistic locking (FOR UPDATE) reduces throughput unnecessarily. Optimistic locking doesn't lock on read — it only checks on write:

-- Schema needs a version column
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 0;

-- Read
SELECT id, stock, version FROM products WHERE id = 10;
-- Suppose: stock=5, version=3

-- Write (only update if version is still 3)
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 10 AND version = 3;

-- If affected rows = 0 → another transaction changed the row → retry

With TypeORM:

@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  stock: number;

  @VersionColumn()
  version: number; // TypeORM manages this automatically
  // Throws OptimisticLockVersionMismatchError on conflict
}

// TypeORM checks version on save and throws if there's a conflict
await productRepository.save(product);

---

Choosing an isolation level

Scenario	Isolation Level	Notes
Analytics, reports, approximate results are fine	READ COMMITTED	Less locking, higher throughput
Standard OLTP application	REPEATABLE READ (default)	Sufficient for most cases
Need stable totals/counts within a transaction	REPEATABLE READ + FOR UPDATE	More flexible than SERIALIZABLE
Financial data, zero tolerance for race conditions	SERIALIZABLE or FOR UPDATE	Lower performance

Don't use SERIALIZABLE globally — it locks every SELECT, significantly reducing throughput. Instead, use FOR UPDATE selectively only where race conditions are an actual concern.

---

Checklist when you suspect a race condition

1. Identify the type: lost update, dirty read, phantom read, or write skew?
2. Check isolation level: SELECT @@transaction_isolation;
3. Inspect the code pattern: is there a read-then-write with two separate statements?
4. Add FOR UPDATE on the SELECT that precedes the UPDATE
5. Consider atomic update: UPDATE t SET col = col - n instead of read-then-write
6. Add optimistic locking if contention is low and you don't want to sacrifice throughput