Analyzing Gap‑Lock Deadlocks in MySQL When Using MyBatis‑Plus saveOrUpdate

1. Scenario Reconstruction

Version information

MySQL version: 5.6.36-82.1-log
Mybatis-Plus starter version: 3.3.2
Storage engine: InnoDB

Deadlock phenomenon

A developer in production used MyBatis‑Plus's com.baomidou.mybatisplus.extension.service.IService#saveOrUpdate(T, com.baomidou.mybatisplus.core.conditions.Wrapper) (referred to as method B) under concurrent load, and MySQL reported a deadlock error.

2. Why Is It a Gap‑Lock Deadlock?

2.1 What is a deadlock? Two transactions wait for each other's locks, causing mutual blocking.

2.2 What is a gap lock? A MySQL row‑lock type that locks the interval (gap) between index values rather than a specific record.

MySQL finds the nearest smaller and larger index values and locks the interval to prevent other transactions from inserting into that gap.

2.3 Why does MySQL introduce gap locks? Combined with record locks they form a next‑key lock, which under REPEATABLE READ isolation prevents phantom reads.

2.4 Gap‑lock deadlock analysis

The source of method B is:

default boolean saveOrUpdate(T entity, Wrapper
updateWrapper) {
    return this.update(entity, updateWrapper) || this.saveOrUpdate(entity);
}

The method updates first, then falls back to save, which can cause MySQL to acquire gap locks during the update phase. The InnoDB status log shows two transactions each waiting for a gap lock (X,GAP) on the same primary‑key interval, creating a classic deadlock.

2.5 Verifying the Gap‑Lock Deadlock

We create a test table:

create table t_gap_lock(
    id int auto_increment primary key comment 'primary key ID',
    name varchar(64) not null comment 'name',
    age int not null comment 'age'
) comment 'gap‑lock test table';

Insert sample rows (ids 1,5,6,9,12). Then start two transactions without committing:

-- Transaction 1
begin;
update t_gap_lock t set t.age = 25 where t.id = 4; -- no row matched
insert into t_gap_lock(id, name, age) value (7, 'gap‑lock 7', 27); -- blocks

-- Transaction 2 (in another session)
begin;
update t_gap_lock t set t.age = 25 where t.id = 7; -- no row matched
insert into t_gap_lock(id, name, age) value (4, 'gap‑lock 4', 24); -- deadlock error

Running SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS \G; shows two X,GAP locks on the same gap, confirming the deadlock.

A second verification ("Verification 2") repeats the pattern with both transactions updating the same non‑existent row first, then inserting into each other's gap, reproducing the deadlock and demonstrating that gap locks are non‑exclusive.

3. How to Resolve It?

3.1 Disable gap locks (not recommended)

Lower isolation level (e.g., READ COMMITTED).

Set innodb_locks_unsafe_for_binlog=1 in my.cnf (disables gap locks).

These approaches sacrifice phantom‑read protection and should only be used when that risk is acceptable.

3.2 Custom saveOrUpdate implementation (recommended)

Write a tailored saveOrUpdate that avoids the extra reflection and transaction overhead of the framework method:

@Transactional(rollbackFor = {Exception.class})
public boolean saveOrUpdate(T entity) {
    if (entity == null) {
        return false;
    } else {
        Class
cls = entity.getClass();
        TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
        Assert.notNull(tableInfo, "error: cannot execute because TableInfo cache is missing", new Object[0]);
        String keyProperty = tableInfo.getKeyProperty();
        Assert.notEmpty(keyProperty, "error: cannot execute because ID column is missing", new Object[0]);
        Object idVal = ReflectionKit.getFieldValue(entity, tableInfo.getKeyProperty());
        return !StringUtils.checkValNull(idVal) && !Objects.isNull(this.getById((Serializable) idVal))
            ? this.updateById(entity) : this.save(entity);
    }
}

4. Extension

4.1 What if both transactions modify an existing row?

When updating a row that actually exists, MySQL acquires a regular record lock (X) instead of a gap lock. Record locks are exclusive, so the second transaction blocks until the first commits, preventing a gap‑lock deadlock.

5. Conclusion

Although MyBatis‑Plus's default saveOrUpdate can cause gap‑lock deadlocks, the framework remains valuable for rapid development. Developers should understand the locking behavior, use custom implementations when necessary, and apply appropriate isolation levels.

Author: Xie Xing, backend engineer at ZhaiZhai Finance Technology, passionate about coding and open‑source sharing.