MySQL那些场景下会造成死锁？-月伴飞鱼

1.批量更新id没排序。update 1,2 另外一个update 2,1，方案是id排序。

2.update 条件不一样导致死锁。尽量主键更新。

update my_table set name = 'test',age = 22 where name = "hollischuang";
这个SQL会先对name加锁， 然后再回表对id加锁。

select * from my_table where id = 15 for update;

update my_table set age = 33 where name like "hollis%";

以上SQL，会先获取主键的锁，然后再获取name的锁。

3.插入间隙锁导致的死锁。

4.并发插入唯一索引导致的死锁。

表和数据准备:

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);

死锁分析

查看事务的锁情况

SELECT * FROM INFORMATION_SCHEMA.data_locks;

使用 SHOW ENGINE INNODB STATUS; 命令来查看死锁日志。

关键分析

对于 INSERT 操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上 Share Record Lock + Gap Lock（即使是 RC 事务隔离级别）。

时间线维度分析

事务 T2 执行
```
INSERT INTO t7(id, a) VALUES(26, 10);
```
语句执行成功，持有 a=10 的 X 行锁 (X locks rec but not gap)。
事务 T1 执行
```
INSERT INTO t7(id, a) VALUES(30, 10);
```
由于 T2 的 INSERT 语句已插入 a=10，事务 T1 的 INSERT a=10 发生唯一约束冲突，需要申请对冲突的唯一索引 a=10 加上 Share Record Lock + Gap Lock（lock mode S waiting）。
这是一个间隙锁，会锁住 (4,10) 之间的 gap 区域。即使在 RC 事务隔离级别下，也会存在 Next-Key Lock，导致阻塞并发。此时事务 T1 持有 (4,10) 的 Gap Lock，并等待 a=10 上的 Share Lock。
事务 T2 执行
```
INSERT INTO t7(id, a) VALUES(40, 9);
```
插入 a=9 需要先获取插入意向 Gap 锁 (4,10)，而 (4,10) 处于事务 T1 申请的 Gap Lock 保护范围内，因此事务 T2 需要等待事务 T1 释放 (4,10) 的 Gap Lock。
日志中会显示 lock_mode X locks gap before rec insert intention waiting，此时事务 T2 持有 a=10 上的 X Lock，并等待 (4,10) 的插入意向 Gap Lock。

综上，形成死锁。

解决方案

InnoDB 会选择资源最小的事务进行回滚，另一个事务执行成功。可采取以下措施：

尽量避免大事务，降低锁冲突的可能性。
死锁回滚后，记录原始 SQL，手动处理。

死锁回滚记录原始 SQL

try {
    // 事务代码
} catch (DataAccessException e) {
    if (e.getCause() instanceof MySQLTransactionRollbackException) {
        // 遇到 MySQL 死锁异常后，记录 SQL，人工处理插入数据
        log.error("Caught MySQLTransactionRollbackException, manualSql={}", generateInsertSQL(records));
    }
}