Master MySQL Transaction Isolation Levels and Locking Mechanisms

Database Isolation Levels

MySQL supports four isolation levels: READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ, SERIALIZABLE, as described in the book High Performance MySQL .

Changing the Isolation Level

Global change : add transaction-isolation = REPEATABLE-READ to the [mysqld] section of my.cnf. The default is REPEATABLE‑READ.

Session change : after connecting to MySQL, run

SET SESSION TRANSACTION ISOLATION LEVEL …;

Autocommit and Transaction Basics

MySQL’s autocommit is ON by default, meaning each statement runs in its own transaction and is committed automatically. If you use SELECT ... FOR UPDATE without starting a transaction, the lock is released immediately after the statement.

Lock Types

Shared lock : acquired by read operations; other sessions can also acquire shared locks but cannot acquire exclusive locks.

Exclusive lock : acquired by write operations; blocks all other locks on the same row or table.

Locks can be row‑level or table‑level, resulting in four combinations: row‑shared, table‑shared, row‑exclusive, table‑exclusive.

Locking Example

START TRANSACTION;
SELECT * FROM user WHERE userId = 1 FOR UPDATE;

After this, other sessions attempting to read with a shared lock (e.g., under SERIALIZABLE) will be blocked. SELECT * FROM user; This query will be suspended because it needs a shared lock.

SELECT * FROM user WHERE userId = 1 FOR UPDATE;
UPDATE user SET userAge = 100 WHERE userId = 1;

The second statement waits for the exclusive lock to be released.

Isolation Level Demonstrations (InnoDB)

Two clients, A and B, are used to illustrate each level.

1. READ‑UNCOMMITTED

Changes made by one transaction are visible to the other even before commit, showing dirty reads. Row‑level shared locks are still applied when a row is modified.

2. READ‑COMMITTED

Only after a transaction commits do its changes become visible to other transactions. Row‑level shared locks are held during modifications.

3. REPEATABLE‑READ

Even after a transaction commits, other concurrent transactions do not see its changes until they start a new transaction. Modifying a row acquires a row‑shared lock that is released when the transaction ends.

4. SERIALIZABLE

The strictest level; a query acquires a shared lock on the accessed rows or tables, preventing other transactions from writing until the transaction finishes.

These examples demonstrate how isolation levels affect visibility of changes and the locking behavior of InnoDB.