MySQL Locks, Transactions, and Concurrency Control Overview

This article provides a systematic introduction to MySQL locking, transaction, and concurrency‑control mechanisms, aiming to help readers deepen their understanding of how MySQL handles data consistency and performance.

1. MySQL Server Logical Architecture

Each client connection creates a server‑side thread (managed by a thread pool). A SELECT first checks the query cache; if not cached, it proceeds through parsing, optimization, and execution.

2. MySQL Locks

2.1 Shared and Exclusive Locks (Row‑level locks)

Shared lock (S) : also called a read lock; multiple sessions can read the same row concurrently.

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

2.2 Intention Locks (Table‑level)

InnoDB supports multi‑granularity locking. Before a session can acquire a row‑level shared lock, it must first obtain an intention‑shared (IS) lock on the table; before a row‑level exclusive lock, it must obtain an intention‑exclusive (IX) lock.

SELECT ... LOCK IN SHARE MODE   -- acquires IS lock
SELECT ... FOR UPDATE          -- acquires IX lock

2.3 Record Locks (Index Row Locks)

When a query uses an indexed column, InnoDB locks the matching index records. If no suitable index exists, the whole table is locked.

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;  -- locks the index record for c1=10

2.4 Gap Locks

Gap locks protect the gaps between index records (or before the first/after the last record) to prevent phantom rows during range scans.

SELECT userId FROM t1 WHERE userId BETWEEN 1 AND 4 FOR UPDATE;  -- locks the gaps between existing values 1‑4

2.5 Next‑Key Locks

A combination of a record lock and the preceding gap lock. In the default REPEATABLE READ isolation level, next‑key locks are automatically applied.

2.6 Insert Intention Locks

When inserting into a gap, InnoDB first acquires an insert‑intention lock on the gap, allowing concurrent inserts into different positions of the same gap.

START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;  -- acquires IX lock on the gap
INSERT INTO child (id) VALUES (101);          -- waits if another transaction holds a conflicting insert‑intention lock

2.7 AUTO‑INC Locks

Special table‑level locks that serialize inserts into an AUTO_INCREMENT column to guarantee monotonic primary‑key values.

2.8 Predicate Locks for Spatial Indexes

(Briefly mentioned; omitted for brevity.)

3. Transactions

A transaction is an atomic unit of work. MySQL defaults to autocommit mode, where each statement is its own transaction. Turning off autocommit groups statements into an explicit transaction.

SHOW VARIABLES LIKE "autocommit";
SET autocommit=0;   -- disable autocommit
START TRANSACTION;
...                -- multiple DML statements
COMMIT;

Example of a money‑transfer transaction:

CREATE DATABASE IF NOT EXISTS employees;
USE employees;
CREATE TABLE `account` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR(4),
  `p_money` DECIMAL(10,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);
INSERT INTO `account` (`id`,`p_name`,`p_money`) VALUES (1,'tim',200),(2,'bill',200);
START TRANSACTION;
SELECT p_money FROM account WHERE p_name='tim';   -- step 1
UPDATE account SET p_money=p_money-100 WHERE p_name='tim';   -- step 2
UPDATE account SET p_money=p_money+100 WHERE p_name='bill';   -- step 3
COMMIT;

4. Isolation Levels

READ UNCOMMITTED : dirty reads are allowed.

READ COMMITTED : each statement sees only committed data; non‑repeatable reads can occur.

REPEATABLE READ : the default for InnoDB; guarantees repeatable reads and uses next‑key locks to prevent phantom rows.

SERIALIZABLE : the strongest level; every read acquires a lock, eliminating phantoms but increasing contention.

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

5. Concurrency Control and MVCC

Multi‑Version Concurrency Control (MVCC) provides non‑blocking reads by storing hidden creation and deletion version numbers with each row.

Select (consistent read) : reads rows whose creation version ≤ current transaction version and whose deletion version is NULL or > current version.

Insert : the new row receives the current transaction version as its creation version.

Update : the old row’s deletion version is set to the current version; a new version of the row is inserted with the current version.

Delete : the row’s deletion version is set to the current transaction version.

MVCC eliminates dirty reads, but other phenomena depend on the isolation level:

Dirty read : reading uncommitted changes (possible only under READ UNCOMMITTED).

Non‑repeatable read : a row changes between two reads in the same transaction (possible under READ COMMITTED).

Phantom read : new rows appear in a range query (possible under READ COMMITTED; prevented by REPEATABLE READ with next‑key locks).

5.5 Viewing Lock Information

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

6. Deadlock Issues

A deadlock occurs when two sessions wait for each other’s locks, causing a cycle. Example:

// 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;

InnoDB detects deadlocks using a wait‑for graph and aborts one victim transaction. Common avoidance strategies include:

Access tables and rows in a consistent order.

Split large transactions into smaller ones.

Lock all required resources early in the transaction.

Use a lower isolation level when appropriate (e.g., RC instead of RR).

Ensure proper indexing to reduce row‑level lock contention.

For further reading, see the MySQL official documentation on InnoDB locking.