工业核信息化部网站备案系统,网站设置文件夹权限,品牌推广策略怎么写,网站建设税费提示#xff1a;文章写完后#xff0c;目录可以自动生成#xff0c;如何生成可参考右边的帮助文档 文章目录 案例分析生产背景死锁日志表结构执行计划 EXPLAN为什么会用 index_merge#xff08;索引合并#xff09;为什么用了 index_merge就死锁了解决方案注#xff1a;M… 提示文章写完后目录可以自动生成如何生成可参考右边的帮助文档 文章目录 案例分析生产背景死锁日志表结构执行计划 EXPLAN为什么会用 index_merge索引合并为什么用了 index_merge就死锁了解决方案注MySQL官方已经确认了此bug77209 案例分析
生产背景
生产环境出现死锁流水通过查看死锁日志看到造成死锁的是两条一样的update语句只有where条件中的值不同如下
UPDATE test_table SET status 1 WHERE trans_id xxx1 AND status 0;
UPDATE test_table SET status 1 WHERE trans_id xxx2 AND status 0;一开始比较费解通过大量查询跟学习后分析出了死锁形成的具体原理特分享给大家希望能帮助到遇到同样问题的朋友。
死锁日志
*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET status 1 WHERE trans_id xxx1 AND status 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index idx_status of table test.test_table trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET status 1 WHERE trans_id xxx2 AND status 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index idx_status of table test.test_table trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index PRIMARY of table test.test_table trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0*** WE ROLL BACK TRANSACTION (1)
简要分析下上边的死锁日志
1、第一块内容第1行到第9行中第6行为事务(1)执行的SQL语句第7和第8行意思为事务(1)在等待 idx_status 索引上的X锁 2、第二块内容第11行到第19行中第16行为事务(2)执行的SQL语句第17和第18行意思为事务(2)持有 idx_status 索引上的X锁 3、第三块内容第21行到第23行的意思为事务(2)在等待 PRIMARY 索引上的X锁。but not gap指不是间隙锁 4、最后一句的意思即为MySQL将事务(1)进行了回滚操作。
表结构
CREATE TABLE test_table (id int(11) NOT NULL AUTO_INCREMENT,trans_id varchar(21) NOT NULL,status int(11) NOT NULL,PRIMARY KEY (id),UNIQUE KEY uniq_trans_id (trans_id) USING BTREE,KEY idx_status (status) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8
主键索引 id唯一索引 trans_id普通索引 status InnoDB引擎中有两种索引 聚簇索引 将数据存储与索引放到了一块索引结构的叶子节点保存了行数据。 辅助索引 辅助索引叶子节点存储的是主键值也就是聚簇索引的键值。 主键索引 PRIMARY 就是聚簇索引叶子节点中会保存行数据。 uniq_trans_id 索引和 idx_status 索引为辅助索引叶子节点中保存的是主键值也就是id列值。 当我们通过辅助索引查找行数据时先通过辅助索引找到主键id再通过主键索引进行二次查找也叫回表最终找到行数据。
执行计划 EXPLAN 通过看执行计划可以发现update语句用到了index merge(索引合并)也就是这条语句既用到了 uniq_trans_id 索引又用到了 idx_status 索引
Using intersect(uniq_trans_id,idx_status) 的意思是通过两个索引获取交集。
为什么会用 index_merge索引合并
MySQL5.0之前一个表一次只能使用一个索引无法同时使用多个索引分别进行条件扫描。但是从5.1开始引入了 index merge 优化技术对同一个表可以使用多个索引分别进行条件扫描。 如执行计划中的语句
UPDATE test_table SET status 1 WHERE trans_id 38 AND status 0 ;MySQL会根据 trans_id ‘38’ 这个条件利用 uniq_trans_id 索引找到叶子节点中保存的id值同时会根据 status 0 这个条件利用 idx_status 索引找到叶子节点中保存的id值然后将找到的两组id值取交集最终通过交集后的id回表也就是通过 PRIMARY 索引找到叶子节点中保存的行数据。
这里可能很多人会有疑问了uniq_trans_id 已经是一个唯一索引了通过这个索引最终只能找到最多一条数据那MySQL优化器为啥还要用两个索引取交集再回表进行查询呢这样不是多了一次 idx_status 索引查找的过程么。我们来分析一下这两种情况执行过程。 上边两种情况主要区别在于第一种是先通过一个索引把数据找到后再用其它查询条件进行过滤第二种是先通过两个索引查出的id值取交集如果取交集后还存在id值则再去回表将数据取出来。
当优化器认为第二种情况执行成本比第一种要小时就会出现索引合并。生产环境流水表中 status 0 的数据非常少这也是优化器考虑用第二种情况的原因之一。
为什么用了 index_merge就死锁了 上面简要画了一下两个update事务加锁的过程从图中可以看到在 idx_status 索引和 PRIMARY 聚簇索引 上都存在重合交叉的部分这样就为死锁造成了条件。
如当遇到以下时序时就会出现死锁 事务1等待事务2释放锁事务2等待事务1释放锁这样就造成了死锁。
MySQL检测到死锁后会自动回滚代价更低的那个事务如上边的时序图中事务1持有的锁比事务2少则MySQL就将事务1进行了回滚。
解决方案
一、从代码层面
where 查询条件中只传 trans_id 将数据查询出来后在代码层面判断 status 状态是否为0使用 force index(uniq_trans_id) 强制查询语句使用 uniq_trans_id 索引where 查询条件后边直接用 id 字段通过主键去更新。
二、从MySQL层面
删除 idx_status 索引或者建一个包含这俩列的联合索引将MySQL优化器的index merge优化关闭。 注MySQL官方已经确认了此bug77209 https://bugs.mysql.com/bug.php?id77209
