Mysql死锁案例分析

 前言

通过《mysql锁机制》初步了解mysql的锁机制，通过几个死锁场景分析更加深入了解锁。

场景1：

唯一索引批量插入，引起的插入间隙死锁。

// 隔离级别RC

CREATE TABLE `z` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`a`),
  UNIQUE KEY `b` (`b`)
);

BEGIN;
INSERT INTO `z` VALUES (3, 1, 2);
INSERT INTO `z` VALUES (5, 3, 0);
INSERT INTO `z` VALUES (7, 6, 0);
INSERT INTO `z` VALUES (10, 8, 5);
COMMIT;


// 事务1
BEGIN;
delete from  z where b = 3; // 1
delete from  z where b = 8; // 1 
insert INTO z  SELECT 5,3,0; // 1   
insert INTO z  SELECT 10,8,0; // 3
commit;


// 事务2
BEGIN;
insert INTO z  SELECT 11,9,0; // 2
insert INTO z  SELECT 4,2,0;  // 4
commit;

按上述标号1-4执行：

1. 事务1执行(1)标识的语句：2句delete分别(3),(8)的记录锁；insert语句触发索引唯一检查，对(1,6)的间隙锁
2. 事务2执行(2)标识的语句：insert获取(9)的插入意向锁
3. 事务1执行(3)标识的语句：insert语句触发索引唯一检查想要(6,正无穷)间隙，等待（9）记录锁
4. 事务2执行(4)标识的语句：insert想获取(2)的插入意向锁等待(1,6)的间隙锁

场景2：

二级索引引发的死锁(概率事件)

CREATE TABLE `z` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`a`),
  KEY `b` (`b`,`c`)
);

BEGIN;
INSERT INTO `z` VALUES (1, 1, 2);
INSERT INTO `z` VALUES (3, 1, 2);
INSERT INTO `z` VALUES (5, 3, 0);
INSERT INTO `z` VALUES (7, 6, 0);
INSERT INTO `z` VALUES (10, 8, 5);
COMMIT;


// 事务1

BEGIN;
UPDATE z set c = 3 where b = 1; // 1
COMMIT;

// 事务2
BEGIN;
update z set c = 5 where a = 1; // 2
COMMIT;

// 事务3
BEGIN;
UPDATE z set c = 4 where b = 1; // 3
COMMIT

按上述标号1-3执行：

1. 事务1的加载顺序为：b索引上(1,2,1),(1,2,3)记录锁，然后获取a索引（1）（3）记录锁
2. 事务2：等待a索引（1）的记录锁
3. 事务3：等待b索引上(1,2,1)记录锁
4. 事特1提交释放锁：事务2获取a索引（1）的记录锁，事务3获取b索引上(1,2,1)记录锁
5. 事务2等待b索引上(1,2,1)记录锁，事务3获取a索引上(1)记录锁,死锁