✏️ Explanatory Question

What are the different transaction isolation levels in MySQL?

👁 8 Views
📘 Detailed Answer
🟢 Easy
← Previous What are COMMIT, ROLLBACK, and SAVEPOINT?
💡

Answer with Explanation

61

What are the different transaction isolation levels in MySQL?

Level: Advanced — Directly tied to the "Isolation" in ACID; a favourite deep-dive question.

Isolation levels define how and when the changes made by one transaction become visible to other concurrent transactions. They balance data consistency against performance/concurrency.

MySQL (InnoDB) supports the four SQL-standard isolation levels, from least to most strict:

  • READ UNCOMMITTED: Transactions can read uncommitted changes from others. Allows "dirty reads." Least strict, fastest.
  • READ COMMITTED: Only reads committed data. Prevents dirty reads, but non-repeatable reads can occur.
  • REPEATABLE READ: Guarantees the same result if a row is read twice. The InnoDB default. Prevents dirty and non-repeatable reads.
  • SERIALIZABLE: The strictest level — transactions run as if fully sequential. Prevents all anomalies but lowest concurrency.
Key point: The default isolation level in MySQL's InnoDB engine is REPEATABLE READ, which uses MVCC (Multi-Version Concurrency Control) and snapshots to provide consistent reads without heavy locking.

Isolation Levels vs Anomalies

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Prevented Possible Possible
REPEATABLE READ (default) Prevented Prevented Prevented*
SERIALIZABLE Prevented Prevented Prevented

*In InnoDB, REPEATABLE READ also prevents phantom reads via next-key (gap) locking — beyond the basic SQL standard.

Quick Example

-- View the current isolation level
SELECT @@transaction_isolation;

-- Set the isolation level for the NEXT transaction
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set it globally (affects new sessions)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Use within a transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE acc_id = 1;
COMMIT;
The trade-off: Higher isolation = more consistency but more locking and lower concurrency. Lower isolation = faster and more concurrent but risks anomalies. Choose based on your application's needs.
Interviewer tip: The one-liner they want — "MySQL supports four isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (the InnoDB default), and SERIALIZABLE — trading consistency against concurrency by controlling which read anomalies are allowed."
← Previous What are COMMIT, ROLLBACK, and SAVEPOINT?