The Seven Types of Locks in InnoDB

MySQL is currently the most widely used relational database in the world, and InnoDB is its most popular storage engine. InnoDB is renowned for its strong performance under high concurrency and large data volumes. This performance is closely tied to its sophisticated locking mechanisms.

In total, InnoDB supports seven types of locks:

• Auto-Increment Locks
• Shared and Exclusive Locks
• Intention Locks
• Insert Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks

Scenario: Given a table t(id AUTO_INCREMENT, name) using the default isolation level REPEATABLE READ (RR), suppose it has existing rows:

1, li  
2, yang  
3, chen 

Transaction A runs:

insert into t(name) value ('xxx'); -- not yet committed

Transaction B runs later:

insert into t(name) value ('ooo');

Question: Will Transaction B be blocked?

Analysis: InnoDB avoids phantom reads under RR by applying locking mechanisms. Here:

1. Transaction A inserts a row (4, ‘xxx’) via the auto-increment column.
2. If Transaction B proceeds without being blocked, it will insert (5, ‘ooo’).
3. Later, Transaction A inserts another row: (6, ‘xxoo’).

When A executes:

select * from t where id > 3;

It retrieves rows 4 and 6 but not 5, as uncommitted changes from B aren’t visible under RR. This gap (missing 5) appears like a phantom, breaking the assumption of continuity for auto-increment fields.

Auto-Increment Locks

An Auto-Increment Lock is a special table-level lock taken during inserts into AUTO_INCREMENT columns to ensure consistent, gap-free key generation.

From the official documentation:

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

To configure its behaviour, the innodb_autoinc_lock_mode parameter can be adjusted.

What if it’s not AUTO_INCREMENT?

With a table t(id UNIQUE PRIMARY KEY, name) and existing records:

10, li  
20, yang  
30, chen

If Transaction A inserts id=11 and not yet committed

insert into t values(11, xxx);

and Transaction B inserts id=12 concurrently,

insert into t values(12, ooo);

auto-increment locks are not used. What locks are applied, and whether blocking occurs, will be discussed later.

Shared and Exclusive Locks

InnoDB implements standard row-level locking, including Shared (S) and Exclusive (X) locks.

• A transaction must acquire an S-lock to read a row.
• An X-lock is required to modify or delete a row.

Compatibility Matrix:

That means:

• Multiple S-locks can coexist → concurrent reads allowed.
• X-locks are exclusive → only one transaction can write or read-write.

Drawback: Limited parallelism in high-concurrency systems.
Solution: InnoDB utilises Multi-Version Concurrency Control (MVCC) to improve performance

Intention Locks

InnoDB uses multiple granularity locking, allowing row-level and table-level locks to coexist. This is enabled via Intention Locks. An intention lock signals a transaction’s intent to acquire future S or X locks on specific rows. It exists only at the table level.

Types:

• IS (Intention Shared): Intends to acquire S-locks on some rows.
• IX (Intention Exclusive): Intends to acquire X-locks on some rows.

Examples:

• select … lock in share mode → sets IS-lock.
• select … for update → sets IX-lock.

Compatibility:

• IS and IX locks are not mutually exclusive, but they conflict with S/X locks.

Insert Intention Locks

Do inserts require strong exclusive locks like updates/deletes?

Not always. This is where Insert Intention Locks, a subset of Gap Locks, come in. These are index-based locks that allow concurrent inserts within the same index range, as long as they don’t conflict.

From the official docs:
Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

Example: table with unique PK, InnoDB, RR:
t(id unique PK, name);

10, li  
20, yang  
30, chen

• Transaction A inserts id=11, not yet committed
• Transaction B inserts id=12

Although both operate in the same index gap, their insert positions don’t conflict. Thus:

• Insert Intention Locks are used.
• Transaction B is not blocked.

Summary of Lock Strategies So Far:

• Shared Locks (S) enable concurrent reads in InnoDB.
• Exclusive Locks (X) ensure serialised update/delete in InnoDB.
• Insert Intention Locks enable concurrent inserts in InnoDB.

Record Locks

Record Locks apply to individual index records. Example:

select * from t where id = 1 for update;

This locks the index entry with id=1, preventing concurrent modifications.

Note:
Regular reads like select * from t where id = 1 do not lock the row — these are snapshot reads, handled by MVCC.

Gap Locks

Gap lock locks the gaps between index records, as well as the range before the first index record or after the last one.

Example, InnoDB, RR:

t(id PK, name KEY, sex, flag);

There are four records in the table:

1, li, m, A
3, yang, m, A
5, chen, m, A
9, huang, f, B

Query:

select * from t where id between 8 and 15 for update;

This locks the gap, preventing other transactions from inserting, say, id=10.

Purpose:

• Avoids “phantom reads” by locking non-existent rows.
• Only effective under REPEATABLE READ (RR).
• Automatically disabled under READ COMMITTED (RC).

Next-Key Locks

A Next-Key Lock is a combination of a Record Lock + Gap Lock. It locks:

• The indexed row itself.
• Including the index range.

From the docs:
If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

Example, InnoDB, RR:

t(id PK, name KEY, sex, flag);

with records:

1, li, m, A
3, yang, m, A
5, chen, m, A
9, huang, f, B

The potential next-key lock on the primary key is:

(-infinity, 1], (1, 3], (3, 5], (5, 9], (9, +infinity)

Purpose:

Also to prevent phantom reads, effective under RR, disabled under RC.

Summary