动态数据源事务异常

最新推荐文章于 2022-11-28 14:34:54 发布

xinyuan_java

最新推荐文章于 2022-11-28 14:34:54 发布

阅读量481

点赞数

分类专栏： java j2ee框架文章标签：动态数据源事务异常 CannotCreateTransactionExcepti

本文链接：https://blog.csdn.net/xinyuan_java/article/details/101674789

版权

在使用Spring动态数据源并添加@Transactional注解实现事务管理时，遇到在生产环境中出现 CannotCreateTransactionException 异常。问题根源在于测试环境数据库默认存储引擎为MyISAM（不支持事务），而生产环境为InnoDB（支持事务）。调整数据库配置，确保事务一致性，解决了事务异常问题。

摘要由CSDN通过智能技术生成

场景

系统中使用 spring的动态数据源，在迁移程序时，直接加上 @Transactional 注解，实现事务

1. 在测试环境测试没有问题，在生产出现了如下bug

2. 断点数据一切正常，在执行DBupdate的时候出现

MySQLNonTransientConnectionException

CannotCreateTransactionException(at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin)

解决

1. 生产出问题，测试没能重现原因

生产和测试的额数据库不一致：

生产的数据库默认的存储引擎是 InnoDB，支持事务

测试数据库默认是 MyISAM ，不支持事务

2. spring动态数据源的事务配置

@Bean
public PlatformTransactionManager mainTransactionManager
  (@Qualifier("mainDataSourceDynamic") DynamicDataSource masterDataSource) 
    {
	 return new DataSourceTransactionManager(masterDataSource);
    }

错误日志

2019-09-27 19:46:56.312 [http-nio-10010-exec-6] INFO  c.y.c.a.s.web.filter.SecurityFilter -doFilterInternal..........42275b09-5d66-4c8e-b8f2-8940dbf0d20f
2019-09-27 19:46:56.312 [http-nio-10010-exec-6] INFO  c.y.c.a.s.web.filter.SecurityFilter -doFilterInternal.......... org
2019-09-27 19:46:56.313 [http-nio-10010-exec-6] INFO  c.y.c.a.s.web.filter.SecurityFilter -doFilterInternal.......... org
2019-09-27 19:46:56.322 [http-nio-10010-exec-6] INFO  c.y.c.a.s.web.CustomerController - update Info Id: 123456789 Info: {       "nickName": "nick",     "unit": "cccccc",       "organization": 1,      "qualificatType": 1,"qualificatNo": "56214777777",   "idcardNo": "2000885221149662",         "industryType": 2,      "Status": 1,         "isInside": 1,  "principal": "XXXXX",   "violationNo": 1,       "violationMonthNo": 1,  "freezeNo": 1 }
2019-09-27 19:46:56.322 [http-nio-10010-exec-6] INFO  c.y.c.a.s.web.CustomerController - update custinfo Id: 123456789 oldMoblie:  newMoblie:  scAddress:  scProvince:  scSex: null cusExts: 
2019-09-27 19:47:14.349 [http-nio-10010-exec-6] ERROR o.a.tomcat.jdbc.pool.ConnectionPool -Unable to create initial connections of pool.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 10 times. Giving up.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.Util.getInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2115)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2040)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.GeneratedConstructorAccessor161.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
        at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310)
        at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203)
        at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:732)
        at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:664)
        at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:479)
        at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154)
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
        at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:246)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(