Transaction Isolation Levels in MySQL

What Is a Transaction Isolation Level?

Isolation is the ACID property that controls how concurrent transactions observe and affect shared data. The isolation level chooses a defined balance among reproducible reads, lock behavior, concurrency and overhead. It does not simply mean “safe versus unsafe” or “fast versus slow.”

InnoDB supports all four SQL isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Its default is REPEATABLE READ.

Reproducible Two-Session Lab

Create the table once, outside the test transactions. Then open two MySQL connections named Session A and Session B.

DROP TABLE IF EXISTS isolation_demo;
CREATE TABLE isolation_demo (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(40) NOT NULL,
  quantity INT NOT NULL,
  INDEX idx_quantity (quantity)
) ENGINE = InnoDB;

INSERT INTO isolation_demo VALUES
(1, 'Notebook', 100),
(2, 'Pen',       20);
COMMIT;

Reset quantity to 100 and commit before each independent experiment. Timing matters, so execute the labelled statements in the shown session order.

Dirty Read, Nonrepeatable Read and Phantom

Dirty, nonrepeatable and phantom reads describe visibility. Lost updates are prevented through correct update patterns, locking or optimistic version checks; an isolation label alone should not replace application design.

The Four InnoDB Isolation Levels

Two Verified Snapshot Experiments

READ COMMITTED: second read gets a fresh snapshot

-- Session B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- 100

-- Session A, between B's two reads
START TRANSACTION;
UPDATE isolation_demo SET quantity = 110 WHERE item_id = 1;
COMMIT;

-- Session B
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- 110
COMMIT;

REPEATABLE READ: plain reads reuse one snapshot

-- Reset and commit quantity=100 first.
-- Session B
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- 100; snapshot established.

-- Session A
START TRANSACTION;
UPDATE isolation_demo SET quantity = 110 WHERE item_id = 1;
COMMIT;

-- Session B
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- Still 100 in the consistent-read snapshot.
COMMIT;

A locking read such as SELECT FOR UPDATE reads the current version and takes locks, not the old consistent-read snapshot. Avoid casually mixing locking and nonlocking reads in one REPEATABLE READ transaction because they can represent different database states.

Set Global, Session or Next-Transaction Scope

-- Inspect current session value.
SELECT @@SESSION.transaction_isolation;

-- Every subsequent transaction in this session.
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Only the next transaction; execute outside a transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT COUNT(*) FROM isolation_demo;
COMMIT;

-- Server-wide default for future sessions; admin privilege required.
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• A GLOBAL change affects subsequent connections, not existing sessions.
• A SESSION change affects subsequent transactions in that connection, not an already active transaction.
• An unqualified SET TRANSACTION applies only to the next transaction.
• Set the level before START TRANSACTION when using next-transaction scope.
• Connection pools should establish session settings deliberately when a connection is checked out.

How to Choose an Isolation Level

1. Define the anomaly that would violate the business rule.
2. Choose the minimum correct combination of isolation, locks and constraints.
3. Keep transactions small and predicates indexed.
4. Test with two concurrent sessions, not only single-user data.
5. Handle deadlocks and lock timeouts explicitly.
6. Benchmark representative production volume before changing defaults.

Exam Answer and Practical Tasks

Model answer: An isolation level determines the visibility of concurrent transaction changes. InnoDB supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. READ UNCOMMITTED can permit dirty reads. READ COMMITTED creates a fresh consistent-read snapshot per statement. REPEATABLE READ, the InnoDB default, reuses the first-read snapshot for consistent reads. SERIALIZABLE applies stricter locking behavior. The correct choice depends on required anomalies, locking and workload.

Practice: reproduce one dirty read and roll it back; compare two reads under READ COMMITTED and REPEATABLE READ; insert a range-matching row between two count queries; then repeat using a locking range read and observe whether the insert waits.

Continue to concurrency control and locks and deadlocks.

Official References

• MySQL 8.4: InnoDB Transaction Isolation Levels
• MySQL 8.4: Consistent Nonlocking Reads
• MySQL 8.4: SET TRANSACTION Statement

Snapshot, locking and scope behavior were checked against the official MySQL 8.4 manual.