Understanding MySQL Transactions, Locks, Isolation Levels, and MVCC

Understanding MySQL Transactions and Locks

MySQL transactions and locks are fundamental features of the database engine and common interview topics. This article introduces the concepts, implementation principles, and practical details of transactions and locks.

What Is a Transaction?

According to Wikipedia, a transaction is a logical unit of work in a DBMS consisting of a finite sequence of database operations.

ACID Properties

Transactions have four essential properties, often abbreviated as ACID:

Atomicity – all operations succeed or all fail.

Consistency – database integrity constraints remain intact before and after the transaction.

Isolation – concurrent transactions do not interfere with each other.

Durability – once committed, changes are permanently stored.

Transaction States

A transaction can be in one of five states: active – operations are currently being executed. partially committed – the last operation finished but changes are not yet flushed to disk. failed – an error occurred while the transaction was active or partially committed. aborted – the transaction has been rolled back after a failure. committed – the transaction is partially committed and all modifications have been persisted.

Isolation Levels

To balance isolation and performance, MySQL supports four isolation levels defined by the SQL standard: READ UNCOMMITTED – allows dirty reads. READ COMMITTED – prevents dirty reads but allows non‑repeatable reads and phantom reads. REPEATABLE READ – prevents dirty and non‑repeatable reads; InnoDB also eliminates phantom reads at this level. SERIALIZABLE – the strictest level, preventing all three anomalies.

The following table shows which anomalies each level can still produce:

Isolation Level

Dirty Read

Non‑Repeatable Read

Phantom Read

READ UNCOMMITTED

Possible

Possible

Possible

READ COMMITTED

Impossible

Possible

Possible

REPEATABLE READ

Impossible

Impossible

Possible (InnoDB: impossible)

SERIALIZABLE

Impossible

Impossible

Impossible

Multi‑Version Concurrency Control (MVCC)

MVCC solves read‑consistency problems by maintaining multiple versions of a row. In InnoDB each row stores hidden fields trx_id (the transaction that created the version) and roll_pointer (pointer to the undo log).

When a transaction reads data, a ReadView is built containing: m_ids – IDs of all active read/write transactions. min_trx_id – smallest active transaction ID. max_trx_id – next transaction ID to be assigned. creator_trx_id – ID of the transaction that created the ReadView.

Visibility of a row version is determined by comparing its trx_id with the values in the ReadView according to four rules (same transaction, older than min_trx_id, newer than max_trx_id, or between min_trx_id and max_trx_id and present in m_ids).

Locks

MySQL classifies lock conflicts into three categories: read‑read, write‑write, and read‑write. To guarantee consistency, MySQL uses shared (S) locks for reads and exclusive (X) locks for writes, plus intention locks (IS, IX) at the table level.

Lock Granularity

Locks can be row‑level or table‑level.

Row‑level locks affect only the specific rows involved.

Table‑level locks affect the whole table.

Row‑Level Lock Types

InnoDB implements three row‑level lock forms:

Record Locks – lock a specific index record.

Gap Locks – lock the gap between index records.

Next‑Key Locks – a combination of a record lock and the gap to its right (default lock type). Next‑Key Locks degenerate to Record Locks for unique‑index equality searches and to Gap Locks when no record matches.

Examples:

SELECT * FROM t WHERE id = 4 FOR UPDATE;   -- Record lock on id=4

SELECT * FROM t WHERE id = 3 FOR UPDATE;   -- Gap lock on (1,4)

SELECT * FROM t WHERE id > 5 AND id <= 7 FOR UPDATE;   -- Next‑Key locks on (4,7] and (7,+∞)

Table‑Level Locks in InnoDB

InnoDB uses intention locks (IS, IX) and an AUTO‑INC lock for tables with AUTO_INCREMENT columns. The AUTO‑INC lock is held only for the duration of the insert operation; a lightweight lock may be used when the number of rows to insert is known in advance.

Locking Reads vs. Snapshot Reads

Snapshot reads (the default for READ COMMITTED and REPEATABLE READ) rely on MVCC and do not acquire row locks. Locking reads (e.g., SELECT ... FOR UPDATE) acquire row‑level locks to prevent concurrent modifications.

Understanding these mechanisms helps developers write correct concurrent SQL code and prepare for database‑related interview questions.