insert 导致的死锁现场一

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第4天，点击查看活动详情。

今天来看一个 MySQL insert 死锁现场。

环境准备

建表语句如下，注意这个表存在一个唯一索引。

CREATE TABLE lock_test1 (
    id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(45) NOT NULL,
    age INT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX `uniq_user_name` (user_name ASC)
);

基本情况

数据库版本及事务隔离级别如下。

-- 5.7.10-log
select @@version;
-- REPEATABLE-READ
select @@tx_isolation;

现场复原

开启三个 session，执行同样的 insert 语句，然后 session A 执行回滚，即可复现死锁。

原因分析

t2 时刻查询一下锁情况

select * from information_schema.innodb_locks;

根据上面的锁情况，来分析一下原因。

t1 时刻，session A 执行完 insert 语句后，加了 X 锁，t2 时刻，session B 执行完 insert 语句后，等待 S 锁，同样 session C 执行完 insert 语句后，等待获取 S 锁。t3 时刻，session A 执行 rollback，释放了 X 锁，这时，session B、session C 都想加 X 锁，但是它们还都持有 S 锁，最终导致了死锁。

疑问解释

• insert 语句加什么锁？

  INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

  MySQL 官方文档描述，insert 加 X 记录锁，非 next-key lock。

• session B 为什么加 S 锁？

  If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

  MySQL 官方文档描述，发生唯一键冲突，会在索引上加 S 记录锁。

总结

今天的 insert 死锁现场的原因是唯一键冲突。

参考文档

MySQL 5.6 Reference Manual

《MySQL 实战 45 讲》