MySQL死锁套路

# 构造数据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";

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

第一条语句

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

在调试中得到的结果如下

可以看到这条语句对唯一键 uk_name 加共享锁(S锁),而且成功。

第二条语句

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 执行成功

详细的锁状态变化如下

死锁日志如下:

LATEST DETECTED DEADLOCK------------------------181208 23:00:52*** (1) TRANSACTION:TRANSACTION 53A7, ACTIVE 162 sec starting index readmysql tables in use 1, locked 1LOCK 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 Updatingupdate 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 waitingRecord 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 readmysql tables in use 1, locked 13 lock struct(s), heap size 376, 2 row lock(s)MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updatingupdate 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 SRecord 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 waitingRecord 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) 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 锁的记录锁)

*** (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 锁(共享锁)

*** (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 锁的记录锁)

跟之前理论上推断的结论是一致。

本文分享自微信公众号 - 数据和云(OraNews)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券