Uncovering Gap‑Lock Deadlocks in MyBatis‑Plus saveOrUpdate and Effective Solutions

1. Scenario Reconstruction

The environment uses MySQL 5.6.36‑82.1‑log with InnoDB storage engine and Mybatis‑Plus starter 3.3.2. In production, a colleague (A) invoked

com.baomidou.mybatisplus.extension.service.IService#saveOrUpdate(T, com.baomidou.mybatisplus.core.conditions.Wrapper)

(referred to as method B) under concurrent load, which resulted in a deadlock error shown in the screenshot.

2. Why Gap‑Lock Causes the Deadlock?

A deadlock occurs when two transactions wait for each other's locks. The article first clarifies basic concepts:

Deadlock: Two transactions each hold a lock the other needs, causing mutual blocking.

Gap lock: A type of InnoDB row lock that locks the interval (gap) between index values, not the record itself.

Gap locks combine with record locks to form next‑key locks , preventing phantom reads under REPEATABLE READ isolation.

MySQL acquires a gap lock on the interval surrounding the target index value. When two concurrent transactions both try to insert into each other's gaps, each obtains a gap lock that the other needs, leading to a deadlock.

The source code of method B is:

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

Because the method attempts an update before checking existence, it may acquire a gap lock during the update phase. The InnoDB status logs captured with SHOW ENGINE INNODB STATUS show two transactions waiting for gap locks:

*** (1) TRANSACTION:
... (omitted)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 347 n bits 80 index `PRIMARY` of table `database_name`.`table_name` trx id 71C lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:
... (omitted)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 347 n bits 80 index `PRIMARY` of table `database_name`.`table_name` trx id 71D lock_mode X locks gap before rec insert intention waiting

Thus, each transaction holds a gap lock that the other needs, producing a classic deadlock.

3. Reproducing the Issue

Two verification scenarios are provided.

Verification 1 – Direct Gap‑Lock Deadlock

CREATE TABLE t_gap_lock(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(64) NOT NULL COMMENT '名称',
    age INT NOT NULL COMMENT '年龄'
) COMMENT '间隙锁测试表';

Initial data:

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  18 |
|  5 | 李四 |  19 |
|  6 | 王五 |  20 |
|  9 | 赵六 |  21 |
| 12 | 孙七 |  22 |
+----+------+-----+

Transaction 1:

BEGIN;
UPDATE t_gap_lock SET age = 25 WHERE id = 4; -- no row, acquires gap lock
INSERT INTO t_gap_lock(id, name, age) VALUES (7, '间隙锁7', 27); -- blocks

Transaction 2 (simultaneous):

BEGIN;
UPDATE t_gap_lock SET age = 25 WHERE id = 7; -- no row, acquires another gap lock
INSERT INTO t_gap_lock(id, name, age) VALUES (4, '间隙锁4', 24); -- deadlock error 1213

Querying INFORMATION_SCHEMA.INNODB_LOCKS shows both transactions holding X,GAP locks on the same interval.

Verification 2 – More Realistic Flow

Both transactions first execute an UPDATE that matches no rows (thus acquiring a gap lock). Then each attempts an INSERT into the other's gap. The first INSERT blocks, the second INSERT immediately triggers a deadlock, confirming the gap‑lock deadlock pattern observed in production.

Additional test shows that when updating an existing row (e.g., WHERE id = 1), MySQL acquires a regular Record Lock (mode X) without a gap component, and the lock is mutually exclusive, preventing the gap‑lock deadlock scenario.

4. How to Resolve?

Disable gap locks (not recommended): Lower isolation level (e.g., READ COMMITTED) or set innodb_locks_unsafe_for_binlog=1 in my.cnf. This sacrifices protection against phantom reads.

Customize saveOrUpdate (recommended): Implement a tailored method that avoids the extra reflection and transaction overhead of the built‑in Mybatis‑Plus version. Example implementation:

@Transactional(rollbackFor = {Exception.class})
public boolean saveOrUpdate(T entity) {
    if (entity == null) {
        return false;
    }
    Class<?> cls = entity.getClass();
    TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
    Assert.notNull(tableInfo, "error: cannot find TableInfo cache for entity!");
    String keyProperty = tableInfo.getKeyProperty();
    Assert.notEmpty(keyProperty, "error: cannot find key column for entity!");
    Object idVal = ReflectionKit.getFieldValue(entity, tableInfo.getKeyProperty());
    return !StringUtils.checkValNull(idVal) && Objects.nonNull(this.getById((Serializable) idVal))
           ? this.updateById(entity) : this.save(entity);
}

5. Extensions

The article explores what happens when both transactions modify existing rows. In that case MySQL uses a standard record lock (mode X) without a gap component, which is exclusive and prevents the deadlock pattern.

6. Conclusion

Although Mybatis‑Plus’s saveOrUpdate can cause gap‑lock deadlocks, the framework remains valuable for rapid development. Developers should understand the locking behavior, avoid the built‑in method in high‑concurrency scenarios, or adjust isolation settings with caution.