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

最新推荐文章于 2023-03-28 11:54:11 发布

Java_Mike

最新推荐文章于 2023-03-28 11:54:11 发布

阅读量1.3k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/Java_Mike/article/details/117897997

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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

发现问题

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

分析问题

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

Java_Mike

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
2
评论
深入分析MySQL索引优化导致的死锁问题

死锁是我们在大学时就接触过的概念，由于鄙人资历尚浅，毕业后的开发过程中几乎没有碰到过死锁问题。然而常在河边走，哪有不湿鞋，最近就碰到了一个诡异的死锁问题，于是就抓住这次难得机会，对这次的死锁问题好好研究了一番。于是就总结出了这篇博客。发现问题某一天，我一如既往地对一个批量并发操作相关的业务进行自测，在自测过程中发现有两个线程执行失败了，通过日志发现是更新状态时导致的死锁问题。第一眼见到这个问题，我有点纳闷，明明更新的不是同一行记录，为什么会出现死锁呢？于是带着这个问题在网上找了一些大佬的文章，发现是索引
复制链接

扫一扫