Isolation Levels

Isolation levels in MySQL are one of the most important (but often confusing) parts of transactions. They directly control how multiple transactions interact with each other and what concurrency issues can occur.

What is Isolation in Transactions?

• Isolation = how much one transaction is protected from the side effects of other concurrent transactions.
• MySQL implements this using Isolation Levels, which balance between performance (concurrency) and safety (consistency).

Problems Isolation Levels Prevent

When multiple transactions run at the same time, different anomalies may occur:

1. Dirty Read → Reading uncommitted changes from another transaction.

2. Non-Repeatable Read → A row’s value changes between two reads in the same transaction.

3. Phantom Read → New rows appear/disappear in a range query between two reads.

MySQL Isolation Levels

MySQL supports 4 standard isolation levels (SQL standard).

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
READ UNCOMMITTED	❌ Allowed	❌ Allowed	❌ Allowed	🔥 Fastest (least safe)
READ COMMITTED	✅ Prevented	❌ Allowed	❌ Allowed	⚡ Fast
REPEATABLE READ (default in MySQL InnoDB)	✅ Prevented	✅ Prevented	❌ Allowed (but handled by Next-Key Locks in InnoDB)	⚖️ Balanced
SERIALIZABLE	✅ Prevented	✅ Prevented	✅ Prevented	🐌 Slowest (most safe)

Run each transaction in seperate terminal

READ UNCOMMITTED

Transactions can see uncommitted changes made by others → dirty reads.

Rarely used because it risks reading invalid data.

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;

-- Transaction B (before A commits)
SELECT balance FROM accounts WHERE account_id = 1;

Transaction B might see Alice’s balance reduced before A commits, even if A later ROLLBACKs → invalid data

READ COMMITTED

A transaction only sees committed data.

Prevents dirty reads

But allows non-repeatable reads.

-- Transaction A
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Alice = 5000

-- Transaction B (commits)
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
COMMIT;

-- Transaction A reads again
SELECT balance FROM accounts WHERE account_id = 1; -- Alice = 4000

Same query in Transaction A gives different results → non-repeatable read.

REPEATABLE READ (MySQL Default)

• Prevents dirty reads
• Prevents non-repeatable reads
• But phantom rows can appear in range queries.

-- Transaction A
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 4000; -- Returns Alice

-- Transaction B
INSERT INTO accounts VALUES (3, 'Charlie', 4500);
COMMIT;

-- Transaction A runs the same query
SELECT * FROM accounts WHERE balance > 4000;
-- Returns Alice + Charlie → Phantom row appeared

InnoDB adds next-key locking to reduce phantom reads, but it can still occur depending on queries.

SERIALIZABLE

• Highest isolation level.
• Transactions are executed as if they run one after another (sequentially).
• Prevents dirty reads, non-repeatable reads, phantom reads.
• But slow → causes locking and reduces concurrency.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;
SELECT * FROM accounts WHERE balance > 4000;

If another transaction tries to insert/update rows that affect this query, it must wait until the first one commits.

How to Set Isolation Level in MySQL

-- Session level (only for current connection)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Global level (affects all new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Check current level:

SELECT @@transaction_isolation;

In practice:

• MySQL default = REPEATABLE READ (best tradeoff).
• Banking systems → SERIALIZABLE for maximum safety.
• High-performance apps → READ COMMITTED for speed with acceptable risks.