MySQLadvanced

MySQL Isolation Levels

How InnoDB implements each isolation level, why REPEATABLE READ behaves differently from PostgreSQL's, and which level to choose.

2 min readAdvancedUpdated 31 Jul 2026Edit this page

MySQL

InnoDB implements all four SQL isolation levels and defaults to REPEATABLE READ — a stricter default than most other engines, with behaviour that is specific to InnoDB.

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

REPEATABLE READ in InnoDB

A consistent read view is established at the first read of the transaction, and plain SELECT statements see that snapshot for the rest of the transaction — so no non-repeatable reads and no phantoms for non-locking reads.

The important subtlety:

InnoDB also takes next-key locks under REPEATABLE READ, which prevents phantoms for locking reads by locking the gaps between index records. That is what makes statement-based replication safe, and it is a common source of unexpected lock contention: an UPDATE matching no rows can still block inserts into a range.

READ COMMITTED

Each statement gets a fresh snapshot. Gap locks are largely disabled, so only matching rows are locked.

Reasons to choose it:

Substantially less lock contention on write-heavy workloads with range predicates.
Behaviour that matches PostgreSQL and Oracle defaults, which reduces surprises in code written against them.

Cost: non-repeatable reads within a transaction. Applications that read the same row twice in one transaction and expect stability must use a locking read.

Requires row-based binary logging — which you should be using anyway.

SERIALIZABLE

InnoDB implements it by converting plain SELECT into SELECT ... FOR SHARE. It therefore blocks where PostgreSQL's serializable aborts. The practical consequence is that lock wait timeouts, rather than serialization failures, become the retryable error.

Choosing

Level	Use when
`READ COMMITTED`	Write-heavy OLTP with range predicates; migrating code from PostgreSQL
`REPEATABLE READ`	Default; transactions that need a stable view across several reads
`SERIALIZABLE`	Rare, targeted use where the invariant cannot be expressed another way

Set it per session for the transactions that need something different, rather than changing the server default and re-testing every application at once.

Retry the right errors

Two error classes are normal and must be retried by the application:

ER_LOCK_DEADLOCK (1213) — deadlock; the transaction was rolled back entirely.
ER_LOCK_WAIT_TIMEOUT (1205) — lock wait timeout. By default only the statement is rolled back, not the transaction, so the application must decide explicitly whether to roll back and retry. innodb_rollback_on_timeout = ON makes it roll back the whole transaction, which is usually the behaviour applications actually expect.