菜鸟笔记
提升您的技术认知

记录一次mysql死锁

一,死锁发现

项目中有一个接口包含更新操作1,后面发现更新失败,通过查看应用程序日志,发现发生了死锁

sql 1 如下

1.最初版本根据id为条件,更新(plan_start_time 二级索引)

update tt_task  SET org_id = ?,   org_name = ?,    plan_start_time = ?          where id = ?

2.第二版根据order_number唯一索引为条件,更新(这样改当时想法是减少对id的争夺,后面发布后未生效,似乎导致了更严重的死锁(更新1后面还有一个更新2操作,从应用日志中发现更新2的死锁))

update tt_task  SET org_id = ?,   org_name = ?,    plan_start_time = ?          where order_number = ?

二,死锁日志

1.最初根据id更新死锁,更新1

 2.改成根据唯一索引order_number更新

 3.更新2死锁(更新1后面的一个更新操作)(plan_arrive_time 二级索引)

update tt_task          SET navigation_distance = ?,    plan_arrive_time = ?          where id = ?

 4.死锁日志分析

上面上传的死锁日志是不全的,出现死锁日志有的不是一处错误,有的是连续两个,甚至是三个死锁报错

4.1 连续两个报错日志,且是不同的两条数据,发生报错时间s秒级

4.2 只有一个报错日志,某一条数据发生死锁

4.3 连续出现3处死锁日志,如上图3,1.3处是更新2的死锁,2处是更新1的死锁,且分别是3条不同的数据

三,猜想

一般死锁原因:并发+两个事物+操作同一条数据(具体原因:待补充)

比较疑惑的是

1.更新操作,条件已经是id,最大限度的减少了死锁,为什么还会发生

2.只有一条死锁报错,只涉及到一条数据,怎么产生死锁的(不会并发啊)

3.连续两个死锁日志,是两个不同的数据(不是争夺同一个数据引发死锁吗)

猜想1:

肯定有并发操作

针对发生死锁的这个表,项目中有很多更新操作(确实有,哪些比较模糊)

当前发生死锁的更新,肯定是和哪个更新操作发生了冲突,两个不同事物各自持有锁

方案

1,怎么优化当前发生死锁的sql,但是已经是根据id更新的,死锁概率应该比较低啊,方向是这里,所以有了改成根据唯一索引更新,想减少对主键id的占用。

具体操作

1.1 把代码逻辑中更新报错的sql,单独写一个根据唯一索引更新,独立与其他更新操作

1.2 把更新操作上的,@Transation 事物注解去掉,如下图 

结果:但是上线后,未生效

四,继续排查

因为第一次改动未生效,发下可以查询mysql日志,如下

1.查询mysql死锁日志

SHOW ENGINE INNODB STATUS;

2.mysql日志详情

更新2的死锁日志

*** (1) TRANSACTION:
TRANSACTION 1090268215, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1951581, OS thread handle 140039768516352, query id 6856707976 10.129.22.123 service-2eoui9 updating
update tt_task
         SET navigation_distance = '28115',
            
            
            
                plan_arrive_time = '2022-06-25 17:11:03.08' 
        where id = 528013
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1233 page no 40677 n bits 96 index PRIMARY of table `yl-task-jghc`.`tt_task` trx id 1090268215 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 111; compact format; info bits 0

之前未发现的死锁日志 (grabbing_status 二级索引),当作更新3

update tt_task
        set grabbing_status = '0'
        where grabbing_status = '79501010'
          and relation_parts_count > 0

*** (2) TRANSACTION:
TRANSACTION 1090267856, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
30328 lock struct(s), heap size 2711760, 557309 row lock(s), undo log entries 35
MySQL thread id 1951474, OS thread handle 140039766386432, query id 6856706017 10.129.22.123 service-2eoui9 updating
update tt_task
        set grabbing_status = '0'
        where grabbing_status = '79501010'
          and relation_parts_count > 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1233 page no 40677 n bits 88 index PRIMARY of table `yl-task-

3. 再结合业务日志发现以下问题

3.1.实际上更新2(包含在更新1中,且是异步的)的死锁概率大于更新1

3.2.更新1,更新2,这两个受害者,都与自己无关,也与对方无关,还有一个第三者,是第三者导致了更新1,更新2,甚至更多地方的死锁

至此,发现了导致死锁的根本sql

4.可归类为

更新1(死锁受害者)

更新2(死锁受害者)

更新3(死锁引发者)

五,死锁知识

归纳如下

【1】基本概念
1.通过聚簇索引更新时,会在聚簇索引上加锁。
2.通过二级索引进行更新时,会先对二级索引加锁,然后对聚簇索引加锁。
3.使用聚簇索引更新二级索引时,会先对聚簇加锁,再对二级索引加锁。此结论的前提条件为结论4
4.更新二级索引时,只有二级索引所在的列产生实际变化的更新,才会对二级索引加锁,否则仅会对聚簇索引加锁。

【2】锁知识

1.mysql的事务支持与存储引擎有关,MyISAM不支持事务,INNODB支持事务,更新时采用的是行级锁

2.行级锁必须建立在索引的基础

3.行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。

4.在并发高的应用中,批量更新一定要带上记录的主键,优先获取主键上锁,这样开业减少死锁的发生

5.在采用INNODB的MySQL中,更新操作默认会加行级锁,行级锁是基于索引的,在分析死锁之前需要查询一下mysql的执行计划,看看是否用到了索引,用到了哪个索引,对于没有用索引的操作会采用表级锁。如果操作用到了主键索引会先在主键索引上加锁,然后在其他索引上加锁,否则加锁顺序相反。在并发度高的应用中,批量更新一定要带上记录的主键,优先获取主键上的锁,这样可以减少死锁的发生。

6. innodb 读不加锁。但是写加了锁,在什么时候加锁呢? 在我们执行一条 update 语句的时候。 在什么时候释放锁呢? 在事务提交的时候。

上面说过 锁会在事务提交的时候释放,所以 两个事务就锁死了。

7.where条件 与表锁/行锁

在 update 语句的 where 条件没有使用索引,就会全表扫描;

where条件里面,不加索引时,update会使用“表锁”进行更新,影响所有行的查询更新;

加了索引后,使用“行锁”进行udpate,只锁当前行。不影响其他行的查询更新。

mysql的行锁是通过索引加载的,即是行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描

【3】死锁必要条件

1.产生死锁的必要条件 多个并发事务(2个或者以上) 每个事物都持有了锁(或者是已经在等待锁) 每个事务都需要再继续持有锁(为了完成事务逻辑,还必须更新更多的行) 事物之间产生加锁的循环等待,形成死锁

2.产生死锁的必要条件
多个并发事务(2个或者以上)
每个事物都持有了锁(或者是已经在等待锁)
每个事务都需要再继续持有锁(为了完成事务逻辑,还必须更新更多的行)
事物之间产生加锁的循环等待,形成死锁

【4】加锁流程

4.1

对同一条数据,T1通过二级索引更新,T2通过聚簇索引更新T1作为更新条件二级索引,如果T1先拿到二级索引,T2先拿到聚簇索引。此时就会出现T1等T2的聚簇索引,T2等T1的二级索引的死锁
4.2

事物1: 根据二级索引作为条件,更新二级索引;先锁二级索引(相关记录),再锁主键索引(相关记录)

事物2: 根据主键更新数据,更新的字段中包含;先锁主键索引(相关记录),再锁二级索引(相关记录)

形成了(针对单个的一条记录(很多这样记录))

事物1锁定二级索引记录,持有二级索引锁,待获取主键索引锁

事物2锁定主键索引记录,持有主键索引锁,待获取二级索引锁

六,死锁原因,深度分析

七,方案

针对更新3,引发死锁的sql

更新条件为二级索引的sql进行优化,改为先查询id,再根据id更新

mysql死锁的例子_MySQL数据库的一次死锁实例分析_C18298182575的博客-CSDN博客

MySQL造成更新死锁及插入死锁的几种常见原因_yue_hu的博客-CSDN博客_mysql update 死锁

今日头条

mysql死锁的例子_MySQL数据库的一次死锁实例分析_weixin_39947314的博客-CSDN博客

MySQL InnoDB update锁表问题Record Locks_&夜半钟声到客船&的博客-CSDN博客