您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

并发插入引发的死锁问题排查

时间:2021-04-12 10:30:21  来源:  作者:科技伍小黑

一.业务背景

我们现在的业务是一款数据产品,有不少实时计算和爬取来的数据都汇总到大数据仓库、数据挖掘平台ODPS上。然后应用在读取这些数据时,这些数据会先导入到并发读能力更强,适合结构查询的MySQL上。数据端开发的同学在跑定时任务时, tps比较高,于是出现了一些线上问题:在开发过程中发现对某一包含unique key(联合的唯一索引)的表进行并发插入的时候,出现大量的死锁,使得插入几乎无法进行。于是为了排查问题,请教了DBA以及数据库事业部的同学,最后发现了问题的所在,特此记录下来

二.死锁现场

1.表结构

CREATE TABLE tkn_tb_cinema_show_data (

……

cinema_id bigint(20) DEFAULT NULL COMMENT ‘影院ID’,

show_id bigint(20) DEFAULT NULL COMMENT ‘影片ID’,

now_date varchar(32) DEFAULT NULL COMMENT ‘当日时间’,

……

PRIMARY KEY (id),

UNIQUE KEY uid_cinema_show_date (cinema_id,show_id,now_date),

……

) ENGINE=InnoDB AUTO_INCREMENT=2162973490 DEFAULT CHARSET=utf8 COMMENT=’淘宝电影订单影院影片数据统计’

2.问题状况

可以看到出现死锁的原因是因为批量插入的时候,该事务

持有锁

index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X

等待锁

index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X locks gap before rec insert intention waiting

这样一看确实奇怪,怎么批量插入不同行怎么会有死锁,再看看死锁日志

(SHOW ENGINE INNODB STATUS;)

transactions deadlock detected, dumping detailed information.

2017-06-11 08:41:03 2ac742684700

*** (1) TRANSACTION:

TRANSACTION 73278630816, ACTIVE 1 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 622 lock struct(s), heap size 79400, 743 row lock(s), undo log entries 388

MySQL thread id 13824253, OS thread handle 0x2ac195786700, query id 53621728233 11.227.64.76 dwexp update

INSERT INTO tkn_tb_cinema_show_data ......

*** (1) HOLDS THE LOCK(S):

RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X

Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 8; hex 80000000000035cb; asc 5 ;;

1: len 8; hex 80000000000356f1; asc V ;;

2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;

3: len 8; hex 80000000807f52b2; asc R ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 8; hex 8000000000006262; asc bb;;

1: len 8; hex 8000000000035911; asc Y ;;

2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;

3: len 8; hex 80000000807fdae4; asc ;;

*** (2) TRANSACTION:

TRANSACTION 73278630826, ACTIVE 1 sec inserting, thread declared inside InnoDB 4836

mysql tables in use 1, locked 1

2425 lock struct(s), heap size 292392, 3363 row lock(s), undo log entries 1804

MySQL thread id 13824252, OS thread handle 0x2ac742684700, query id 53621728249 11.227.64.76 dwexp update

INSERT INTO tkn_tb_cinema_show_data ......

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X

Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 8; hex 8000000000006262; asc bb;;

1: len 8; hex 8000000000035911; asc Y ;;

2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;

3: len 8; hex 80000000807fdae4; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 8; hex 80000000000035cb; asc 5 ;;

1: len 8; hex 80000000000356f1; asc V ;;

2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;

3: len 8; hex 80000000807f52b2; asc R ;;

*** WE ROLL BACK TRANSACTION (1)

 

三.分析问题

1.阅读死锁日志

1. 从日志中可以看到两个事务的持锁情况和等待锁情况:

a. 事务一

HOLDS THE LOCK(S) …… lock_mode X 持有X锁

WAITING FOR THIS LOCK TO BE GRANTED …… X locks gap before rec insert intention waiting 等待insert intention lock

b.事务二

HOLDS THE LOCK(S) …… lock_mode X 持有X锁

WAITING FOR THIS LOCK TO BE GRANTED …… X locks gap before rec insert intention waiting 等待insert intention lock

2. 补充关于一些锁方面的知识

当InnoDB在判断行锁是否冲突的时候,除了最基本的IS IX S X锁的冲突判断意外,还有一套更精确的判断逻辑。除了上面说到的锁类型,InnoDB还将锁细分为如下几种子类型:

record lock(RK)

锁直接加在索引记录上面,锁住的是key

gap lock(GK)

间隙锁,锁定一个范围,但不包括记录本身。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况

next key lock(NK)

行锁和间隙锁组合起来就叫Next-Key Lock

insert intention lock(IK)

如果插入前,该间隙已经由gap锁,那么Insert会申请插入意向锁。因为了避免幻读,当其他事务持有该间隙的间隔锁,插入意向锁就会被阻塞(不用直接用gap锁,是因为gap锁不互斥)。

下面画的就是“精确模式”锁兼容矩阵

列相加行已有RKGKIKNK

RK0110

GK1111

IK1010

NK0110

insert中对唯一索引的加锁逻辑

先做UK冲突检测,如果存在目标行,先对目标行加S NK(S lock中的next key lock,下同),这个锁如果最终插入成功(该记录在等待期间被其他事务删除,此锁被同时删除)

如果1成功,对对应行加X IK

如果2成功,插入记录,并对记录加X RK(有可能是隐式锁)

3.锁的细节

1. 前文已分析,一个insert SQL需要加的锁依次为 S NK, X IK, X RK、那么加XIK前需要GK或NK。而insert不需要加GK,因此两个事务X IK被申请等待的原因是在申请S NK的过程受到阻塞了。

2. insert完成之后,只会残留X RK锁,这就是两个事务都有X RK的原因,说明它们刚插入完某几条记录。

3. 由1,2可以推测,死锁是事务1 的S NK被事务2的 X RK所阻塞,说明事务2插入的记录在事务1 S NK的范围内。而事务2的 S NK被 事务1 阻塞的申请S NK给阻塞,说明事务1 S NK的范围要大于事务2 S NK的范围。

4. 由第3点推断,可以证明出事务2所有的记录范围 REC2 是要在 事务1所有的记录范围 REC1之后的,既REC2 < REC1

而插入的业务场景的数据是:

事务1

('10076','150686','2017-06-11 08:39:15.866') ,

('10111','150686','2017-06-11 08:39:15.866') ,

('10133','214563','2017-06-11 08:39:15.866') ,

('10171','150686','2017-06-11 08:39:15.866')

事务2

('15186','150686','2017-06-11 08:39:15.866') ,

('15186','151509','2017-06-11 08:39:15.866') ,

('15186','207522','2017-06-11 08:39:15.866') ,

('15187','151509','2017-06-11 08:39:15.866')

 

实际的插入数据符合我们的预期

5.由上面的结论,我们可以得到一张死锁循环图

四.预防死锁

死锁发生的条件:

1、资源不能共享,需要只能由一个进程或者线程使用

2、请求且保持,已经锁定的资源自给保持着不释放

3、不剥夺,自给申请到的资源不能被别人剥夺

4、循环等待

防止死锁的途径就是避免满足死锁条件的情况发生,适合这个问题解决的方案有:

1、保持事务简短并在一个批处理中

在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。保持事务在一个批处理中,可以最小化事务的网络通信往返量,减少完成事务可能的延迟并释放锁。

2、使用低隔离级别

确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取(未修改)的数据,而不必等待第一个事务完成。使用较低的隔离级别(例如提交读)而不使用较高的隔离级别(例如可串行读)可以缩短持有共享锁的时间,从而降低了锁定争夺(比如这次的S NK和X IK 是InnoDB引擎Repeatable Read级别才有的)。



Tags:死锁   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
前言最近老顾经常碰到同事说,mysql又死锁了导致业务报错。今天我们就来聊聊死锁以及怎么解决锁类型mysql锁级别:页级、表级、行级表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发...【详细内容】
2021-09-06  Tags: 死锁  点击:(84)  评论:(0)  加入收藏
生产上偶现这段代码会出现死锁,死锁日志如下。*** (1) TRANSACTION:TRANSACTION 424487272, ACTIVE 0 sec fetching rowsmysql tables in use 3, locked 3LOCK WAIT 6 lock s...【详细内容】
2021-07-29  Tags: 死锁  点击:(65)  评论:(0)  加入收藏
一、前言最近参加了几轮面试,发现很多5-7年工作经验的候选人在性能优化这一块,基本上只能说出传统的分析方式,例如ANR分析,是通过查看/data/anr/ 下的log,分析主线程堆栈、cpu、...【详细内容】
2021-06-17  Tags: 死锁  点击:(149)  评论:(0)  加入收藏
一.业务背景我们现在的业务是一款数据产品,有不少实时计算和爬取来的数据都汇总到大数据仓库、数据挖掘平台ODPS上。然后应用在读取这些数据时,这些数据会先导入到并发读能力...【详细内容】
2021-04-12  Tags: 死锁  点击:(209)  评论:(0)  加入收藏
最近遇到一个由于唯一性索引,导致并发插入产生死锁的场景,在分析死锁产生的原因时,发现这一块还挺有意思的,涉及到MySql中不少的知识点,特此总结记录一下。 一、MySql常见的锁谈...【详细内容】
2021-02-19  Tags: 死锁  点击:(168)  评论:(0)  加入收藏
01. Mysql 事务死锁现象及原因初步判断做IT的几乎每天都接触 MySql,但是 Mysql 事务死锁却并不常见,前段时间就让我遇到了。异常日志如下 从日志看是发生了 Lock wait timeout...【详细内容】
2021-01-27  Tags: 死锁  点击:(205)  评论:(0)  加入收藏
在并发环境中,我们为了保证共享可变数据的线程安全性,需要使用加锁机制,如果锁使用不当可能会引起死锁,线程饥饿等问题。在Java应用程序中如果发生死锁,程序是无法自动恢复的,严重...【详细内容】
2020-12-25  Tags: 死锁  点击:(131)  评论:(0)  加入收藏
导读本文介绍Java诸多优化实例:第一,排查堆上、堆外内存泄露;第二,使用arthas、jaeger、tcpdump、jstack做性能优化;第三,排查进程异常退出的原因,如被杀、System.exit、Java调用的...【详细内容】
2020-11-25  Tags: 死锁  点击:(78)  评论:(0)  加入收藏
死锁 思维导图是什么线程死锁是指由于两个或者多个线程互相持有对方所需要的资源,导致这些线程处于等待状态,无法前往执行。当线程进入对象的synchronized代码块时,便占有了资...【详细内容】
2020-10-28  Tags: 死锁  点击:(124)  评论:(0)  加入收藏
死锁死锁的原理非常简单,用一句话就可以描述完。就是当多线程访问多个锁的时候,不同的锁被不同的线程持有,它们都在等待其他线程释放出锁来,于是便陷入了永久等待。比如A线程持...【详细内容】
2020-08-25  Tags: 死锁  点击:(55)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条