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

梦里梦外; 2023-10-07 19:35 13阅读 0赞

## 1. 前言 ##

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

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

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

![在这里插入图片描述][67e75dc758bb497980067ff67b605c95.png_pic_center]

可参考以下相关内容：

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

## 2. 示例项目 ##

以下使用的示例项目下载地址为：[https://github.com/Adrninistrator/DB-Transaction-test][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：

使用“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，其事务管理器分别使用两个不同的数据源：

`使用多数据源，不支持动态切换时，假如通过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：

在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：

#### 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：

`使用多数据源支持动态切换时，假如通过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表的内容如下：

![在这里插入图片描述][d27af76b00cd45b19b9fdb66edde9e15.png_pic_center]

通过以上日志可以看出：

执行“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抓包结果如下：

![在这里插入图片描述][24b69412f7d34a13abda79cf0bc39c69.png_pic_center]

通过以上可以看出：

执行“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

[67e75dc758bb497980067ff67b605c95.png_pic_center]: https://img-blog.csdnimg.cn/67e75dc758bb497980067ff67b605c95.png#pic_center
[https_github.com_Adrninistrator_DB-Transaction-test]: https://github.com/Adrninistrator/DB-Transaction-test
[d27af76b00cd45b19b9fdb66edde9e15.png_pic_center]: https://img-blog.csdnimg.cn/d27af76b00cd45b19b9fdb66edde9e15.png#pic_center
[24b69412f7d34a13abda79cf0bc39c69.png_pic_center]: https://img-blog.csdnimg.cn/24b69412f7d34a13abda79cf0bc39c69.png#pic_center