Understanding InnoDB Locks: How MySQL Handles Transactions, Isolation, and Deadlocks

Preface

Hello, I am SanYou. This article discusses InnoDB locks, covering how a single SQL acquires locks, lock rules, and how to analyze and resolve deadlocks.

Why locking is needed?

Introduction to InnoDB's seven lock types

How a single SQL acquires locks

Locking rules under the RR isolation level

How to view transaction lock status

Deadlock case analysis

1. Why locking is needed?

In a database, concurrent transactions must not interfere with each other, so MySQL adds locks to the SQL statements being executed.

Just as you lock a room to avoid being disturbed, MySQL locks a transaction to prevent other transactions from affecting it.

Without locks, concurrent writes could produce inconsistent or incorrect data, breaking data consistency.

1.1 Problems caused by transaction concurrency

Dirty read : Transaction A reads uncommitted data from transaction B.

Non‑repeatable read : Two identical queries within the same transaction return different results because another transaction modified the data.

Phantom read : A range query returns different result sets when another transaction inserts or deletes rows within the range.

1.2 Example of locking with and without locks

MySQL has four isolation levels: READ COMMITTED (RC), REPEATABLE READ (RR), SERIALIZABLE, and READ UNCOMMITTED. Under READ UNCOMMITTED, no locks are taken, leading to dirty reads, non‑repeatable reads, and phantom reads.

<code>CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_name_idx` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO account(id,name,balance) VALUES (1,'Jay',100);
INSERT INTO account(id,name,balance) VALUES (2,'Eason',100);
INSERT INTO account(id,name,balance) VALUES (3,'Lin',100);
</code>

Under READ UNCOMMITTED, Transaction B can modify Jay's balance before Transaction A reads it, causing a dirty read.

When using the SERIALIZABLE level, MySQL acquires locks for both reads and writes, preventing such anomalies.

2. InnoDB's Seven Lock Types

2.1 Shared (S) and Exclusive (X) Row Locks

InnoDB implements two standard row‑level locks: shared (S) locks for reads and exclusive (X) locks for writes.

Shared lock (S): A transaction must acquire an S lock before reading a record.

Exclusive lock (X): A transaction must acquire an X lock before modifying a record.

If transaction T1 holds an S lock on a row, another transaction T2 can also acquire an S lock, but an X lock request will be blocked.

<code>SET GLOBAL innodb_status_output=ON; -- enable output
SET GLOBAL innodb_status_output_locks=ON; -- enable lock info output
SHOW ENGINE INNODB STATUS;
</code>

The lock output shows entries such as lock_mode X locks rec but not gap , which is a record lock.

2.2 Intention Locks

Intention locks (IS and IX) are table‑level locks that indicate a transaction intends to acquire row‑level S or X locks. They do not conflict with each other and allow the engine to quickly determine whether a table can be locked.

Because InnoDB supports both table and row locks, intention locks prevent the need to scan every row to check for conflicting X locks.

2.3 Record Lock

A record lock is a simple row lock applied to a specific index entry. It blocks other transactions from inserting, updating, or deleting that row.

<code>RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc       ;;
</code>

2.4 Gap Lock

Gap locks prevent phantom reads by locking the interval between index entries. For example, lock_mode X locks gap before rec denotes an X‑type gap lock.

<code>RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;
</code>

2.5 Next‑Key Lock

A next‑key lock combines a record lock with the preceding gap lock, covering the interval (previous_key, current_key]. It is the default lock type for range scans under REPEATABLE READ.

2.6 Insert Intention Lock

Before inserting a row, InnoDB acquires an insert intention gap lock to signal the intention to insert without blocking other non‑conflicting inserts.

<code>RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;
</code>

2.7 Auto‑Increment Lock

The auto‑increment lock is a special table‑level lock for columns with the AUTO_INCREMENT attribute. It ensures that concurrent inserts generate consecutive primary key values.

An AUTO‑INC lock is taken by transactions inserting into tables with AUTO_INCREMENT columns; other transactions must wait until the lock is released.

<code>mysql> create table t0 (id int NOT NULL AUTO_INCREMENT, name varchar(16), primary key (id));
mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
</code>

3. How a Single SQL Acquires Locks

The article enumerates nine scenarios based on the query condition (primary key, unique index, ordinary index, or no index) and isolation level (RC, RR, SERIALIZABLE). For each case, it shows the exact locks taken and provides verification steps with transactions A and B.

3.1 Primary key + RC

Under READ COMMITTED, deleting a row by primary key acquires an X lock on that row.

<code>BEGIN;
DELETE FROM t1 WHERE id = 6;
</code>

Another transaction attempting to update the same row blocks until the lock is released.

3.2 Unique index + RC

Deleting by a unique index acquires two X locks: one on the unique index entry and one on the corresponding primary key record.

3.3 Ordinary index + RC

When the condition uses a non‑unique secondary index, both the matching secondary index entries and the corresponding primary key rows are locked.

3.4 No index + RC

MySQL scans the clustered index; each matching row gets an X lock, but rows that do not satisfy the condition are unlocked early for efficiency.

3.5 Primary key + RR

Under REPEATABLE READ, the locking behavior is the same as RC for primary‑key queries.

3.6 Unique index + RR

Same as RC: two X locks are taken.

3.7 Ordinary index + RR

In addition to the X lock on the row, a gap lock is added to prevent phantom inserts within the range.

3.8 No index + RR

Both X locks and gap locks are taken on all scanned rows, effectively locking the whole table.

3.9 SERIALIZABLE

Writes behave like RC/RR, but SELECT statements also acquire locks, turning read‑only queries into locking reads.

4. Locking Rules Under RR Isolation

RR uses next‑key locks as the basic unit. Two principles guide locking: (1) the lock unit is a next‑key lock, and (2) only accessed index records are locked. Optimizations cause next‑key locks to degenerate into row locks for unique‑index equality scans or into gap locks when the right‑most index entry does not satisfy the condition. A known bug causes unique‑index range scans to lock an extra gap.

4.1 Example: Equality query gap lock

Transaction A updates a row with id=6, acquiring a gap lock on (5,10). Transaction B tries to insert a row with id=9, which falls inside the locked gap and blocks, while Transaction C inserts id=10, which is outside the gap and succeeds.

4.2 Example: Non‑unique index equality lock

Transaction A acquires a next‑key lock on (0,5] and later a gap lock (5,10) on the secondary index. Updates on the primary key are not blocked, but inserts into the gap are.

4.3 Example: Primary‑key range lock

SELECT ... FOR UPDATE on id BETWEEN 10 AND 15 locks the row id=10 and a next‑key lock on (10,15]. Inserts of id=12 block, while inserts of id=6 succeed.

4.4 Example: Non‑unique index range lock

Range scans on a non‑unique index acquire next‑key locks for the entire covered interval, causing both inserts and updates within the range to block.

4.5 Example: Unique‑index range lock bug

InnoDB may scan past the last matching unique key, locking an extra gap (15,20] and causing inserts of id=16 or updates of id=20 to block.

4.6 Example: Equality with duplicate values

When a secondary index contains duplicate values, the lock range covers the duplicate interval. Inserts into the gap block, while updates on the upper bound succeed.

4.7 Example: LIMIT reduces lock range

Adding LIMIT 2 to a SELECT ... FOR UPDATE narrows the lock range to the first two matching rows, allowing other transactions to insert rows beyond the limited range.

5. How to View Transaction Lock Status

Lock information can be obtained from the information_schema tables (INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS) or by executing SHOW ENGINE INNODB STATUS . The former provides structured data about active transactions, held locks, and waiting locks, while the latter shows detailed lock logs, including lock modes (X, S, gap) and the specific index entries involved.

5.1 Using information_schema

INNODB_TRX : active transaction details (trx_id, state, isolation level, rows_locked, etc.).

INNODB_LOCKS : locks that are held or awaited (lock_mode, lock_type, lock_table).

INNODB_LOCK_WAITS : maps waiting transactions to the blocking transaction (requesting_trx_id, blocking_trx_id).

5.2 Using SHOW ENGINE INNODB STATUS

Enabling innodb_status_output_locks=ON makes the status output include lock information. The log shows entries such as TRX HAS BEEN WAITING ... FOR THIS LOCK TO BE GRANTED and details the lock mode, index, and record values.

<code>TRANSACTIONS
------------
Trx id counter 1644854
... 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
</code>

6. Hands‑On Deadlock Case Analysis

To analyze a deadlock, retrieve the latest deadlock log with SHOW ENGINE INNODB STATUS , locate the two involved transactions, examine the SQL statements they were executing, and identify the locks each transaction holds and waits for. The deadlock occurs when each transaction holds a lock needed by the other, forming a circular wait.

6.1 Simple deadlock example

Two transactions each select a different row FOR UPDATE , then attempt to update the row locked by the other, resulting in a deadlock.

<code>SELECT * FROM t6 WHERE id=10 FOR UPDATE;
SELECT * FROM t6 WHERE id=5 FOR UPDATE;
</code>

6.2 Deadlock log analysis

The log shows Transaction 1 waiting for a record lock on id=10, while Transaction 2 holds that lock and waits for a record lock on id=5, forming the deadlock cycle.

Conclusion

The article references the GeekTime "MySQL45" course and several online resources for deeper study of InnoDB locking mechanisms.