一次死锁排查过程

最新推荐文章于 2024-09-14 19:00:09 发布

baidu_36146918

最新推荐文章于 2024-09-14 19:00:09 发布

阅读量1k

点赞数

文章标签： sql mysql

本文链接：https://blog.csdn.net/baidu_36146918/article/details/122560910

版权

一次死锁排查过程

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

查询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) 事务1	TRANSACTION(307242183) 事务2
`sql 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;`