亲宝软件园·资讯

展开

update.where无索引导致MySQL死锁问题解决

Mating 人气:0

引言

随着我被拉入一个新的群聊“生产环境死锁问题排查解决”,打破了午后的悠然惬意,点开群聊秒送了一个648超级大礼包(业务不正常,死锁异常日志输出),领导怒斥并要求赶紧排除解决并总结经验,刚好我略懂略懂一点MySQL锁知识,这不得秀一下自己的实力

死锁的日志

既然死锁已经发生,也完全不要慌啊,按我说着做,一定能找到原因然后解决

触发下面这条命令获取到线索

SHOW ENGINE INNODB STATUS;

执行后你会得到一段让人看了有点迷迷的死锁日志,具体我们该怎么分析死锁,可以分成一下三步

由于死锁日志过于长,下面的日志只截取部分有用的记录

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-10-14 15:51:34 0x1a00
发生死锁的事务(一)
*** (1) TRANSACTION:
TRANSACTION 32828384, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 1128, 285 row lock(s)
MySQL thread id 26352, OS thread handle 8548, query id 987590925 WIN-6Q9NIAJLCDR 172.27.15.57 swgj updating
事务(一)导致死锁的sql
update xxx
     SET STATUS = '2',
        DESCRIPTION = 'xxx',
        MODIFY_TIME = '2022-10-14 15:51:35.707' 
     WHERE  BATCH_NO = 'xxx'
        and SFSB = '1'
事务(一)持有的锁
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 82 n bits 104 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828384 lock_mode X
事务(一)持有锁的数据记录信息 (supremum虚拟最大记录)
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
事务(一)持有锁的数据记录信息 (哪一行数据被锁了)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 30663730333038302d313164662d346439642d626338662d393439333333; asc 0f703080-11df-4d9d-bc8f-949333; (total 36 bytes);
 1: len 6; hex 0000015129e1; asc    Q) ;;
 2: len 7; hex 010000402103cd; asc    @!  ;;
 3: len 6; hex 313635303036; asc 165006;;
 .....
 .....
事务(一)等待的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 37 n bits 112 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828384 lock_mode X waiting
事务(一)等待锁的数据记录信息
Record lock, heap no 45 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 32643736613832362d343763362d343861332d613038662d343539333061; asc 2d76a826-47c6-48a3-a08f-45930a; (total 36 bytes);
 1: len 6; hex 000001f4ebdd; asc       ;;
 2: len 7; hex 820000402b3c96; asc    @+< ;;
 3: SQL NULL;
 .....
 .....
 发生死锁的事务(二)
 *** (2) TRANSACTION:
TRANSACTION 32828381, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 125 lock struct(s), heap size 24696, 137 row lock(s), undo log entries 724
MySQL thread id 26357, OS thread handle 12700, query id 987590958 WIN-6Q9NIAJLCDR 172.27.15.57 swgj update
事务(二)导致死锁的sql
insert into xxx
     ( ID, BATCH_NO, DFSJZJ,... ) 
     values ( 'xxx', 'xxx','xxx' )
事务(二)持有的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 37 n bits 112 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828381 lock_mode X locks rec but not gap
事务(二)持有锁的数据记录信息
Record lock, heap no 45 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 32643736613832362d343763362d343861332d613038662d343539333061; asc 2d76a826-47c6-48a3-a08f-45930a; (total 36 bytes);
 1: len 6; hex 000001f4ebdd; asc       ;;
 2: len 7; hex 820000402b3c96; asc    @+< ;;
 3: SQL NULL;
 ...
 ...
事务(二)等待的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 82 n bits 104 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828381 lock_mode X locks gap before rec insert intention waiting
事务(二)等待锁的数据记录信息
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 30666435313133662d393462382d346636632d383362372d303463656536; asc 0fd5113f-94b8-4f6c-83b7-04cee6; (total 36 bytes);
 1: len 6; hex 000001dbb470; asc      p;;
 2: len 7; hex 020000013619a1; asc     6  ;;
 3: len 6; hex 313636333830; asc 166380;;
 ...
 ...

分析日志

从数据库版本5.7、事务的隔离级别 REPEATABLE READ

官方文档明确指出在 REPEATABLE READ 隔离级别下,默认查询条件下是加 next-key locks (record locks + gap locks )gap locks,当查询条件使用了唯一索引时,只会对当前查询的唯一记录进行加锁,此时锁为 record locks

官方文档强势占位

MySQL 隔离级别

MySQL InnoDB 锁的类型

MySQL InnoDB 中不同 SQL 语句如何置锁

从死锁日志信息可以得出

复盘

接下来我将用一个小例子来复现这次死锁现象

CREATE TABLE `dead_lock` (
  `id` varchar(10) NOT NULL,
  `batch_no` varchar(10) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO dead_lock (id,batch_no,status) VALUES
	 ('10','1','1'),
	 ('20','20','1'),
	 ('30','30',NULL),
	 ('32','32',NULL);
begin;
insert into dead_lock values ('34', '34', null);

begin;
update dead_lock set status = '1' where batch_no = '20';

insert into dead_lock values ('11', '11', null);

总结

MySQL 事务隔离级别 REPEATABLE READ 的情况下,对于 update,delete 等操作语句,查询条件尽量使用索引,减少锁的范围,提高写的并发量,避免不必要的死锁发生影响业务正常运行

这次死锁的说明就到这里,希望大家能看得懂并有所收获,不得不说有些知识我是略过了,大家可以自行查看官方文档补充了解,更多关于update where无索引MySQL死锁的资料请关注其它相关文章!

加载全部内容

相关教程
猜你喜欢
用户评论