深入分析MySQL索引优化导致的死锁问题

死锁是我们在大学时就接触过的概念,由于鄙人资历尚浅,毕业后的开发过程中几乎没有碰到过死锁问题。然而常在河边走,哪有不湿鞋,最近就碰到了一个诡异的死锁问题,于是就抓住这次难得机会,对这次的死锁问题好好研究了一番。于是就总结出了这篇博客。

发现问题

某一天,我一如既往地对一个批量并发操作相关的业务进行自测,在自测过程中发现有两个线程执行失败了,通过日志发现是更新状态时导致的死锁问题。第一眼见到这个问题,我有点纳闷,明明更新的不是同一行记录,为什么会出现死锁呢?于是带着这个问题在网上找了一些大佬的文章,发现是索引优化导致的死锁。

分析问题

MySQL5.1之后引入了index_merge索引优化技术,它允许对同一个表同时使用多个索引进行查询,并对多个索引的查询结果进行合并(取交集(intersect)、并集(union)等)后返回。而使用索引查询并更新记录是会加锁的。具体加锁逻辑如下(基于RC隔离级别):

  • 根据主键id进行更新
    update user set name=‘xxx’ where id=5;只需将id=5的记录加上X锁即可(X锁称为互斥锁,加锁后本事务可以读和写,其他事务读和写会被阻塞)。如图:
    在这里插入图片描述
  • 根据唯一索引进行更新
    update user set name=‘xxx’ where name=‘ddd’,这里假设name添加了唯一索引。InnDB先在name索引上找到找到name='ddd’的索引项(id=29)并加上X锁,然后根据id=29再到主键索引上找到对应的叶子节点并加上X锁。
    一共两把锁,一把加在唯一索引上,一把加在主键索引上。这里的加锁是一步步加的,不会同时给唯一索引和主键索引加锁。这种分步加锁的机制实际上也是导致死锁的诱因之一。如图:
    在这里插入图片描述
  • 根据非唯一索引进行更新
    update user set name=‘xxx’ where name=‘ccc’;这里假设name加的不是唯一索引,即根据name可以查到多条记录(id不同)。和上面唯一索引加锁类似,不同的是会给所有符合条件的索引项加锁。如图:
    在这里插入图片描述
    这里一共四把锁,加锁步骤如下:
  • 在非唯一索引(name)上找到(ccc,3)的索引项,加上X锁;
  • 根据(ccc,3)找到主键索引(3,ccc)的记录,加上X锁;
  • 在非唯一索引(name)上找到(ccc,4)的索引项,加上X锁;
  • 根据(ccc,4)找到主键索引的(4,ccc)记录,加上X锁。

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

前面提到,MySQL5.1之后引入的index_merge索引优化技术是将同一个表同时使用多个索引进行查询,并将结果合并,那么更新操作使用index_merge索引优化技术的话就会出现死锁问题。下面通过一个例子具体分析。
创建如下表:

CREATE TABLE `store`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `store` int(0) NULL DEFAULT NULL,
  `sku_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ws_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_sku_code`(`sku_code`) USING BTREE,
  INDEX `index_ws_code`(`ws_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

测试源码即相关数据可通过以下github地址获取,我这边使用的mysql版本是8.0.19。
https://github.com/javaMikes/deadlock-demo
主要测试代码如下:

public class TestIndexMerge {

    public static final String URL = "jdbc:mysql://127.0.0.1:3306/dead_lock_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";

    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        ExecutorService executorService = new ThreadPoolExecutor(300, 300,
                0L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue<>());
        for (int i = 0; i <= 100; i++) {
            final int index = i;
            final String wsCode = "ws_" + i;
            final String skuCode = "sku_" + i;
            executorService.submit(() -> {
                try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
                    for (int j = 0; j < 10000; j++) {
                        conn.createStatement()
                                .executeUpdate("update store set store = store - 1 WHERE sku_code='" + skuCode + "' and ws_code ='" + wsCode + "';");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
        }
    }

}

测试代码逻辑很简单,开启多个线程去更新store的库存store字段,执行代码后会抛出死锁异常。
在这里插入图片描述

在MySQl中执行以下命令可获取最新的死锁日志。

SHOW ENGINE INNODB STATUS;

死锁日志中,我们主要关注以下几行:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-06-14 11:12:02 0x488
*** (1) TRANSACTION:
TRANSACTION 8183525, ACTIVE 3 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 19 lock struct(s), heap size 3520, 127 row lock(s), undo log entries 1
MySQL thread id 3322, OS thread handle 18304, query id 3397979 localhost 127.0.0.1 root updating
update store set store = store - 1 WHERE sku_code='sku_31' and ws_code ='ws_31'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 24555 page no 21 n bits 328 index PRIMARY of table `dead_lock_test`.`store` trx id 8183525 lock_mode X locks rec but not gap
Record lock, heap no 148 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000c3b; asc    ;;;
 1: len 6; hex 0000007c8951; asc    | Q;;
 2: len 7; hex 010000011e2110; asc      ! ;;
 3: len 4; hex 800f34fe; asc   4 ;;
 4: len 6; hex 736b755f3331; asc sku_31;;
 5: len 5; hex 77735f3332; asc ws_32;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24555 page no 21 n bits 328 index PRIMARY of table `dead_lock_test`.`store` trx id 8183525 lock_mode X locks rec but not gap waiting
Record lock, heap no 49 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000bd8; asc     ;;
 1: len 6; hex 0000007c8951; asc    | Q;;
 2: len 7; hex 010000011e0c36; asc       6;;
 3: len 4; hex 800f34fe; asc   4 ;;
 4: len 6; hex 736b755f3332; asc sku_32;;
 5: len 5; hex 77735f3331; asc ws_31;;

*** (2) TRANSACTION:
TRANSACTION 8183585, ACTIVE 3 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 19 lock struct(s), heap size 3520, 126 row lock(s)
MySQL thread id 3387, OS thread handle 17144, query id 3398163 localhost 127.0.0.1 root updating
update store set store = store - 1 WHERE sku_code='sku_32' and ws_code ='ws_32'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24555 page no 21 n bits 328 index PRIMARY of table `dead_lock_test`.`store` trx id 8183585 lock_mode X locks rec but not gap
Record lock, heap no 49 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000bd8; asc     ;;
 1: len 6; hex 0000007c8951; asc    | Q;;
 2: len 7; hex 010000011e0c36; asc       6;;
 3: len 4; hex 800f34fe; asc   4 ;;
 4: len 6; hex 736b755f3332; asc sku_32;;
 5: len 5; hex 77735f3331; asc ws_31;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24555 page no 21 n bits 328 index PRIMARY of table `dead_lock_test`.`store` trx id 8183585 lock_mode X locks rec but not gap waiting
Record lock, heap no 148 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000c3b; asc    ;;;
 1: len 6; hex 0000007c8951; asc    | Q;;
 2: len 7; hex 010000011e2110; asc      ! ;;
 3: len 4; hex 800f34fe; asc   4 ;;
 4: len 6; hex 736b755f3331; asc sku_31;;
 5: len 5; hex 77735f3332; asc ws_32;;

由死锁日志可知,发生死锁的主要是这两条sql:

update store set store = store - 1 WHERE sku_code='sku_31' and ws_code ='ws_31'
update store set store = store - 1 WHERE sku_code='sku_32' and ws_code ='ws_32'

其中15-20行指的是事务1锁住的行数据,第15行0: len 4; hex 80000c3b; asc ;;;指的是对应行的主键id,将十六进制0c3b转换成十进制为3131,25-30行指的是事务1想要获取锁的行数据,对应的主键id为5730。所以事务1给主键id为3131的行记录加了X锁,并想要获取主键id为3032的行记录锁
同理可知,事务2给主键id为3032的行记录加了X锁,并想要获取主键id为3131的行记录锁。于是死锁就这样产生了。此时MySQL的处理方法是回滚持有锁最少的事务,并且JDBC会抛出我们前面的MySQLTransactionRollbackException回滚异常。
我们再深入分析一下这次死锁产生的流程。
通过explain命令分析上面产生死锁的两条sql。

explain update store set store = store - 1 WHERE sku_code='sku_31' and ws_code ='ws_31';
explain update store set store = store - 1 WHERE sku_code='sku_32' and ws_code ='ws_32';

发现这两条sql用的类型都是index_merge。
在这里插入图片描述
于是根据上面我们提到的索引加锁步骤,就能分析出产生这次死锁的流程。
在这里插入图片描述

如图所示,可知产生此次死锁的流程如下:

  • 事务1WHERE sku_code='sku_31' and ws_code ='ws_31'首先走index_sku_code索引,分别对二级索引和主键索引加锁成功(1-1和1-2)。
  • 此时事务2开始执行WHERE sku_code='sku_32' and ws_code ='ws_32',首先也是走index_sku_code索引,因为和事务1加锁记录不冲突,所以正常执行(2-1和2-2)。
  • 事务2继续执行,这是走的是index_ws_code(右上)索引,先对二级索引加锁成功(2-3,此时事务1还没开始对index_ws_code索引加锁),但是在对主键索引加锁时,发现id=3131的主键索引已经被事务1加锁,因此只能等待(2-4),同时在完成2-4加锁前,对其他记录的加锁也会暂停(因为InnoDB是逐条记录加锁的,前一条未完成则后面的不会执行)。
  • 此时事务1继续执行。这是走的是index_ws_code索引,先对二级索引加锁成功(1-3),再对主键索引加锁,这时发现id=3032的主键索引已经被事务2加锁,因此也只能等待(1-4)。

综上所述,就出现了两个事务互相等待对方释放资源的情况,并且拥有对方需要的资源,于是就产生了死锁。

解决问题

既然问题原因找到了,如何才能避免这种死锁的发生呢?
死锁的本质原因还是由于加锁顺序不同,本例中是由于index merge同时使用2个索引方向加锁导致,解决方法也比较简单,就是消除因index merge带来的多个索引同时执行的情况。

方案一

利用force index(index_sku_code)强制走某个索引,这样InnoDB就会忽略index merge,避免多个索引同时加锁的情况。

explain update store force index(index_sku_code) set store = store - 1 where sku_code='sku_31' and ws_code ='ws_31';

在这里插入图片描述

方案二

禁用Index Merge,这样InnoDB只会使用index_sku_code和index_ws_code中的一个,所有事务加锁顺序都一样,不会造成死锁。

SET GLOBAL optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off';
方案三

既然index merge同时使用了2个独立索引,我们可以新建一个包含这两个索引所有字段的联合索引,这样InnoDB就只会走这个单独的联合索引。ps:我就是通过这个方法解决的。

alter table store add index index_skucode_wscode(sku_code,ws_code);
方案四

可以先利用idx_skucode和idx_wscode查询到主键id,再拿主键id进行update操作。这种方式避免了由update引入X锁,由于最终更新的条件是唯一固定的,所以不存在加锁顺序的问题,避免了死锁的产生。但是个人觉得这样改动有点大。

心得

死锁案例到这里就分析结束了,解决了这个问题后,感觉自己对死锁的相关概念有了更清晰的认识。希望这篇文章能给遇到死锁难题的你提供一个更好的思路。

文中难免会有一些错误或者不合理的地方,欢迎提出,不吝赐教!

参考链接
https://www.cnblogs.com/vivotech/p/14313671.html
https://segmentfault.com/a/1190000039265975

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值