专栏首页Throwable's Blog一次MySQL死锁问题的排查与分析(一)

一次MySQL死锁问题的排查与分析(一)

前提

笔者负责的一个系统最近有新功能上线后突然在预警模块不定时报出MySQL死锁导致事务回滚。幸亏,上游系统采用了异步推送和同步查询结合的方式,感知到推送失败及时进行了补偿。于是,笔者争取了一点时间详细分析了导致死锁的多个事务的执行时序,分析并且得出解决方案。

死锁场景复现

首先,MySQL的服务端版本是5.7(小版本可以基本忽略),使用了InnoDB。有一张用户数据表的schema设计如下(无关字段已经屏蔽掉):

CREATE TABLE `t_user_data`
(
    id      BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    data_id VARCHAR(50)     NOT NULL COMMENT '数据ID',
    INDEX idx_user_id (user_id),
    INDEX idx_data_id (data_id)
) COMMENT '用户数据表';

业务代码中发生死锁的伪代码如下:

process_method(dataId,userDataDtoList){
    start transaction:
    userDataDao.deleteByDataId(dataId);
    for dto in userDataDtoList:
        UserData userData = convert(dto);
        userDataDao.insert(dto);
    commit;
}

这里的逻辑是,如果已经存在对应dataId的数据要先进行删除,然后写入新的用户数据。

尝试用两个Session提交两个事务重现死锁问题:

时间序列

Tx-Session-1

Tx-Session-2

T1

START TRANSACTION;

T2

START TRANSACTION;

T3

DELETE FROM t_user_data WHERE data_id = ‘xxxxx’;

T4

DELETE FROM t_user_data WHERE data_id = ‘yyyyy’;

T5

INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (1, ‘xxxxx’);

T6

INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (2, ‘yyyyy’);

T7

Deadlock found when trying to get lock; try restarting transaction(Rollback)

T8

COMMIT;

这里会出现两个现象:

  1. Tx-Session-2会话T4执行完毕之后,Tx-Session-1会话T5执行的时候,Tx-Session-1会话客户端会处于阻塞状态。
  2. Tx-Session-2会话T6执行完毕之后,MySQL提示死锁事务被回滚,此时,Tx-Session-1会话客户端会解除阻塞。

导致死锁的原因

后面会写一篇专门的文章学习和理解MySQL的InnoDB数据引擎的锁相关知识,这里直接排查InnoDB的死锁日志。

mysql> show engine innodb status;

输出的死锁日志如下:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-11 19:16:04 0x5804
*** (1) TRANSACTION:
TRANSACTION 3882, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 32, OS thread handle 9876, query id 358 localhost ::1 doge update
INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (1, 'xxxxx')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 6 n bits 72 index idx_data_id of table `test`.`t_user_data` trx id 3882 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 3883, ACTIVE 9 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 22532, query id 359 localhost ::1 doge update
INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (2, 'yyyyy')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 33 page no 6 n bits 72 index idx_data_id of table `test`.`t_user_data` trx id 3883 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 6 n bits 72 index idx_data_id of table `test`.`t_user_data` trx id 3883 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

这里要参考MySQL关于InnoDB锁的关于next-key锁描述那一节,注意死锁日志关键字supremum的意义:

next-key锁将gap锁定在索引中最大值之上,而supremum伪记录的值高于索引中实际的任何值。supremum不是真正的索引记录,因此,实际上,此next-key锁仅锁定最大索引值之后的间隙。

两个事务的锁属性可以通过select * from information_schema.innodb_locks;进行查询,数据如下表:

lock_id

lock_tx_id

lock_mode

lock_type

lock_table

lock_index

lock_space

lock_page

lock_rec

lock_data

3882:33:6:1

3882

X

RECORD

test.t_user_data

idx_data_id

33

6

1

supremum pseudo-record

3883:33:6:1

3883

X

RECORD

test.t_user_data

idx_data_id

33

6

1

supremum pseudo-record

DELETE FROM t_user_data WHERE data_id = '不存在的索引值';

上面的SQL执行时候,如果条件刚好是索引列,并且查询的值是当前表(索引)中不存在的数据,根据next-key锁的描述和死锁日志中的asc supremum关键字,执行该DELETE语句的时候,会锁定目标值和高于目标值的任何值,如果条件是"xxxxx",那么相当于锁定区间为(“xxxxx”,最大上界]。

next-key锁是索引记录上的记录锁(Record Lock)和索引记录之前的间隙上的间隙锁(Gap Lock)定的组合。间隙锁有两个特点:

  1. 两个事务即使锁定的区间一致(或者有部分重合),不会影响它们之间获取到锁(可以参考行锁的兼容性矩阵)。
  2. 间隙锁G会阻止非持有G的其他事务向锁定的区间中插入数据,以避免产生冲突数据。

分析到这里,就很好解释上面出现死锁的执行时序:

  1. 两个事务的DELETE语句都可以正确执行,这个时候,两者的间隙锁锁定的区域分别是(‘xxxxx’,最大上界]和(‘yyyyy’,最大上界]。
  2. 事务1执行INSERT语句的时候阻塞,是因为事务2的间隙锁不允许事务1插入索引值’xxxxx’。
  3. 事务2执行INSERT语句的时候阻塞,是因为事务1的间隙锁不允许事务1插入索引值’yyyyy’,执行到这一步,MySQL的死锁检查模块应该起效了,因为两个事务依赖的锁资源已经成环(或者成有向图)。
  4. 事务2的优先级比较低,于是抛出死锁异常并且被回滚了。

之前曾经和DBA同事聊过,发生死锁的事务是怎么衡量优先级或者怎么确定哪个事务需要回滚(释放锁资源让另一个事务可以正常提交),但是后来没有收到很好的答复,这一点有时间再研究一下。

解决方案

参考MySQL的文档,解决方案有两个:

  1. 方案一:降低数据库的事务隔离级别,需要降低到READ COMMITED,这样子可以关闭间隙锁的扫描。(<== 并不推荐这种做法,修改事务隔离级别有可能出现新的问题)
  2. 方案二:针对对应的原因修改业务代码。

这里方案二只需要把伪代码逻辑修改如下:

process_method(dataId,userDataDtoList){
    List<UserData> userDataList = userDataDao.selectByDataId(dataId);
    start transaction:
    if userDataList is not empty: 
       List<Long> ids = collectIdList(userDataList);
       userDataDao.deleteByIds(ids);       
    for dto in userDataDtoList:
        UserData userData = convert(dto);
        userDataDao.insert(dto);
    commit;
}

就是先根据dataId进行查询,如果存在数据,聚合主键列表,通过主键列表进行删除,然后再进行数据插入。

小结

这并非是第一次在生产环境中出现MySQL死锁,只是这次的案例相对简单。InnoDB提供的死锁日志其实并没有提供完整的事务提交的SQL,所以对于复杂的场景需要细致结合代码和死锁日志进行排查,很多时候对应的代码逻辑是多处的。这里列举一下笔者处理死锁问题的一些步骤:

  1. 及时止损,如果可以回滚导致死锁的代码,那么最好果敢地回滚;如果重试可以解决问题并且出现死锁问题的规模不大,可以尝试短时间内进行问题排查。
  2. 通过业务系统日志迅速定位到发生死锁的代码块,JVM应用一般底层是依赖JDBC,出现死锁的时候会抛出一个SQLException的子类,异常栈的信息中带有"Deadlock"字样。
  3. 分析InnoDB的死锁日志,一般会列出竞争锁的多个事务的相对详细的信息,这些信息是排查死锁问题的第一手资料。
  4. 修复问题上线后注意做好监控和预警,确定问题彻底解决。

参考资料:

(本文完 c-1-d e-a-20190511)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • RabbitMQ扩展之消费者取消通知

    当一个信道上建立的消费者订阅了一个队列,有可能出现各种原因导致消费停止。一个很明显的原因就是客户端在同一个信道上发出basic.cancel命令,消息中间件代理...

    Throwable
  • Mybatis代码生成器Mybatis-Generator使用详解

    最近在做创业项目的时候因为有比较多的新需求,需要频繁基于DDL生成Mybatis适合的实体、Mapper接口和映射文件。其中,代码生成器是MyBatis Gen...

    Throwable
  • 深入理解Object提供的阻塞和唤醒API

    前段时间花了大量时间去研读JUC中同步器AbstractQueuedSynchronizer的源码实现,再结合很久之前看过的一篇关于Object提供的等待和唤醒...

    Throwable
  • Yii2.0框架模型添加/修改/删除数据操作示例

    本文实例讲述了Yii2.0框架模型添加/修改/删除数据操作。分享给大家供大家参考,具体如下:

    砸漏
  • python3 爬虫第二步Selenium 使用简单的方式抓取复杂的页面信息

    网站复杂度增加,爬虫编写的方式也会随着增加。使用Selenium 可以通过简单的方式抓取复杂的网站页面,得到想要的信息。

    公众号 碧油鸡
  • mybatis使用 原始dao开发 (自己写dao实现类)

    用户5927264
  • maven 创建java 项目

    斯文的程序
  • apple ID可绕过安全提示问题直接修改密码漏洞

    利用前提:知道受害者的apple id及其注册时的出生日期 利用步骤: 第一步:登录https://iforgot.apple.com/iForgot/iFor...

    FB客服
  • 使用maven的一个最简单的例子

    版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。

    Jerry Wang
  • SSM 单体框架 - 教育平台后台管理系统:广告和用户模块开发

    后台管理系统的广告管理模块包含了广告位列表查询、添加&修改广告位、回显广告位名称、广告分页查询、图片上传接口、新建&修改广告、回显广告信息、广告状态上下线等接口...

    RendaZhang

扫码关注云+社区

领取腾讯云代金券