Detailed Explanation of MySQL InnoDB Lock Mechanisms

Introduction

A few months ago I needed strong consistency for a project that used transactions, which led me to use MySQL locks. My understanding of MySQL's lock mechanism was superficial, so I decided to summarise the various InnoDB locks in MySQL 5.7.

Overview

InnoDB defines seven types of locks:

Shared/Exclusive Locks

Intention Locks

Record Locks

Gap Locks

Next‑key Locks

Insert‑Intention Locks

Auto‑increment Locks

1. Shared/Exclusive Locks

Shared lock (S lock) is acquired when reading data.

Exclusive lock (X lock) is acquired when modifying data.

Characteristics:

Shared locks do not block each other (read‑read can run concurrently).

Exclusive locks block all other locks (write‑read and write‑write cannot run concurrently).

When a write transaction has not committed, other sessions cannot read the locked rows; only snapshot reads (non‑locking SELECT) can see the data.

2. Intention Locks

To support multiple‑granularity locking (row‑level and table‑level coexistence), InnoDB introduces intention locks, which are table‑level declarations of future row‑level locks.

Intention Shared lock (IS) indicates that the transaction intends to acquire S locks on some rows.

Intention Exclusive lock (IX) indicates that the transaction intends to acquire X locks on some rows.

Lock syntax examples:

select ... lock in share mode;   // request IS lock
select ... for update;           // request IX lock

Compatibility matrix (IS/IX with each other): both are compatible. However, intention locks are mutually exclusive with S/X locks as shown in the following tables.

IS

IX

IS

compatible

compatible

IX

compatible

compatible

S

X

IS

compatible

incompatible

IX

incompatible

incompatible

Exclusive locks are the strongest and never compatible with any other lock type.

3. Record Locks

Record locks protect individual index records. Example table definition:

create table lock_example(
  id smallint(10),
  name varchar(20),
  primary key(id)
) engine=innodb;

With REPEATABLE READ isolation, a SELECT … FOR UPDATE on a specific row first acquires an IX lock on the table and then an X lock on the chosen record.

select * from lock_example where id=1 for update;

4. Gap Locks

Gap locks protect the interval between index records (or before the first/after the last record). They prevent other transactions from inserting rows into the locked range.

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

This statement locks the range (8,15). Gap locks are only effective under REPEATABLE READ; they disappear under READ COMMITTED.

5. Next‑key Locks

Next‑key locks combine a record lock with the surrounding gap lock, protecting both the index entry and its interval. When the index column is unique, InnoDB can downgrade a next‑key lock to a simple record lock.

Example:

-- Transaction A (not yet committed)
select * from lock_example where id = 20 for update;
-- Transaction B attempts to insert into the same gap and will block
insert into lock_example values('zhang', 15);

Because Transaction A holds a next‑key lock on id = 20, any insert into the interval [10,30) is blocked, preventing phantom reads. The lock also disappears under READ COMMITTED.

6. Insert‑Intention Locks

Insert‑intention locks are a special kind of gap lock used for INSERT statements. Multiple transactions can insert into the same gap concurrently as long as they target different positions, avoiding unnecessary blocking.

insert into lock_example values(11, 'xxx');
insert into lock_example values(12, 'ooo');

Both inserts succeed without waiting because they do not conflict on the exact insertion point.

7. Auto‑increment Locks

Auto‑increment locks are table‑level locks taken when a transaction inserts into an AUTO_INCREMENT column. They ensure that generated primary‑key values are consecutive.

A transaction inserting into an AUTO_INCREMENT column holds an AUTO‑INC lock; other inserting transactions must wait until the lock is released.

Example:

-- Transaction A (not committed)
insert into lock_example(name) values('xxx');
-- Transaction B will block until A commits
insert into lock_example(name) values('ooo');

Summary

The seven lock types can be grouped by mutual exclusion level (shared vs. exclusive) and by granularity (table‑level vs. row‑level). Shared/Intention‑Shared locks improve read concurrency, while exclusive/Intention‑Exclusive locks guarantee strong consistency. Row‑level locks (record, gap, next‑key, insert‑intention) protect individual records or intervals, whereas table‑level locks (intention, auto‑increment) coordinate access to the whole table.