What are the transaction isolation levels, and which does InnoDB use by default?

Summary

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. InnoDB defaults to REPEATABLE READ: a transaction's consistent reads see a snapshot taken at its first read, and locking reads use next-key locks to prevent phantoms.

Longer Answer

• READ UNCOMMITTED: dirty reads possible; almost never used.
• READ COMMITTED: each statement sees the latest committed data; fewer gap locks, so less locking contention; common in high-concurrency OLTP and the default in PostgreSQL and SQL Server.
• REPEATABLE READ (InnoDB default): a stable snapshot for plain SELECTs within the transaction; SELECT ... FOR UPDATE and writes lock index ranges (next-key locks).
• SERIALIZABLE: plain SELECTs become LOCK IN SHARE MODE (when autocommit is off), reducing concurrency.

A subtle point: in REPEATABLE READ, plain reads see the snapshot but UPDATE and locking reads act on the latest committed version, so read-then-update logic can still be wrong unless you lock or update atomically.

Key Points

• Four levels; InnoDB default REPEATABLE READ.
• READ COMMITTED reduces gap locking.
• Snapshot reads vs locking reads differ.
• Read-then-write still needs locks or atomic updates.

Deep dive: Transactions, Locking & Concurrency in Code

What interviewers look for

Knowing the default and the difference between consistent (snapshot) reads and locking reads.

Follow-up questions

• Why might a team switch to READ COMMITTED?
• What is a phantom read?