Master MySQL Locks and Transactions: A Deep Dive into Concurrency Control

1. MySQL Server Logical Architecture

Each client connection creates a thread on the MySQL server, which processes the query through parsing, optimization, and execution phases. If a matching result exists in the query cache, it is returned immediately; otherwise the full execution pipeline runs.

2. MySQL Lock Types

2.1 Shared and Exclusive Locks (Row‑level)

Shared lock (S) : also called a read lock; multiple sessions can hold it simultaneously on the same row.

Exclusive lock (X) : also called a write lock; it blocks all other read and write locks on the same row.

2.2 Intention Locks

InnoDB uses intention locks (IS for shared, IX for exclusive) at the table level to indicate a session’s intention to acquire row‑level locks later. A transaction must acquire an IS or IX lock before it can lock any row in that table.

2.3 Record Locks (Index Row Locks)

When a query matches rows via an index, InnoDB places a record lock on each matching index entry. If the query does not use an index, the whole table may be locked.

2.4 Gap Locks

Gap locks protect the gaps between index records (or before the first record) to prevent other sessions from inserting rows into those gaps. They are typically used by range queries such as SELECT ... FOR UPDATE or BETWEEN clauses.

2.5 Next‑Key Locks

A next‑key lock combines a record lock with the surrounding gap lock, preventing phantom rows from appearing in repeatable‑read transactions.

2.6 Insert Intention Locks

These are a special form of gap lock applied before an INSERT. They lock the gap where the new row will be placed but do not block other inserts into different gaps.

2.7 AUTO‑INC Locks

When inserting into a table with an AUTO_INCREMENT column, InnoDB obtains a table‑level AUTO‑INC lock to guarantee that generated primary‑key values are contiguous across concurrent transactions.

3. Transactions

A transaction groups a set of SQL statements into an atomic unit: either all statements commit or none do. MySQL defaults to AUTOCOMMIT=1, meaning each statement runs in its own transaction unless AUTOCOMMIT is disabled.

SHOW VARIABLES LIKE "autocommit";
SET autocommit=0;  -- disable
SET autocommit=1;  -- enable

Explicit transaction control uses START TRANSACTION, COMMIT, and ROLLBACK.

4. Isolation Levels

READ UNCOMMITTED : allows dirty reads; other sessions can see uncommitted changes.

READ COMMITTED : prevents dirty reads but allows non‑repeatable reads.

REPEATABLE READ (default in InnoDB): guarantees repeatable reads and uses next‑key locks to avoid most phantom reads.

SERIALIZABLE : the strictest level; every SELECT behaves like FOR UPDATE, eliminating phantom reads at the cost of higher contention.

Isolation level can be inspected and changed with:

SELECT @@global.tx_isolation;   -- global level
SELECT @@tx_isolation;           -- session level
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

5. MVCC (Multi‑Version Concurrency Control)

InnoDB stores hidden columns per row: a creation version and an update version. SELECT statements without locking clauses read the snapshot that existed at the transaction’s start, while writes create new versions and mark old rows with the update version.

-- Snapshot read condition
(row_create_version <= current_version) &&
(row_update_version IS NULL || row_update_version > current_version)

INSERT writes a new row with the current version as its creation version. UPDATE creates a new version of the row and records the old version’s update version. DELETE marks the row with the current version as a delete marker.

6. Deadlocks

A deadlock occurs when two or more transactions wait for each other’s locks, forming a cycle. InnoDB detects deadlocks using a wait‑for graph and aborts one transaction (the victim) after innodb_lock_wait_timeout expires.

// Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name='tim';
UPDATE account SET p_money=p_money+100 WHERE p_name='bill';
COMMIT;

// Session B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name='bill';
UPDATE account SET p_money=p_money-100 WHERE p_name='tim';
COMMIT;

Both sessions lock one row and then attempt to lock the other, creating a circular wait.

Common strategies to avoid deadlocks:

Access tables and rows in a consistent order across transactions.

Break large transactions into smaller ones.

Lock all required resources early in the transaction.

Use a lower isolation level (e.g., READ COMMITTED) when business logic permits.

Ensure appropriate indexes exist to limit the number of rows locked.

7. Viewing Lock Information

SELECT * FROM information_schema.innodb_trx WHERE trx_state='lock wait';
SHOW ENGINE INNODB STATUS;

These commands reveal waiting transactions and the current lock graph.