Master MySQL Transaction Isolation Levels and Lock Mechanisms

The article outlines the four transaction isolation levels defined in MySQL, referencing the explanations from the book High Performance MySQL .

Changing the Isolation Level

Global change : edit the MySQL configuration file (my.cnf) and add the following lines at the end:

# Optional parameters: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
[mysqld]
transaction-isolation = REPEATABLE-READ

MySQL’s default global level is REPEATABLE-READ, which is also the built‑in default.

Session change : after logging into the MySQL client, execute: SET SESSION transaction_isolation = 'READ-COMMITTED'; Remember that MySQL’s autocommit parameter is ON by default, meaning each individual query runs as its own transaction and is automatically committed. Therefore, lock behavior and isolation apply even without an explicit START TRANSACTION statement.

Lock Types

MySQL uses two basic lock modes:

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

Exclusive lock – acquired by write operations; no other session can obtain any lock on the same row or table.

Locks can be applied at the row level or the table level, resulting in combinations such as row‑shared, row‑exclusive, table‑shared, and table‑exclusive.

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

When a transaction holds an exclusive lock on a row, any other transaction attempting a conflicting operation will be blocked until the lock is released.

Isolation Level Examples (InnoDB)

Two MySQL clients, A and B, are used to demonstrate each level. Client A changes the isolation level, starts a transaction, and performs queries while client B modifies data concurrently.

1. READ‑UNCOMMITTED

Client A sets the level to READ‑UNCOMMITTED and queries the user table. Client B updates a row but does not commit. A’s subsequent query sees the uncommitted change, demonstrating that this level allows dirty reads. After B rolls back, A’s query reflects the original data again.

2. READ‑COMMITTED

With the isolation level set to READ‑COMMITTED, client A’s query does not see B’s uncommitted update. Only after B commits does A observe the change, confirming that this level prevents dirty reads but allows non‑repeatable reads.

3. REPEATABLE‑READ

When both clients use REPEATABLE‑READ, A’s repeated queries return the same result even after B commits a change, because the snapshot taken at the start of A’s transaction remains unchanged. Row‑level shared locks are held until the transaction ends.

4. SERIALIZABLE

At the strictest level, SERIALIZABLE, a query acquires a shared lock on the entire table or affected rows. Other transactions can only read; any write attempts block until the first transaction finishes, ensuring full serializability.

In summary, MySQL’s isolation levels control the visibility of concurrent changes and the locking behavior of transactions. READ‑UNCOMMITTED allows dirty reads, READ‑COMMITTED prevents them, REPEATABLE‑READ provides repeatable reads with row‑level shared locks, and SERIALIZABLE enforces the strongest isolation by locking tables or rows for the duration of a transaction.