记录:Sharding-Jdbc 配置max.connections.size.per.query造成的死锁问题

记录:Sharding-Jdbc 配置max.connections.size.per.query造成的死锁问题

项目场景:

版本:jdk11,sharding-jdbc4.1.1,mysql8.0
分表:table表根据 主键id 水平分表,分为10张表,即table_$->{0…9}

精简后的场景代码:

    @Transactional(rollbackFor = Exception.class)
    public void update(List<Long> ids) {
        for (Long id : ids) {
            // update table set ...... where id = #{id};
            updateById(id);
        }

        /*
        update table set ...... where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
         */
        updateByIds(ids);
    }

问题描述:

整个方法体 使用用spring-Transaction注解,应该只有一个事务存在,但是当ids值不同时,即路由到不同的分表时,却出现两个事务(A事务、B事务),A事务执行执行updateById方法sql,B事务是执行updateByIds方法的sql,B事务需要用到A事务的行锁,而A事务一直在Running状态,B事务一直处于锁等待状态,直到触发mysql的锁超时机制,两事务才同时结束。
记录:Sharding-Jdbc 配置max.connections.size.per.query造成的死锁问题

所以重点排查两个问题:
  1. 为什么会有两个事务;
  2. 为什么A事务迟迟不结束,直到触发mysql的锁超时机制,才结束;

原因分析:

根据场景分析,还原事务的执行情况:

sessionA sessionB
1 update table_1 set … where id = 1;
2 update table_2 set … where id = 2;
3 update table_1 set … where id in( 1,2 ); update table_2 set … where id in( 1,2 );

1. 为什么会有两个事务:

由于配置sharding-jdbc参数:max.connections.size.per.query: 2
updateByIds方法,根据路由、改写后,会有2个sql(分别对应不同的分表)
sharding-jdbc在执行的准备阶段,会在maxConnectionSizePerQuery的允许范围内,为每个sql创建对应的connection,所以就有2个connection,对应上表的第3步,table1-sql在spring事务处,table2-sql是一个新connection、新的事务;

2. 为什么A事务迟迟不结束,直到触发mysql的锁超时机制,才结束:

由上表可知,A事务 占着table1表的id=1的行锁,还占着table2表的id=2的行锁。
B事务 更新table2表id=2这行,也需要id=2的行锁,所以B事务 等待着 事务A提交事务后释放id=2的行锁。
而由于B事务是在A事务代码里的,A事务需要等待B事务代码执行完,才会commit。
因此从业务层面 造成了死锁。

解决方案:

  1. 将更新合并为 同一个sql。
  2. 将max.connections.size.per.query修改为1。但修改成1会降低查询的效率;大于1又不适合OLTP操作,所以需要在设计业务代码时,考虑更全面。

具体sharding-jdbc执行引擎的准备阶段原理、源码 可以看下一篇博客:Sharding-Jdbc执行引擎准备阶段源码分析

问题回溯:

  1. 业务代码的设计考虑不全面;
  2. 对sharding-jdbc执行引擎不够熟悉;
  3. 想当然的以为添加spring事务注解后,只会有一个事务存在;
上一篇:MySQL增删改总结归纳


下一篇:打印乘法口诀表