通过唯一索引S锁与X锁来了解MySQL死锁套路

在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL  源码的方式来查看死锁的过程，这篇文章来讲讲一个常见的案例。
这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇

我们来看一个简化过的例子

?

1 2 3 4 5 6 7 8 9 10 11 12 13

# 构造数据 CREATE TABLE `t1` (   `id` int (11) NOT NULL AUTO_INCREMENT,   ` name ` varchar (10),   ` level ` int (11),   PRIMARY KEY (`id`),   UNIQUE KEY `uk_name` (` name `) ); INSERT INTO `t1` (` name `, ` level `) VALUES ( 'A' ,0);   # 出现问题的sql语句如下，并发情况下就会出现死锁 INSERT ignore INTO `t1` (` name `, ` level `) VALUES ( 'A' ,0); update t1 set level = 1 where name = "A" ;

我们用之前介绍过的源码分析方式，先来看下这两条语句分别加什么锁，然后分析死锁形成的过程。

第一条语句

?

1	INSERT ignore INTO t1 ( name , level ) VALUES ( 'A' ,0);

在调试中得到的结果如下

可以看到这条语句对唯一键 uk_name 加共享锁（S锁），而且成功。

第二条语句

?

1	update t1 set level = 1 where name = "A" ;

 通过唯一键更新数据库字段。

这种情况在之前的文章已经介绍过，会对唯一索引加 X 锁，然后对主键索引加 X 锁

这样就可以非常轻松的复现死锁的问题了，步骤如下

1.开启两个 session，分别 begin
2.session1 执行INSERT ignore INTO t1 (name, level) VALUES ('A',0);
3.session2 执行INSERT ignore INTO t1 (name, level) VALUES ('A',0);
4.session1 执行update t1 set level = 1 where name = "A"; 进入等待状态
5.session2 执行update t1 set level = 1 where name = "A";，死锁产生，被回滚，同时事务 1 执行成功

详细的锁状态变化如下

t1	t2	备注
INSERT IGNORE INTO	-	t1成功获得uk的S锁 DB_SUCCESS
-	INSERT IGNORE INTO	t2成功获得uk的S锁 DB_SUCCESS
UPDATE	-	t1尝试获得uk的X锁，但没有成功，处于等待状态 DB_LOCK_WAIT
-	UPDATE	t2尝试获得uk的X锁，发现死锁产生 DB_DEADLOCK
-	Deadlock	t2释放S锁
成功	-	-

死锁日志如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

LATEST DETECTED DEADLOCK ------------------------ 181208 23:00:52 *** (1) TRANSACTION : TRANSACTION 53A7, ACTIVE 162 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 12, OS thread handle 0x700010522000, query id 1424 localhost root Updating update t1 set level = 1 where name = "A" *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A7 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0   0: len 1; hex 41; asc A;;   1: len 4; hex 80000001; asc ;;   *** (2) TRANSACTION : TRANSACTION 53A8, ACTIVE 8 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updating update t1 set level = 1 where name = "A" *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock mode S Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0   0: len 1; hex 41; asc A;;   1: len 4; hex 80000001; asc ;;   *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0   0: len 1; hex 41; asc A;;   1: len 4; hex 80000001; asc ;;   *** WE ROLL BACK TRANSACTION (2)

来详细看一下这个死锁日志

?

1 2	*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting

事务 1 想获取 uk_name 唯一索引上的 X 锁 （非 gap 锁的记录锁）

?

1 2	*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S

事务 2 持有uk_name 唯一索引上的 S 锁（共享锁）

?

1 2	*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock_mode X locks rec but not gap waiting

事务 2 想获得 uk_name 唯一索引上的 X 锁（非 gap 锁的记录锁）
跟之前理论上推断的结论是一致的

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：https://juejin.im/post/5ce3cfa46fb9a07ece67a4a9