数据源使用错误导致MySQL事务失效分析

1. 前言

在Java应用中使用MySQL数据库时，可能出现因为使用的数据源或数据库会话错误，导致事务失效的问题。

在Java应用中使用多数据源时，可能在执行SQL语句的不同阶段使用了不相同的数据源，导致事务失效，以下主要对此类问题进行分析。

在Java应用中访问MySQL服务时，涉及Java应用、网络传输、MySQL服务这三层，在每一层都可以对执行的SQL语句与事务操作进行监控与观测，涉及的内容如下图所示：

可参考以下相关内容：

内容	链接
MySQL SQL语句与事务执行及日志分析	https://blog.youkuaiyun.com/a82514921/article/details/126563449
tcpdump、Wireshark抓包分析MySQL SQL语句与事务执行	https://blog.youkuaiyun.com/a82514921/article/details/126563471
Spring、MyBatis、Druid、MySQL不使用事务执行SQL语句分析	https://blog.youkuaiyun.com/a82514921/article/details/126563515
Spring、MyBatis、Druid、MySQL使用事务执行SQL语句分析	https://blog.youkuaiyun.com/a82514921/article/details/126563542
Spring、MyBatis、Druid、MySQL执行SQL语句与事务监控	https://blog.youkuaiyun.com/a82514921/article/details/126563558
数据源使用错误导致MySQL事务失效分析	https://blog.youkuaiyun.com/a82514921/article/details/126563573
TiDB乐观事务、悲观事务模型验证	https://blog.youkuaiyun.com/a82514921/article/details/126563502

2. 示例项目

以下使用的示例项目下载地址为：https://github.com/Adrninistrator/DB-Transaction-test，使用说明可参考“README.md”，

相关的执行日志保存在DB-Transaction-test-log目录中，log-print_stack_trace_off目录中是未打印调用堆栈的日志，log-print_stack_trace_on目录中是打印调用堆栈的日志。

3. 验证环境

• JDK版本

1.8

• Spring版本

5.3.20

• Mybatis版本

org.mybatis:mybatis:3.2.8
org.mybatis:mybatis-spring:1.2.2

• Druid版本

1.2.10

• MySQL Connector版本

8.0.29

• MySQL版本

MariaDB 10.0.36

• Spring事务传播机制

默认值REQUIRED

4. 使用多数据源时事务正常生效的使用方式

在示例项目的Spring XML配置文件中，定义了两个DruidDataSource数据源，分别为dataSource1、dataSource2。

为了方便使用，以上两个数据库源连接同一个数据库的同一个用户（不会影响验证效果）。

在Spring XML中，定义了分别使用两个数据源的SqlSessionFactoryBean与MapperScannerConfigurer：

通过SqlSessionFactoryBean的dataSource属性指定使用的数据源，通过mapperLocations指定扫描的MyBatis XML文件范围；

通过MapperScannerConfigurer的basePackage属性指定扫描的MyBatis Mapper接口所在的包。

以下为dataSource1对应的配置：

<bean id="ds1SqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="failFast" value="true"/>
    <property name="dataSource" ref="dataSource1"/>
    <property name="mapperLocations">
        <list>
            <value>classpath*:test/db/dao/ds1/*.xml</value>
        </list>
    </property>
</bean>

<bean id="ds1MapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage">
        <value>test.db.dao.ds1</value>
    </property>
    <property name="sqlSessionFactoryBeanName" value="ds1SqlSessionFactory"/>
</bean>

以下为dataSource2对应的配置：

<bean id="ds2SqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="failFast" value="true"/>
    <property name="dataSource" ref="dataSource2"/>
    <property name="mapperLocations">
        <list>
            <value>classpath*:test/db/dao/ds2/*.xml</value>
        </list>
    </property>
</bean>

<bean id="ds2MapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage">
        <value>test.db.dao.ds2</value>
    </property>
    <property name="sqlSessionFactoryBeanName" value="ds2SqlSessionFactory"/>
</bean>

通过以上配置，使用不同的MyBatis Mapper接口（及对应的MyBatis XML文件）时，相关的数据库操作会在对应的数据源中进行：

• 使用test.db.dao.ds1包中的Ds1TaskLockMapper接口时，会使用数据源dataSource1执行相关数据库操作；

• 使用test.db.dao.ds2包中的Ds2TaskLockMapper接口时，会使用数据源dataSource2执行相关数据库操作。

在Spring XML中，定义了分别使用两个数据源的事务管理器，事务管理器ds1TransactionManager使用数据源dataSource1，事务管理器ds2TransactionManager使用数据源dataSource2：

<bean id="ds1TransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource1"/>
</bean>

<bean id="ds2TransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource2"/>
</bean>

使用“tx:annotation-driven”指定开启通过注解使用事务，指定默认的事务管理器为routingTransactionManager：

<tx:annotation-driven transaction-manager="routingTransactionManager" proxy-target-class="true"/>

4.1. 多数据源不支持动态切换

4.1.1. 通过@Transactional注解使用事务

使用多数据源，不支持动态切换时，假如通过@Transactional注解使用事务，为了使事务生效，MyBatis Mapper接口对应的MapperScannerConfigurer的SqlSessionFactory，与@Transactional注解transactionManager属性指定的事务管理器，需要使用相同的数据源:

例如MyBatis Mapper接口使用数据源dataSource1对应的Ds1TaskLockMapper时，则@Transactional注解的事务管理器也需要使用数据源dataSource1对应的ds1TransactionManager；

MyBatis Mapper接口使用数据源dataSource2对应的Ds2TaskLockMapper时，则@Transactional注解的事务管理器也需要使用数据源dataSource2对应的ds2TransactionManager。

4.1.2. 通过TransactionTemplate使用事务

在Spring XML中，定义了两个事务模板TransactionTemplate，其事务管理器分别使用两个不同的数据源：

<bean id="ds1TransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
    <property name="transactionManager" ref="ds1TransactionManager"/>
</bean>

<bean id="ds2TransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
    <property name="transactionManager" ref="ds2TransactionManager"/>
</bean>

使用多数据源，不支持动态切换时，假如通过TransactionTemplate使用事务，为了使事务生效，MyBatis Mapper接口对应的MapperScannerConfigurer的SqlSessionFactory，与TransactionTemplate的事务管理器，需要使用相同的数据源：

例如MyBatis Mapper接口使用数据源dataSource1对应的Ds1TaskLockMapper时，则TransactionTemplate需要使用ds1TransactionTemplate，其事务管理器ds1TransactionManager对应的数据源为dataSource1；

MyBatis Mapper接口使用数据源dataSource2对应的Ds2TaskLockMapper时，则TransactionTemplate需要使用ds2TransactionTemplate，其事务管理器ds2TransactionManager对应的数据源为dataSource2。

4.2. 多数据源支持动态切换

在Spring XML中，定义了支持动态切换（路由）的数据源testRoutingDataSource，对应的类为test.db.datasource.TestRoutingDataSource（父类为Spring的org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource），指定默认数据源为dataSource1。通过targetDataSources属性指定，key为dataSourceKey1时，使用数据源dataSource1；key为dataSourceKey2时，使用数据源dataSource2：

<bean id="testRoutingDataSource" class="test.db.datasource.TestRoutingDataSource">
    <property name="defaultTargetDataSource" ref="dataSource1"/>
    <property name="targetDataSources">
        <map>
            <entry key="dataSourceKey1" value-ref="dataSource1"/>
            <entry key="dataSourceKey2" value-ref="dataSource2"/>
        </map>
    </property>
</bean>

在TestRoutingDataSource类中，通过ThreadLocal保存当前使用的数据源的key，重载AbstractRoutingDataSource类中决定使用的数据源key的determineCurrentLookupKey()方法，在该方法中返回了ThreadLocal的值，并提供了分别使用数据源dataSource1、dataSource2的方法setDSKey1()、setDSKey2()：

public class TestRoutingDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<String> threadLocalDSKey = new ThreadLocal<>();

    @Override
    protected Object determineCurrentLookupKey() {
        return threadLocalDSKey.get();
    }

    public static void setDSKey1() {
        setDSKey("dataSourceKey1");
    }

    public static void setDSKey2() {
        setDSKey("dataSourceKey2");
    }

    private static void setDSKey(String dataSourceKey) {
        threadLocalDSKey.set(dataSourceKey);
    }

    public static void clearDSKey() {
        threadLocalDSKey.remove();
    }
}

在Spring XML中，定义了使用以上数据源的SqlSessionFactoryBean与MapperScannerConfigurer：

<bean id="routingSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="failFast" value="true"/>
    <property name="dataSource" ref="testRoutingDataSource"/>
    <property name="mapperLocations">
        <list>
            <value>classpath*:test/db/dao/routing/*.xml</value>
        </list>
    </property>
</bean>

<bean id="routingMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage">
        <value>test.db.dao.routing</value>
    </property>
    <property name="sqlSessionFactoryBeanName" value="routingSqlSessionFactory"/>
</bean>

test.db.dao.routing包中的MyBatis Mapper接口为RoutingTaskLockMapper。

在Spring XML中，定义了使用以上数据源的事务管理器routingTransactionManager：

<bean id="routingTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="testRoutingDataSource"/>
</bean>

4.2.1. 通过@Transactional注解使用事务

使用多数据源支持动态切换时，假如通过@Transactional注解使用事务，为了使事务生效，MyBatis Mapper接口与@Transactional注解指定的事务管理器，都需要使用支持动态切换的数据源；且ThreadLocal需要使用相同的key:

MyBatis Mapper接口需要使用数据源testRoutingDataSource对应的RoutingTaskLockMapper；

@Transactional注解的事务管理器也需要使用数据源testRoutingDataSource对应的routingTransactionManager；

且执行数据库操作期间，TestRoutingDataSource中的ThreadLocal使用的key不能变化。

4.2.2. 通过TransactionTemplate使用事务

在Spring XML中，定义了使用以上事务管理器routingTransactionManager的事务模板routingTransactionTemplate：

<bean id="routingTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
    <property name="transactionManager" ref="routingTransactionManager"/>
</bean>

使用多数据源支持动态切换时，假如通过TransactionTemplate使用事务，为了使事务生效，MyBatis Mapper接口与TransactionTemplate的事务管理器，都需要使用支持动态切换的数据源，且ThreadLocal需要使用相同的key:

MyBatis Mapper接口需要使用数据源testRoutingDataSource对应的RoutingTaskLockMapper；

TransactionTemplate的事务管理器也需要使用数据源testRoutingDataSource对应的routingTransactionManager；

且执行数据库操作期间，TestRoutingDataSource中的ThreadLocal使用的key不能变化。

5. 使用多数据源时事务失效问题

5.1. 导致事务失效的情况

• 通过@Transactional注解使用事务

使用多数据源，且通过@Transactional注解使用事务时，假如MyBatis Mapper接口对应的MapperScannerConfigurer的SqlSessionFactory，与@Transactional注解指定的事务管理器，使用的数据源不相同，则事务会失效。

• 通过TransactionTemplate使用事务

使用多数据源，且通过TransactionTemplate使用事务，假如MyBatis Mapper接口对应的MapperScannerConfigurer的SqlSessionFactory，与TransactionTemplate的事务管理器，使用的数据源不相同，则事务会失效。

例如使用的MyBatis Mapper接口对应的数据源为dataSource1，则@Transactional注解或TransactionTemplate的事务管理器的数据源不使用dataSource1时，事务就会失效（例如使用dataSource2或testRoutingDataSource）。

假如MyBatis Mapper接口对应的数据源为dataSource1，事务管理器的数据源使用testRoutingDataSource，且通过key指定内部使用数据源dataSource1，事务也会失效。

5.2. 事务失效时SQL语句执行情况

• 事务执行过程中未出现异常

事务执行过程中未出现异常时，最后会提交事务。若事务失效，每条SQL语句执行后会立刻自动提交；

• 事务执行过程中出现异常

事务执行过程中出现特定类型的异常时，最后会回滚事务。若事务失效，每条SQL语句执行后会立刻自动提交，且对数据库的修改操作不会回滚。

以上原因是Druid的com.alibaba.druid.pool.DruidPooledConnection类的rollback()方法中，判断成员变量TransactionInfo transactionInfo非null时才调用Connection conn的rollback()方法进行回滚，若为null则不执行回滚。

当事务失效时，以上成员变量为null，不会执行后续的回滚操作。

DruidPooledConnection.rollback()方法代码如下：

public void rollback() throws SQLException {
    if (transactionInfo == null) {
        return;
    }

    if (holder == null) {
        return;
    }

    DruidAbstractDataSource dataSource = holder.getDataSource();
    dataSource.incrementRollbackCount();

    try {
        conn.rollback();
    } catch (SQLException ex) {
        handleException(ex, null);
    } finally {
        handleEndTransaction(dataSource, null);
    }
}

6. 监控多数据源事务失效问题

在Java应用、网络传输、MySQL服务这三层，每一层可以使用的对SQL语句与事务执行的分析及监控方式不相同，可参考以下内容：

6.1. 在MySQL服务层监控事务失效问题

在MySQL服务层，可以通过一般查询日志（general_log）监控事务失效问题，对应MySQL的mysql.general_log表，thread_id列代表连接ID（MySQL服务线程ID）。

以上示例项目执行SQL语句出现事务失效问题时，查询mysql.general_log表的内容如下：

通过以上日志可以看出：

执行“SET autocommit=0”语句以开启事务的会话的连接ID为15；

执行“select for update”、“update”等SQL语句会话的连接ID为16，且该会话已开启自动提交；

执行“commit”语句以提交事务的会话的连接ID为15。

开启事务/提交事务，与执行SQL语句时使用的连接ID不同，不在同一个会话中，说明事务失效。

6.2. 在网络传输层监控事务失效问题

使用tcpdump或Wireshark抓包，可用于监控MySQL客户端与服务器之间的通信数据，即SQL语句的执行情况。

在Wireshark过滤器中输入“mysql and tcp.dstport==3306”过滤条件并应用，可指定只展示MySQL协议，且目标端口为3306，即MySQL客户端请求MySQL服务的数据。“Src port”列代表源端口。

以上示例项目执行SQL语句出现事务失效问题时，通过Wireshark抓包结果如下：

通过以上可以看出：

执行“SET autocommit=0”语句以开启事务的连接的源端口为52079；

执行“select for update”、“update”等SQL语句连接的源端口为52082，且该会话已开启自动提交；

执行“commit”语句以提交事务的连接的源端口为52079。

开启事务/提交事务，与执行SQL语句时使用的源端口不同，不属于同一个连接，即不在同一个会话中，说明事务失效。

6.3. 在Java应用层监控事务失效问题

使用Druid的自定义Filter，可对SQL语句及事务执行情况进行监控。

6.3.1. 人工分析

以上示例项目执行SQL语句出现事务失效问题时，Druid的自定义Filter打印的相关日志如下，可通过人工分析确认：

以下日志中的connThreadId代表连接ID，localPort代表源端口。

2022-07-11 21:11:10.984 [main] DEBUG DruidMonitorFilter.dataSource_getConnection(70) - ### [从连接池借出连接] connThreadId 121 dataSourceName ds2
SqlSessionInfo{connectionHashCode='54ee3737', connThreadId=121, localPort=65475, dataSourceName='ds2', autoCommit=true, serverIP='127.0.0.1', serverPort=3306, dbName='testdb', userName='test', serverVersion='5.5.5-10.0.36-MariaDB'}
2022-07-11 21:11:11.000 [main] DEBUG DruidMonitorFilter.connection_setAutoCommit(242) - ### [关闭自动提交] connThreadId 121 自动提交
SqlSessionInfo{connectionHashCode='54ee3737', connThreadId=121, localPort=65475, dataSourceName='ds2', autoCommit=true, serverIP='127.0.0.1', serverPort=3306, dbName='testdb', userName='test', serverVersion='5.5.5-10.0.36-MariaDB'}

以上执行关闭自动提交以开启事务的会话的连接ID为121，连接的源端口为65475；

2022-07-11 21:11:11.047 [main] DEBUG DruidMonitorFilter.dataSource_getConnection(70) - ### [从连接池借出连接] connThreadId 122 dataSourceName ds1
SqlSessionInfo{connectionHashCode='16f034a3', connThreadId=122, localPort=65476, dataSourceName='ds1', autoCommit=true, serverIP='127.0.0.1', serverPort=3306, dbName='testdb', userName='test', serverVersion='5.5.5-10.0.36-MariaDB'}
2022-07-11 21:11:11.063 [main] DEBUG DruidMonitorFilter.statementExecuteBefore(159) - ### [执行SQL语句] connThreadId 122 自动提交
select
        task_name, lock_flag, begin_time, end_time, process_info, now() as db_current_date
        from task_lock
        where task_name = ? for update
SqlSessionInfo{connectionHashCode='16f034a3', connThreadId=122, localPort=65476, dataSourceName='ds1', autoCommit=true, serverIP='127.0.0.1', serverPort=3306, dbName='testdb', userName='test', serverVersion='5.5.5-10.0.36-MariaDB'}
2022-07-11 21:11:11.079 [main] DEBUG DruidMonitorFilter.statementExecuteBefore(159) - ### [执行SQL语句] connThreadId 122 自动提交
update task_lock
        set
        lock_flag = ?,
        begin_time = now(),
        process_info = ?
        where task_name = ?
SqlSessionInfo{connectionHashCode='16f034a3', connThreadId=122, localPort=65476, dataSourceName='ds1', autoCommit=true, serverIP='127.0.0.1', serverPort=3306, dbName='testdb', userName='test', serverVersion='5.5.5-10.0.36-MariaDB'}

以上执行“select for update”、“update”等SQL语句连接的会话的连接ID为122，连接的源端口为65476；

2022-07-11 21:11:11.079 [main] DEBUG DruidMonitorFilter.connection_commit(188) - ### [提交事务] connThreadId 121 不自动提交 当前连接SQL语句执行次数: 0
SqlSessionInfo{connectionHashCode='54ee3737', connThreadId=121, localPort=65475, dataSourceName='ds2', autoCommit=false, serverIP='127.0.0.1', serverPort=3306, dbName='testdb', userName='test', serverVersion='5.5.5-10.0.36-MariaDB'}

以上执行提交事务的会话的连接ID为121，连接的源端口为65475。

开启事务/提交事务，与执行SQL语句时使用的连接ID不同，不在同一个会话中，说明事务失效。

6.3.2. 应用程序告警

在出现事务失效问题时，除了人工分析日志的方式外，在Druid的自定义Filter DruidMonitorFilter类中，也支持通过应用程序进行告警。

在DruidMonitorFilter类的statementExecuteBefore()方法中，判断执行SQL语句的连接ID与开启事务时的是否相同，若不同则说明出现事务失效问题，可以告警，输出的日志如下所示：

2022-07-11 21:11:11.063 [main] DEBUG DruidMonitorFilter.statementExecuteBefore(166) - @@@ 执行SQL语句的连接ID（MySQL服务线程ID）与开启事务时的不同 122@[ds1] 121@[ds2]
2022-07-11 21:11:11.079 [main] DEBUG DruidMonitorFilter.statementExecuteBefore(166) - @@@ 执行SQL语句的连接ID（MySQL服务线程ID）与开启事务时的不同 122@[ds1] 121@[ds2]

在DruidMonitorFilter类的statementExecuteBefore()方法中，判断提交事务时当前连接的SQL语句执行次数，若为0则说明可能出现事务失效问题，可以告警，输出的日志如下所示：

2022-07-11 21:11:11.079 [main] DEBUG DruidMonitorFilter.connection_commit(188) - ### [提交事务] connThreadId 121 不自动提交 当前连接SQL语句执行次数: 0