进阶 | MySQL 死锁案例解析一则

2023年 12月 26日 71.0k 0

这里一共四把锁,加锁步骤如下:

1、在非唯一索引(name)上找到(ddd,29)的索引项,加上X锁;

2、根据(ddd,29)找到主键索引的(29,ddd)记录,加X锁;

3、在非唯一索引(name)上找到(ddd,37)的索引项,加上X锁;

4、根据(ddd,29)找到主键索引的(37,ddd)记录,加X锁;

从上面步骤可以看出,InnoDB对于每个符合条件的记录是分步加锁的,即先加二级索引再加主键索引;其次是按记录逐条加锁的,即加完一条记录后,再加另外一条记录,直到所有符合条件的记录都加完锁。那么锁什么时候释放呢?答案是事务结束时会释放所有的锁。

小结:MySQL 加锁和索引类型有关,加锁是按记录逐条加,另外加锁也和隔离级别有关。
四、疑问点排查及分析思路
1、发生死锁的表结构及索引情况(隐去了部分无关字段和索引):
如下:

    CREATE TABLE `A` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `create_date` datetime NOT NULL , 
      `modified_date` datetime NOT NULL ,  
      `pay_name` varchar(256) NOT NULL ,  
      `pay_version` varchar(256) DEFAULT NULL , 
    `identifier` varchar(256) NOT NULL ,
      `seller_id` varchar(64) NOT NULL , 
    `state` varchar(64) DEFAULT NULL ,
      `fund_transfer_ order_ no` varchar(256) DEFAULT NULL,  
    PRIMARY KEY (`id`),UNIQUE KEY `uk_scene_identifier`
    (KEY `idx_seller` (`seller_id`),
    KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; 

    该表共有三个索引,1个主键索引,2个普通索引。
    2、分析死锁日志
    发生死锁,第一时间查看死锁日志,内容如下:

      Transactions deadlock detected, dumping detailed information.
      2021-05-19T21:44:23.516263+08:00 5877341 [Note] InnoDB:  
      *** (1) TRANSACTION:
      TRANSACTION 173268495, ACTIVE 0 sec fetching rows
      mysql tables in use 1, locked 1
      LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1
      MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating
      update 死锁语句
      2021-05-19T21:44:23.516321+08:00 5877341 [Note] InnoDB:  
      *** (1) HOLDS THE LOCK(S):
      RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap
      Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
      2021-05-19T21:44:23.516565+08:00 5877341 [Note] InnoDB:  
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waiting
      Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
      2021-05-19T21:44:23.517793+08:00 5877341 [Note] InnoDB:  
      *** (2) TRANSACTION:
      TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81
      mysql tables in use 1, locked 1
      302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1
      MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating
      update 死锁语句

      分析下死锁日志,可以得到以下信息:

      1. 导致死锁的两条SQL语句。
      2. 事务1,持有索引idx_seller_transNo的锁,在等待获取PRIMARY的锁。
      3. 事务2,持有PRIMARY的锁,在等待获取idx_seller_transNo的锁。
      4. 因事务1和事务2之间发生循环等待,故发生死锁。
      5. 事务1和事务2当前持有的锁均为:lock_mode X locks rec but not gap

      两个事务对记录加的都是X 锁,No Gap锁,即对当行记录加锁(Record Lock),并未加间隙锁。
      3、常见锁类型
      X锁:排他锁、又称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
      与之对应的是S锁:共享锁,又称读锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。
      Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。
      Next-Key Lock:1+2,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。
      根据目前掌握的信息,可以做一些简单的推断。
      首先,此次死锁一定是和Gap锁以及Next-Key Lock没有关系的。因为数据库隔离级别是RC(READ-COMMITED)的,这种隔离级别是不会添加Gap锁的。前面的死锁日志也提到这一点。
      然后,就要翻代码了,看看代码中事务到底是怎么做的。核心代码及SQL如下:

        @Transactional(rollbackFor = Exception.class)
        public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {
        fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
        return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo,"PROCESSING");
        }

        该代码的目的是先后修改同一条记录的两个不同字段,同一个事务中执行了两条Update语句,再分别查看下两条SQL的执行计划:分别用到了PRIMARY索引和idx_seller_transNo索引。
        有了以上这些已知信息,就可以开始排查死锁原因及其背后的原理了。
        通过分析死锁日志,再结合代码以及建表语句,发现主要问题出在idx_seller_transNo索引上面:

          KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))

          索引创建语句中,使用了前缀索引,为了节约索引空间,提高索引效率,只选择了fund_transfer_order_no字段的前20位作为索引值。
          因为fund_transfer_order_no只是普通索引,而非唯一性索引。又因为在一种特殊情况下,会有同一个用户的两个fund_transfer_order_no的前20位相同,这就导致两条不同的记录的索引值一样(因为seller_id 和fund_transfer_order_no(20)都相同 )。
          那么为什么fund_transfer_order_no的前20位相同会导致死锁呢?
          我们知道,在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

          • 主键索引的叶子节点存的是整行数据。在InnoDB中,主键索引也被称为聚簇索引(clustered index)。
          • 非主键索引的叶子节点的内容是主键的值,在InnoDB中,非主键索引也被称为非聚簇索引(secondary index)。

          死锁的发生与否,并不在于事务中有多少条SQL语句,死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。
          事务在以非主键索引为where条件进行Update的时候,会先对该非主键索引加锁,然后再查询该非主键索引对应的主键索引都有哪些,再对这些主键索引进行加锁。
          五、解决方案解决方案至此,我们分析清楚了导致死锁的根本原理以及其背后的原理。那么这个问题解决起来就不难了。
          可以从两方面入手,分别是修改索引和修改代码(包含SQL语句)。
          修改索引:只要我们把前缀索引 idx_seller_transNo中fund_transfer_order_no的前缀长度修改下就可以了。比如改成50。即可避免死锁。
          但是,改了idx_seller_transNo的前缀长度后,可以解决死锁的前提条件是update语句真正执行的时候,会用到fund_transfer_order_no索引。如果MySQL查询优化器在代价分析之后,决定使用索引 KEY idx_seller(seller_id),那么还是会存在死锁问题。原理和本文类似。
          所以,根本解决办法就是改代码:

          • 所有update都通过主键ID进行。
          • 在同一个事务中,避免出现多条update语句修改同一条记录。

          其他思考

          在死锁发生之后的一周内,前前后后做过很多中种推断及假设,最终还是要靠实践来验证自己的想法。遇到问题,不要想当然,亲手复现下问题,然后再来分析。

          相关文章

          Oracle如何使用授予和撤销权限的语法和示例
          Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
          下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
          社区版oceanbase安装
          Oracle 导出CSV工具-sqluldr2
          ETL数据集成丨快速将MySQL数据迁移至Doris数据库

          发布评论