CONCEPT

MySQL transaction isolation levels

MySQL transaction isolation levels are the four modes that can be set on a MySQL session (or globally, or at server startup) to control how concurrent transactions interact with each other. Each level defines a specific trade-off between performance (fewer locks, more concurrency, weaker guarantees) and correctness (more locks, less concurrency, stronger guarantees) by permitting or forbidding three classes of read anomaly.

The four levels

From weakest to strongest:

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	allowed	allowed	allowed
READ COMMITTED	prevented	allowed	allowed
REPEATABLE READ	prevented	prevented	prevented*
SERIALIZABLE	prevented	prevented	prevented

*MySQL's REPEATABLE READ prevents phantom reads on locking reads via gap locking — a MySQL-specific strengthening of the SQL-standard definition (SQL-standard REPEATABLE READ permits phantoms).

REPEATABLE READ is the MySQL default — unlike PostgreSQL and SQL Server, which both default to READ COMMITTED. "Repeatable read is the default MySQL isolation level used for all connections unless configured otherwise." (Source: .)

Per-level semantics

READ UNCOMMITTED

The lowest level. Transactions always read the latest version of a row whether or not the writing transaction has committed. Permits all three violations, including the particularly nasty case where a read observes a value that's later rolled back. Use case: "performance takes priority over data consistency" — e.g., approximate social-media like counts where a ±1% error is acceptable for latency gains.

READ COMMITTED

One step up. Prevents dirty reads by only reading committed data. Each SELECT inside a transaction creates a fresh snapshot at that moment, so subsequent selects may see different committed data as other transactions commit. Row versioning tags writes with the writing transaction's ID; subsequent reads pick the latest committed version. Still permits non-repeatable reads and phantom reads.

REPEATABLE READ (MySQL default)

Pins the transaction to a single consistent snapshot taken at the first read — all subsequent reads in the transaction see the same data. Prevents dirty reads + non-repeatable reads. InnoDB additionally uses gap locks on locking reads to prevent phantom reads, making MySQL's REPEATABLE READ stronger than the SQL-standard version. This is the same foundational substrate as formal snapshot isolation in other databases, at the REPEATABLE READ name.

Critical InnoDB detail on locking reads (SELECT … FOR SHARE / FOR UPDATE):

• With a usable index on the WHERE condition: only the matching rows are locked.
• Without a usable index (table scan): all scanned rows are locked regardless of whether they match WHERE, plus gap locks are taken to prevent inserts that would change a re-run's results.

Canonical operational implication: missing indexes inflate lock scope, not just query latency, under REPEATABLE READ.

SERIALIZABLE

The strictest level. Works exactly like REPEATABLE READ, but adds an implicit shared lock on every SELECT — whether or not you use FOR SHARE. Prevents all three violations but produces the most lock contention and deadlock risk. Reach for it only when correctness requirements explicitly demand forbidding all three violations; use READ COMMITTED + explicit locking reads for narrower correctness needs.

Relationship to ACID and MVCC

Isolation levels are the I of ACID. Under the hood in InnoDB, all four levels are implemented via Multi-Version Concurrency Control (MVCC) — row versions tagged by transaction ID — plus shared and exclusive row locks for concurrent-write coordination. The level differences are about which row version a read sees, and when locks are taken:

Level	Snapshot scope	Reads take locks?
READ UNCOMMITTED	latest (incl. uncommitted)	no
READ COMMITTED	per-statement	no (on plain SELECT)
REPEATABLE READ	per-transaction (pinned)	no (on plain SELECT)
SERIALIZABLE	per-transaction (pinned)	yes (implicit S lock)

Setting the isolation level

Three scopes, with privileges and caveats:

1. Per-session / per-next-transaction (default use case):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 'SESSION' optional

The SESSION keyword is optional if run before a transaction starts; errors out if run inside an open transaction.

1. Globally (affects future sessions only; current sessions can still override):

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Requires CONNECTION_ADMIN user permission.

1. At server startup (via CLI flag or config file):

--transaction-isolation=READ-UNCOMMITTED

[mysqld]
transaction-isolation = REPEATABLE-READ

Sets the server default before MySQL starts. Per-session overrides still apply.

MySQL-specific vs SQL-standard

MySQL's REPEATABLE READ is stronger than the SQL standard requires. Per Berenson et al. 1995 ("A critique of ANSI SQL isolation levels"), the SQL-standard REPEATABLE READ permits phantom reads; only SERIALIZABLE forbids them. MySQL's InnoDB closes the phantom-read gap at REPEATABLE READ level via gap locking on locking reads — giving applications phantom-read protection one level earlier than the standard guarantees.

This divergence matters when porting code between engines: an application relying on REPEATABLE READ's MySQL-specific phantom-read protection will silently permit phantom reads on other engines like Oracle (where REPEATABLE READ is the SQL-standard variant) or any engine implementing the bare SQL-standard semantics.

Known anomalies not prevented (nuance)

The post's three-violation framing (dirty / non-repeatable / phantom) is the classical pedagogical set but not exhaustive. Two important anomalies the wiki should note:

• Write-skew: two concurrent transactions each read-then- write disjoint rows based on an invariant that doesn't hold after both commit. Permitted by snapshot isolation / REPEATABLE READ; forbidden by SERIALIZABLE.
• Lost update: two transactions each read-modify-write the same row; one's write silently overwrites the other's. Forbidden by SERIALIZABLE and by REPEATABLE READ with locking reads (SELECT … FOR UPDATE); permitted by READ COMMITTED and READ UNCOMMITTED without care.

Applications moving from SERIALIZABLE to REPEATABLE READ for performance should audit for both classes.

Seen in

• — Brian Morrison II's canonical PlanetScale pedagogical walk- through of the four levels + three violations + two-lock-type substrate + gap-locking phantom-protection mechanism + FOR SHARE / FOR UPDATE locking reads + three isolation- setting mechanisms. REPEATABLE READ as MySQL default canonicalised verbatim. InnoDB MVCC as the pinned-transaction- ID substrate for REPEATABLE READ vs fresh-snapshot-per- select under READ COMMITTED canonicalised. SERIALIZABLE = REPEATABLE READ + implicit-shared-locks-on-every-SELECT canonicalised.

• — Sugu Sougoumarane's 2020 canonical wiki argument for minimising isolation-level strictness in distributed databases. Reframes the four levels as a coupling-cost axis (canonicalised on concepts/distributed-isolation-coupling-cost) rather than just a correctness-vs-throughput trade-off: "The more loosely coupled components are in a distributed system, the better it scales." Three load-bearing contributions to this page: (1) Serializable has two structural failure modes (excess locking + frequent deadlocks) — "not a coincidence that all the existing popular databases like Postgres and MySQL recommend against it". (2) ReadCommitted is the practical baseline + upgradeable to Serializable on demand via locking reads — gives the best of both worlds. The counter-intuitive finding: prefer FOR UPDATE (exclusive) over LOCK IN SHARE MODE (shared) for racing read-then-write cases, because the stronger lock avoids the shared-to-exclusive upgrade deadlock shape. (3) MySQL's REPEATABLE READ is actually SnapshotRead — "MySQL supports the SnapshotRead isolation level by default, but misleadingly calls it REPEATABLE_READ"; canonical clarification of the engine-vs-SQL-standard naming divergence. Also introduces the proposed ReadPartialCommits level as a design-space pointer for 2PC-era applications that can tolerate in-flight-partial-commit visibility — a level weaker than ReadUncommitted that no production database has shipped. Canonicalises the three-part prescription on [[patterns/lowest-isolation-plus-explicit- locks]]: ReadCommitted baseline + avoid multi-statement transactions + avoid distributed transactions via shard- local data co-location.

Related

• concepts/acid-properties
• concepts/dirty-read
• concepts/non-repeatable-read
• concepts/phantom-read
• concepts/shared-lock-vs-exclusive-lock
• concepts/gap-locking
• concepts/locking-read-mysql
• concepts/snapshot-isolation
• concepts/row-level-lock-contention
• concepts/serializable-isolation
• concepts/read-committed-isolation
• concepts/read-uncommitted-isolation
• concepts/read-partial-commits
• concepts/distributed-isolation-coupling-cost
• patterns/on-demand-serializable-read
• patterns/lowest-isolation-plus-explicit-locks
• systems/mysql
• systems/innodb

Engine-level mechanisms under the same names

Ben Dicken's PlanetScale primer (Source: ) makes clear that MySQL and Postgres deliver the same isolation-level names via completely different implementations:

• REPEATABLE READ consistent reads: MySQL reconstructs older row versions from a per-row undo log; Postgres keeps multiple row versions on-heap, tagged with xmin and xmax transaction IDs.
• Postgres REPEATABLE READ forbids phantom reads (above SQL spec), while MySQL's does so only for locking reads via gap locks.
• SERIALIZABLE write-conflict resolution: MySQL always takes X locks and relies on deadlock detection to break cycles; Postgres uses predicate locks + optimistic detection and kills a transaction at commit rather than blocking.

Both approaches therefore require application-side retry logic, for different triggers (deadlock vs. SSI abort).