CONCEPT Cited by 1 source

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: sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work.)

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:

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.

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

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Requires CONNECTION_ADMIN user permission.

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¶

sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work — 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.