数据库死锁, 是最难调试与追踪的.
场景如下:
同一个表, 事务内先插入一条记录, 再更新这条记录, 并发时 会死锁.
并且能够复现.
可以通过什么工具模拟并发事务, 查看信息, 解决问题呢? 这是今天要分享的内容.
一, 前置准备
- set session transaction isolation level repeatable read;
- set session autocommit=0;
- create table t (
- id int(20) primary key AUTO_INCREMENT,
- cell varchar(20) unique
- )engine=innodb;
- start transaction;
- insert into t(cell) values(11111111111);
- insert into t(cell) values(22222222222);
- insert into t(cell) values(33333333333);
- commit;
说明:
(1)案发时, 事务隔离级别 RR ;
(2)多终端实验, 需要 关闭事务自动提交 ;
(3)建表, 设置 PK 与 unique, 初始化数据;
二, 并发事务模拟
- Session A:
- start transaction;
- insert into t(cell)values(44444444444); [1]
- Session B:
- start transaction;
- insert into t(cell) values(55555555555); [2]
- update t set cell=123 where cell=44444444444; [3]
- update t set cell=456 where cell=55555555555; [4]
开启两个终端模拟并发事务:
(1)红色 SQL 为事务 A;
(2)黑色 SQL 为事务 B;
(3)[1][2][3][4]为执行时序;
三, 实验现象
insert into t(cell)values(44444444444); [1]
事务 A 插入数据, 最先执行
结果: 插入成功
insert into t(cell) values(55555555555); [2]
事务 B 插入数据, 第二执行
结果: 插入成果
update t set cell=123 where cell=44444444444; [3]
事务 A 修改 [1] 中插入的数据, 第三执行
结果: 阻塞 , 等待执行结果
画外音: 修改一条自己插入的数据, 在等待什么呢?
update t set cell=456 where cell=55555555555; [4]
事务 B 修改 [2] 中插入的数据, 最后执行
结果:
(1) 事务 B 死锁, 事务 B 被回滚 ;
(2) 事务 A 中,[3]语句阻塞结束, 执行成功 ;
画外音: 说明事务 A 中阻塞的语句, 确实在等事务 B 中的某个锁.
四, 结果分析
两个事务, 各自修改自己插入的数据 , 却产生了 死锁 , 确实诡异.
上述实验现象的 两个核心问题 是:
(1) 语句 [3] 阻塞, 在等待什么锁 ?
(2) 语句 [4] 死锁 , 此时事务 A 和事务 B 一定是彼此占住一把锁, 请求彼此的锁, 这些锁又是什么呢 ?
工具一:
show engine innodb status;
画外音: 前文《 超赞, InnoDB 调试死锁的方法! 》就详细分享过, InnoDB 死锁的分析实践.
执行之后, 显示的内容如下(放大仔细看):
信息很多, 别急, 楼主娓娓道来.
第一部分, 关键词是:
(1) Transaction 1 , 事务 3998;
(2)在执行
update t set cell=123 where cell=44444444444;
(3)正在等待锁释放 (waiting for this lock to be granted) , 记录锁 ( record locks ) , 主键索引上 ( index primary ) , 互斥锁 ( lock_mode X ) , 物理记录 ( physical record ) ,asc 55555555555;
画外音: 英文比较差没事, 抓关键词.
画外音, InnoDB 存储引擎, 聚集索引与非聚集索引的实现方式, 决定了锁会加在聚集索引上, 详见文章:
《 1 分钟了解 MyISAM 与 InnoDB 的索引差异 》.
第二部分, 关键词是:
(1) Transaction 2 , 事务 3999;
(2)正在执行
update t set cell=456 where cell=55555555555;
(3)持有锁 ( holds the lock ) , 记录锁 ( record locks ) , 主键索引上 ( index primary ) , 互斥锁 ( lock_mode X ) , 物理记录 ( physical record ) ,asc 55555555555;
(4)正在等待锁释放 ( waiting for this lock to be granted ) , 记录锁 ( record locks ) , 主键索引上 ( index primary ) , 互斥锁 ( lock_mode X ) , 物理记录 ( physical record ) ,asc 11111111111;
(5)事务 2 回滚 ( we roll back transaction 2 ) ;
通过 show engine innodb status; 能够看到很多事务与锁之间的信息, 对分析问题十分有帮助, 这些信息, 能够解释一些问题, 但 仍有两个疑 惑:
(1) 事务 1 为啥想拿 55555555555 的锁?
画外音: 这正是, 事务 1 被阻塞的原因.
(2) 事务 2 为啥想拿 11111111111 的锁? 死锁的发生, 说明事务 1 此时真占着 11111111111 的锁, 这又是为什么呢?
画外音: 第一个事务占 111 抢 555, 第二个事务占 555 抢 111, 循环嵌套, 才会死锁.
工具二:
explain
为了进一步寻找原因, 可以通过 explain 看下导致死锁语句的执行计划.
explain update t set cell=456 where cell=55555555555;
select_type :SIMPLE
这是一个简单类型的 SQL 语句, 不含子查询或者 UNION.
type :index
访问类型, 即找到所需数据使用的遍历方式, 潜在的方式有:
(1)ALL(Full Table Scan): 全表扫描;
(2) index: 走索引的全表扫描;
(3)range: 命中 where 子句的范围索引扫描;
(4)ref/eq_ref: 非唯一索引 / 唯一索引单值扫描;
(5)const/system: 常量扫描;
(6)NULL: 不用访问表;
上述扫描方式, ALL 最慢, 逐步变快, NULL 最快.
怀疑点 1: 明明 cell 字段有 uniq 索引, 为何要进行走 PK 索引的全表扫描呢?
possible_keys :NULL
可能在哪个索引找到记录.
key :PRIMARY
实际使用索引.
画外音: 使用 PK 进行的全表扫描.
ref :NULL
哪些列, 或者常量用于查找索引上的值.
怀疑点 2:where 条件中的查询条件 55555555555, 本来应该作为在索引上被检索的值呀?
rows :5
找到所需记录, 预估需要读取的行数.
怀疑点 3: 明明修改的是 5, 为何初始化的 1,2,3, 以及第一个事务插入的 4, 以及第二个事务插入的 5, 都要被读取呢? 不应该全表扫描呀.
通过 explain, 基本已经可以判断:
update t set cell=456 where cell=55555555555;
并没有和我们预想一样, 走 cell 索引进行查询, 而是走了 PK 索引 进行了全表扫描 .
再仔细一看:
- create table t (
- id int(20) primary key AUTO_INCREMENT,
- cell varchar(20) unique
- )engine=innodb;
建表 的时候 cell 定义的是 字符串类型 .
而 更新 的时候,
update t set cell=456 where cell=55555555555;
使用的是 整数类型 .
类型转换, 会导致全表扫描, 出现锁升级, 锁住全部记录 .
加上引号, 再次通过 explain 验证一下:
explain update t set cell= '456' where cell= '55555555555';
果然印证了猜想:
(1)type:range, 变为了走索引的字符串比对, 范围扫描;
(2)possible_keys:cell, 通过 cell 索引找到了记录;
(3)key:cell, 实际使用 cell 索引;
(4)ref:const, 使用了常量'555'进行比对;
(5)rows:1, 预估读取行数是 1;
这下全部可以解释了 .
总结
就本例而言: 需要注意字符串与整数之间的强制类型转换 , 有时候少一个引号, 就会使得行锁升级为表锁.
死锁是 MySQL 中非常难调试的问题, 常见的思路与方法有:
(1)通过多终端模拟并发事务, 复现死锁;
(2)通过 show engine innodb status; 可以查看事务与锁的信息;
(3)通过 explain 可以查看执行计划;
思路 比结论更重要, 希望大家有收获.
架构师之路 - 分享技术思路
来源: http://www.tuicool.com/articles/6ZNJBfR