Understanding MySQL Lock Types: Shared, Exclusive, Intention, Gap, Next‑Key, and Insert‑Intention Locks with Deadlock Analysis

The article provides a comprehensive overview of MySQL lock types used by InnoDB, describing their purpose, scope, and interaction.

Shared (S) and Exclusive (X) Locks

S lock: shared lock that allows concurrent reads but blocks exclusive locks. X lock: exclusive lock that permits data updates and blocks both shared and other exclusive locks. Note: a plain SELECT * FROM user is a snapshot read without any lock.

-- S lock
select * from user where id=1 lock in share mode;

-- X lock
select * from user where id=1 for update;
update user set name='zhangsan' where id=1;
delete from user where id=1;
insert into user

Intention Locks (IS, IX)

Before a transaction acquires row‑level locks, it must obtain an intention lock at the table level: IS for shared intent, IX for exclusive intent.

Intention locks are table‑level and do not conflict with row‑level S or X locks, but they do conflict with table‑level S and X locks.

Intention Shared (IS) lock: required before acquiring row‑level shared locks.

Intention Exclusive (IX) lock: required before acquiring row‑level exclusive locks.

Record Lock

Record lock (also called row lock) is applied to rows selected by primary key or unique index when using SELECT ... LOCK IN SHARE MODE or SELECT ... FOR UPDATE:

select * from table where id=1 lock in share mode;
select * from table where id=1 for update;
update table set name = 'zhangsan' where id = 1;
delete from table where id = 1;

Gap Lock

Gap lock (interval lock) locks only the gap between index records (an open interval) without locking the records themselves. Non‑unique index reads acquire gap locks.

-- seq_id is a non‑unique index
select * from table where seq_id=3 lock in share mode;
select * from table where seq_id=3 for update;
update table set name = 'zhangsan' where seq_id = 3;
delete from table where seq_id = 3;

Next‑Key Lock

Next‑key lock = record lock + gap lock (left‑open, right‑closed interval). InnoDB uses it to prevent phantom reads.

Example table data:

id

seq_id

4

1

5

3

6

5

7

7

8

9

When executing:

select * from table where seq_id=3 lock in share mode;

The lock situation is:

Record lock on the row (seq_id=3, id=5).

Gap lock on interval [1,4)~[3,5).

Gap lock on interval [3,5)~[5,6).

Insert‑Intention Lock

Insert‑intention lock is a special form of gap lock set before an INSERT operation. It signals that the transaction intends to insert into a gap, allowing concurrent inserts into different positions within the same gap.

If a unique index conflict occurs during insert, MySQL adds a Share Record Lock and a Gap Lock, which can lead to deadlocks.

Deadlock Example with Concurrent Inserts

Table definition:

create table test(
  id int not null primary key auto_increment,
  a int not null,
  unique key ua(a)
) engine=innodb;

insert into test(id,a) values(1,1),(5,4),(20,20),(25,12);

Two transactions (T1 and T2) run concurrently with autocommit disabled and isolation level READ COMMITTED. The sequence of statements leads to a deadlock when T1 tries to insert a duplicate value for column a while T2 holds a gap lock needed by T1, and vice‑versa.

Deadlock Analysis

T2 inserts (26,10) successfully, holding an X row lock on a=10.

T1 attempts to insert (30,10); a unique‑key conflict forces it to request a Share Record Lock + Gap Lock on the interval (4,10), which it waits for.

T2 then tries to insert (40,9); it needs an insert‑intention gap lock on (4,10), which is held by T1, so T2 waits.

Both transactions wait on each other's locks, creating a deadlock.

Resolution

Avoid large transactions to reduce lock contention.

After InnoDB rolls back the victim, record the original SQL and handle the conflict manually.

try {
    // transaction code
} catch (DataAccessException e) {
    if (e.getCause() instanceof MySQLTransactionRollbackException) {
        // Log the original SQL for manual handling
        log.error("Caught MySQLTransactionRollbackException, manualSql={}", generateInsertSQL(records));
    }
}

References

MySQL Official Documentation: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/innodb-locks-set.html

Deep analysis of MySQL REPLACE deadlock issues: https://github.com/OnelongX/learning/blob/master/mysql/MySQL%20REPLACE%E6%AD%BB%E9%94%81%E9%97%AE%E9%A2%98%E6%B7%B1%E5%85%A5%E5%89%96%E6%9E%90.pdf