Skip to main content

mysql

查看所有分类
数据库 Index-Merge 导致的死锁

问题

监控系统，告警发现 serviceA-task 定时同步供应商订单任务执行出现失败，后来发现 serviceB-api 的日志报文中也一直有死锁存在，Log 日志具体如下：

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction, dubbo version: 2.13.2.RELEASE, current host: 10.36.131.12 org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

问题原因

问题背景

（1）RPC 接口：InboundAppointmentRpcService.createInboundAppointment

（2）Task 任务：shire-task#supplierSaleOrderAggregateTask

上述都会对表 wms_supplier_order_info 执行下面语句更新数据库记录

@Override
    public int updateSupplierOrderInfo(WmsSupplierOrderInfo reocrd) {
        if (Objects.isNull(reocrd)) {
            return 0;
        }
        WmsSupplierOrderInfoExample example = new WmsSupplierOrderInfoExample();
        example.createCriteria()
                .andWarehouseIdEqualTo(reocrd.getWarehouseId())
                .andSupplierIdEqualTo(reocrd.getSupplierId())
                .andGoodsIdEqualTo(reocrd.getGoodsId())
                .andExpectTimeEqualTo(reocrd.getExpectTime())
                .andIsDeletedEqualTo(Boolean.FALSE);
        return mapper.updateByExampleSelective(reocrd, example);
    }

问题原因分析

分析进度 1：

按照上述的更新语句，模拟写了一条查询语句（和上面更新语句的条件是一样的），查看执行计划，出现一条语句走了两条索引，索引类型为 index merge

explain SELECT * FROM wms_supplier_order_info where warehouse_id = 2 and supplier_id = 434135643 and goods_id = 175091807986 and expect_time = '2020-10-12 00:00:00' and is_deleted = 0

image-20240509172744833.png

查了文档，发现出现 index merge 操作会大大增加数据库死锁的概率-附上官方文档：https://bugs.mysql.com/bug.php?id=77209

分析进度 2

找 DBA 同事拿到了当时的死锁日志如下，并还原当时的 DDL 更新语句：

还原当时场景下对应的 SQL 语句，如下：

第一个事务对应的语句

SET warehouse_id = 6880,
        supplier_id = 334985823,
        expect_time = '2021-01-26 00:00:00',
        cargo_id = 218057,
        goods_id = 207967301292,
        goods_name = '【无辣不欢】香辣小米椒100g',
        quantity = 66,
        fetch_time = '2021-01-25 01:31:42.944000',
        sku_spec = '100g/份',
        supplier_name = '河源市喜悦生态农业科技有限公司'
     WHERE (  warehouse_id = 6880
                  and supplier_id = 334985823
                  and goods_id = 207967301292 and expect_time = '2021-01-26 00:00:00' and is_deleted = "false"

第二个事务对应的语句

SET id = 1566583,
        warehouse_id = 3997,
        supplier_id = 735844057,

        expect_time = '2021-01-26 00:00:00',
        cargo_id = 26557,
        goods_id = 184949669110,
        goods_name = '苏菲超熟睡柔棉感420mm4片+弹力贴身纤巧230mm10片日夜组合装',
        quantity = 11,
        fetch_time = '2021-01-25 00:47:37',
        created_at = '2021-01-25 00:03:08',
        updated_at = '2021-01-25 00:47:37',
        is_deleted = 0,
        sku_spec = '14片/条',
        supplier_name = '恒安金盛居家日用专营店',
        `status` = 1,
        expect_first_arrival_time = '2021-01-25 10:00:00' , expect_quantity = 400 where ( warehouse_id = 3997
                  and supplier_id = 735844057
                  and goods_id = 184949669110 and expect_time = '2021-01-26 00:00:00' and is_deleted = "false")

对上面的死锁日志进行分析

（1）事务 1 TRANSACTION(1):持有 4 把行锁(4 row lock(s)), 其中一把处于等待状态((1) WAITING FOR THIS LOCK TO BE GRANTED)，位于页面编号为 61 的地方，锁针对的是主键索引(index PRIMARY of table)的记录锁(Record lock)，只锁记录不锁区间

（2）事务 2 TRANSACTION(2)持有 23 把锁，其中持有((2) HOLDS THE LOCK(S))页面编号为 61 的一个主键记录锁(正是事务 1 在等待的)，有 15 把是处于所等待的状态，就是等待索引 index idx_expect_time_is_deleted 的锁

（3）进一步分析，事务 1 需要的那把锁正好在事务 2 的手里，而对于事务 2 需要获取的索引 index idx_expect_time_is_deleted 的锁，发现已经被事务 1 占用，只能阻塞等待，出现了互相等待问题，进入死锁

模拟分析当时的两个事务加锁的场景可能如下：

image-20240509173205913.png

解决方式

1、尽量避免使用多 where 条件更新记录，可以先查询出来，然后根据主键更新。

2、优化索引，创建联合索引 goods_id,expect_time,is_deleted

3、避免使用大事务，在事务内的加上的行锁，只能在 commit 后，锁才会释放，控制所有锁的时间

4、关闭 MySQL 优化器的 index merge 功能

附录

问题：

1、为什么使用 Merge Index 技术呢？

答：Merge Index 索引合并技术是 MySQL 的一个优化，对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)

参考：

Merger-Index 导致死锁：https://blog.csdn.net/daidaineteasy/article/details/109266083

Merge-Index 技术剖析：https://www.orczhou.com/index.php/2013/01/mysql-source-code-query-optimization-index-merge/

Merge-Index 技术官方介绍：https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

👍点赞👏评论2024-11-19T06:21:05.000Z
MySQL/介绍

两大类索引

使用的存储引擎：MySQL5.7 InnoDB

聚簇索引

* 如果表设置了主键，则主键就是聚簇索引
* 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
* 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

InnoDB 的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB 必须要有至少一个聚簇索引。 由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。

普通索引

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。 InnoDB 的普通索引叶子节点存储的是主键（聚簇索引）的值，而 MyISAM 的普通索引存储的是记录指针。

👍点赞👏评论2024-11-19T06:21:05.000Z
MySQL/联合索引

联合索引数据结构

可以参考：https://www.cnblogs.com/l1pe1/p/16965628.html

image-20240402174500215

总结：

1、一个非叶子节点（应该是个块的大小？），会存在很多列值对（联合索引值），能存放多少，取决于字段属性设置的大小。

2、当前节点，左边子节点小于 A，右边子节点大于 A，

3、节点上存放的值：A 是有序的，A 相等的情况下 B 是有序的。

4、叶子节点会使用链表排序链接起来

MYSQL

1）索引的原理、组成、使用：

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

美团技术团队：

1）慢查询排查：

https://tech.meituan.com/2014/06/30/mysql-index.html

淘宝技术团队：

http://mysql.taobao.org/monthly/

官方锁文档：

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

（1）数据库内核报告：http://mysql.taobao.org/monthly/

（2）MySQL 索引原理及慢查询优化：https://tech.meituan.com/2014/06/30/mysql-index.html

（3）MySQL 实战 45 讲：https://time.geekbang.org/column/intro/139

（4）磁盘 IO 那些事：https://tech.meituan.com/2017/05/19/about-desk-io.html

（5）《MySQL 内核：InnoDB 存储引擎》

联合索引结构图：

image-20240510142940891

👍点赞👏评论2024-11-19T06:21:05.000Z