Isolation Levels, Locking & Deadlocks

Concurrency problems

Problem	What happens
Dirty read	You read data another transaction has not committed
Non-repeatable read	You read the same row twice and get different values
Phantom read	You run the same range query twice and new rows appear
Lost update	Two transactions overwrite each other's change

The four isolation levels

Level	Dirty	Non-repeatable	Phantom
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	No	Possible	Possible
REPEATABLE READ (InnoDB default)	No	No	Mostly prevented
SERIALIZABLE	No	No	No

SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL  TRANSACTION ISOLATION LEVEL REPEATABLE READ;

InnoDB's REPEATABLE READ uses MVCC (multi-version concurrency control): each transaction reads a consistent snapshot taken at its first read, so readers never block writers and writers never block readers. Gap locks additionally prevent most phantoms.

READ COMMITTED is a common choice for high-concurrency OLTP systems because it takes fewer gap locks and so deadlocks less often.

Lock types

-- shared lock: others can read, not write
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;   -- or FOR SHARE

-- exclusive lock: nobody else can read-for-update or write
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- MySQL 8: do not wait, or skip locked rows (great for job queues)
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE NOWAIT;
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

The safe read-modify-write pattern:

START TRANSACTION;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- lock the row
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

Without FOR UPDATE, two concurrent transfers can both read the same balance and one update is lost.

Deadlocks

A deadlock happens when two transactions each hold a lock the other needs:

T1: UPDATE accounts SET … WHERE id = 1;   -- locks row 1
T2: UPDATE accounts SET … WHERE id = 2;   -- locks row 2
T1: UPDATE accounts SET … WHERE id = 2;   -- waits for T2
T2: UPDATE accounts SET … WHERE id = 1;   -- waits for T1 → deadlock

InnoDB detects this automatically, kills the cheaper transaction and returns error 1213. Your application should catch that error and retry — deadlocks are normal in a busy system, not a bug to be eliminated.

SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK section
SELECT * FROM performance_schema.data_locks;
SELECT @@innodb_lock_wait_timeout;   -- default 50 seconds

Reducing lock contention

1. Keep transactions short and commit promptly.
2. Access rows in a consistent order across the whole codebase — this alone removes most deadlocks.
3. Index your WHERE columns: without an index InnoDB locks far more rows than you intended.
4. Do not hold locks while waiting on anything external.
5. Consider READ COMMITTED for write-heavy workloads.
6. Use SKIP LOCKED for queue tables so workers never block each other.