Understanding MySQL Isolation Levels: From Dirty Reads to Serializable

Introduction

Building on a previous article about MySQL transactions and ACID, this piece examines how MySQL uses isolation levels to maintain data consistency when multiple clients operate on the same database concurrently.

What Is a MySQL Isolation Level?

MySQL offers four isolation modes that control how a session behaves during concurrent transactions. The chosen level determines which locking mechanisms are applied and which isolation anomalies may appear.

What Is ACID Compliance?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable storage and integrity of data across transactions.

Atomicity

All statements in a transaction are treated as a single unit; either all succeed or all are rolled back.

Consistency

Transactions move the database from one valid state to another, preserving constraints, triggers, and cascades.

Isolation

Transactions execute independently, preventing interference from other concurrent transactions.

Durability

Once a transaction commits, its changes survive system crashes and power failures.

Simple Example

Consider an online CD store with four tables: products , unit_status , customer_transactions , and customers . The following diagram shows the schema:

Isolation Anomalies

Isolation levels aim to prevent three common anomalies.

Dirty Read

A transaction reads data modified by another uncommitted transaction.

start transaction;
update unit_status set quantity = quantity + 2 where product_id = 20 and status = 'sold_not_shipped';
update unit_status set quantity = quantity - 2 where product_id = 20 and status = 'available';
commit;

If another session queries the same row before the first transaction commits, it may still see the original quantity, leading to inconsistent information if the first transaction later rolls back.

select quantity from unit_status where product_id = 20 and status = 'available';

Non‑repeatable Read

The same query returns different results because another transaction modified the data between reads.

-- First read returns 15
select quantity from unit_status where product_id = 20 and status = 'available';
-- Another transaction updates the row
-- Second read returns 13
select quantity from unit_status where product_id = 20 and status = 'available';
commit;

Phantom Read

Two identical queries return different row counts because another transaction inserted a new row between them.

-- First count
select count(*) from products;
-- Another transaction inserts a new product
insert into products set album = 'The Battle Of Los Angeles', artist = 'Rage Against The Machine', release_year = 1999, cost = 1500;
-- Second count sees the new row
select count(*) from products;
commit;

Locks and Isolation

MySQL enforces isolation using various lock types.

Shared Lock

Allows multiple transactions to read a row but blocks modifications until all shared locks are released.

Exclusive Lock

Prevents other transactions from reading or writing the locked row, ensuring exclusive access.

Gap Lock

Locks the gap between index entries to prevent phantom reads during range scans.

start transaction;
select * from products where release_year = 1999 for update;
update products set cost = 800 where release_year = 1999;
commit;

MySQL’s Four Isolation Levels

Read Uncommitted

: Allows all anomalies, suitable when performance outweighs consistency. Read Committed: Prevents dirty reads; non‑repeatable reads and phantom reads may still occur. Repeatable Read: Default level; uses MVCC to block all three anomalies. Serializable: Highest safety; adds shared locks to all reads, eliminating anomalies but reducing throughput.

How to Set the Isolation Level

Session‑level configuration:

set transaction isolation level read uncommitted;
set session transaction isolation level read committed; -- ‘session’ is optional

Global default (requires admin privileges):

set global transaction isolation level serializable;

Or via MySQL configuration file:

[mysqld]
transaction-isolation = REPEATABLE-READ

Conclusion

By combining ACID principles, appropriate isolation levels, and lock mechanisms, MySQL provides reliable, consistent data handling while allowing developers to balance safety and performance accordingpingly.