一次死锁排查过程

一次死锁排查过程 一次死锁排查过程

最近有个脚本报警，事务产生死锁。

查询InnoDB的死锁日志:

SHOW ENGINE INNODB STATUS;

返回的有关死锁的日志(已脱敏)

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-17 11:59:23 0x7f034c90c700
*** (1) TRANSACTION:
TRANSACTION 307242180, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 47 lock struct(s), heap size 8400, 4 row lock(s)
MySQL thread id 22014272, OS thread handle 139653892638464, query id 1086559135 10.26.15.194 root updating
UPDATE dead_lock SET `field1`='A' WHERe `index_field1` = '26021775' AND `index_field2` = '2022-01-02'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29244 page no 140 n bits 240 index PRIMARY of table `ke_onse_test`.`user_kaoqi_dead_lock` trx id 307242180 lock_mode X locks rec but not gap waiting
Record lock, heap no 36 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 4; hex 80001d32; asc    2;;
 1: len 6; hex 00001250074f; asc    P O;;
 2: len 7; hex e2000000340110; asc     4  ;;
 3: len 8; hex 3233303935303930; asc 23095090;;
 4: len 9; hex e9bb84e9a296e6b481; asc          ;;
 5: len 3; hex 8fcc22; asc   ";;
 6: len 4; hex 80000002; asc     ;;
 7: len 9; hex e585ace4bc91e697a5; asc          ;;
 8: len 2; hex 5b5d; asc [];;
 9: len 2; hex 5b5d; asc [];;
 10: len 2; hex 5b5d; asc [];;
 11: len 4; hex 80000000; asc     ;;
 12: len 4; hex 80000001; asc     ;;
 13: len 0; hex ; asc ;;
 14: len 0; hex ; asc ;;
 15: len 5; hex 99abe2be47; asc     G;;
 16: len 5; hex 99abe2be47; asc     G;;

*** (2) TRANSACTION:
TRANSACTION 307242183, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4754
mysql tables in use 3, locked 3
47 lock struct(s), heap size 8400, 3 row lock(s)
MySQL thread id 22014295, OS thread handle 139652146185984, query id 1086559137 10.26.15.194 root updating
UPDATE dead_lock SET `field1`='B' WHERe `index_field1` = '23095090' AND `index_field2` = '2022-01-02'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 29244 page no 140 n bits 240 index PRIMARY of table `ke_onse_test`.`user_kaoqi_dead_lock` trx id 307242183 lock_mode X locks rec but not gap
Record lock, heap no 36 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 4; hex 80001d32; asc    2;;
 1: len 6; hex 00001250074f; asc    P O;;
 2: len 7; hex e2000000340110; asc     4  ;;
 3: len 8; hex 3233303935303930; asc 23095090;;
 4: len 9; hex e9bb84e9a296e6b481; asc          ;;
 5: len 3; hex 8fcc22; asc   ";;
 6: len 4; hex 80000002; asc     ;;
 7: len 9; hex e585ace4bc91e697a5; asc          ;;
 8: len 2; hex 5b5d; asc [];;
 9: len 2; hex 5b5d; asc [];;
 10: len 2; hex 5b5d; asc [];;
 11: len 4; hex 80000000; asc     ;;
 12: len 4; hex 80000001; asc     ;;
 13: len 0; hex ; asc ;;
 14: len 0; hex ; asc ;;
 15: len 5; hex 99abe2be47; asc     G;;
 16: len 5; hex 99abe2be47; asc     G;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29244 page no 29 n bits 1368 index idx_index_field2 of table `baii_test`.`dead_lock` trx id 307242183 lock_mode X locks rec but not gap waiting
Record lock, heap no 656 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8fcc22; asc   ";;
 1: len 4; hex 80001d32; asc    2;;

*** WE ROLL BACK TRANSACTION (2)

表结构大概如下:

CREATE TABLE `dead_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `index_field1` int(20) NOT NULL,
  `index_field2` int(20) NOT NULL,
  `field1` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_index_field1` (`index_field1`),
  KEY `idx_index_field2` (`index_field2`),
  KEY `idx_day_desc` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

提取有关死锁的两个事务

TRANSACTION(307242180) 下面简称事务1

UPDATE dead_lock SET `field1`='A' WHERe `index_field1` = '26021775' AND `index_field2` = '2022-01-02'

TRANSACTION(307242183) 下面简称事务2

UPDATE dead_lock SET `field1`='B' WHERe `index_field1` = '23095090' AND `index_field2` = '2022-01-02'

查看两个sql的 EXPLAIN, 发现均使用了 merge index 优化技术, 正常的sql 只能使用一个索引，但使用 merge index 技术时会使用多个索引交替查询, 也就是说在查询时需要对两个索引进行加锁，而加锁过程不是原子性的，导致在并发事务过多的情况下，极容易发生死锁。 解析事务日志得到事务时序如下: TRANSACTION(307242180) 事务1TRANSACTION(307242183) 事务2sql START TRANSACTION;对索引：index_field1=‘26021775’ 加X锁(next-key锁)sql START TRANSACTION;回表: 主键索引 index_field1=‘26021775’ 对应的主键 加X锁(行锁)对索引：index_field1=‘23095090’ 加X锁回表: 主键索引 index_field1=‘23095090’ 对应的主键 加X锁(行锁)对索引：index_field2=‘2022-01-02’ 加X锁(next-key锁)回表: 主键索引 index_field2=‘2022-01-02’ 对应的主键 加X锁(行锁), 发现 id=6790164 的行已被事务2持有 开始等待对索引：index_field2=‘2022-01-02’ 加X锁(next-key锁), 发现 index_field2='2022-01-02' 的行已被 事务1持有, 此时死锁发生采用就近原则, 回滚此事务, id=6790164 的行锁解除sql COMMIT;

在隔离级别为RR的情况下，普通索引加锁为 next-key锁主键是唯一索引，所以给主键加的锁退化为 行锁 分析完日志后一些问题的解答

merge index 技术什么情况下才会使用?

不可以转化成 range scan 的条件where 条件有多列都建有索引merge index 优化 已启用Intersect和Union要符合 ROR，即 Rowid-Ordered-Retrival

merge index 技术是啥东西

官方的 http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html民间的 https://www.cnblogs.com/digdeep/p/4975977.html

如何避免这种死锁问题

先查出主键 使用 主键 进行更新创建联合索引(科学的建立索引) 有联合索引后就不走 merge index 了调整mysql 配置关闭 merge index 优化更新事使用单个索引列进行更新，既能防止全表锁又能防止这种死锁问题

next-key锁 的范围

左开右闭区间

5 MySQL 优化术语必知必会让你愉快的和其他程序员交(zhuang)流(bei)

https://www.modb.pro/db/182912 参考文献

MySQL 优化之 index merge(索引合并)记录一次 mysql 死锁 (患者 为 index merge)mysql insert锁机制(insert死锁)mysql使用了index merge（索引合并）造成死锁分析总结multi-column-indexes-vs-index-merge