记录：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的锁超时机制，两事务才同时结束。

所以重点排查两个问题：

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事务注解后，只会有一个事务存在；