Spring Boot Atomikos多个数据源连接池耗尽了普通的JDBC [英] spring boot atomikos multiple datasource connection pool exhausted plain jdbc
本文介绍了Spring Boot Atomikos多个数据源连接池耗尽了普通的JDBC的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个使用Atomikos进行JTA托管事务的Spring Boot应用程序.它使用多个数据源来连接多个数据库.
I have a Spring Boot application that uses Atomikos for JTA Managed Transactions. It uses multiple DataSources to connect multiple Databases.
第一个请求返回了预期的结果，但是第二个请求失败，并显示内存耗尽"异常.
The first request returns the expected Result but the second request failes with a 'Pool exhausted' Exception.
我用纯JDBC和JdbcTemplate尝试了一下，但没有结果.一样.
I tried it with plain JDBC and JdbcTemplate with no result. It is the same.
这是我的Spring Boot版本1.5.8.RELEASE的代码
Here is my code with Spring Boot Version 1.5.8.RELEASE
@SpringBootApplication
@EnableAutoConfiguration(exclude = {
DataSourceAutoConfiguration.class,
HibernateJpaAutoConfiguration.class, //if you are using Hibernate
DataSourceTransactionManagerAutoConfiguration.class
})
@EnableTransactionManagement
public class App {
public static void main(String[] arg) {
SpringApplication.run(App.class, arg);
}
}
第一个方法'index()'用于纯JDBC.第二个"a()"与JdbcTemplate一起使用.
The first method 'index()' is with plain JDBC. The second 'a()' is with JdbcTemplate.
@RestController
@RequestMapping(value = "/demo")
@RequestScoped
public class Controller {
@Autowired
ApplicationContext ctx;
@RequestMapping("")
@org.springframework.transaction.annotation.Transactional(propagation = Propagation.REQUIRES_NEW)
public String index() throws SQLException {
DataSource imsdb = ctx.getBean("IMSDB", DataSource.class);
Connection icon = imsdb.getConnection();
DataSource sqldb = ctx.getBean("SQLDB", DataSource.class);
Connection con = sqldb.getConnection();
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement("select current date from sysibm.sysdummy1");
ParameterMetaData md = stmt.getParameterMetaData();
md.getParameterCount();
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rmd = rs.getMetaData();
rmd.getColumnName(1);
rs.next();
Date date = rs.getDate(1);
rs.close();
stmt.close();
PreparedStatement istmt = icon.prepareStatement("select current date from sysibm.sysdummy1");
ParameterMetaData imd = istmt.getParameterMetaData();
imd.getParameterCount();
ResultSet irs = istmt.executeQuery();
ResultSetMetaData irmd = irs.getMetaData();
irmd.getColumnName(1);
irs.next();
Date idate = irs.getDate(1);
irs.close();
istmt.close();
con.close();
icon.close();
return "dd";
}
@RequestMapping("/a")
@org.springframework.transaction.annotation.Transactional(transactionManager = "transactionManager")
public String a() throws SQLException {
DataSource imsdb = ctx.getBean("IMSDB", DataSource.class);
JdbcTemplate t1 = new JdbcTemplate(imsdb);
DataSource sqldb = ctx.getBean("SQLDB", DataSource.class);
JdbcTemplate t2 = new JdbcTemplate(sqldb);
SqlRowSet rs = t1.queryForRowSet("select current date from sysibm.sysdummy1");
rs.next();
Date date = rs.getDate(1);
SqlRowSet rs2 = t2.queryForRowSet("select current date from sysibm.sysdummy1");
rs2.next();
Date date2 = rs2.getDate(1);
return date.toLocalDate().format(DateTimeFormatter.ISO_DATE);
}
}
数据源配置:
@Configuration
@DependsOn("transactionManager")
public class DatabaseSqlConfig {
@Primary
@Bean(name = "SQLDB")
@ConfigurationProperties(prefix = "spring.jta.atomikos.datasource.sql")
public DataSource SQLDB() {
return new AtomikosDataSourceBean();
}
@Bean(name = "IMSDB")
@ConfigurationProperties(prefix = "spring.jta.atomikos.datasource.ims")
public DataSource IMSDB() {
return new AtomikosDataSourceBean();
}
}
交易管理器:
@Configuration
@ComponentScan
@EnableTransactionManagement
public class MainConfig {
@Bean(name = "userTransaction")
public UserTransaction userTransaction() throws Throwable {
UserTransactionImp userTransactionImp = new UserTransactionImp();
userTransactionImp.setTransactionTimeout(120);
return userTransactionImp;
}
@Bean(name = "atomikosTransactionManager", initMethod = "init", destroyMethod = "close")
public TransactionManager atomikosTransactionManager() throws Throwable {
UserTransactionManager userTransactionManager = new UserTransactionManager();
userTransactionManager.setForceShutdown(false);
userTransactionManager.setTransactionTimeout(120);
return userTransactionManager;
}
@Bean(name = "transactionManager")
@DependsOn({ "userTransaction", "atomikosTransactionManager" })
public JtaTransactionManager transactionManager() throws Throwable {
UserTransaction userTransaction = userTransaction();
TransactionManager atomikosTransactionManager = atomikosTransactionManager();
return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
}
}
应用yml:
spring:
jta:
atomikos:
datasource:
sql:
unique-resource-name: sql
xa-data-source-class-name: com.ibm.db2.jcc.DB2XADataSource
xa-properties:
serverName: 10.141.86.14
portNumber: 50000
databaseName: XXX
user: XXX
password: db2
driverType: 4
initQuery: set current sqlid = 'LVM'
testOnBorrow: true
validationQuery: select 1 from sysibm.sysdummy1
defaultAutoCommit: false
max-pool-size: 1
min-pool-size: 1
reap-timeout: 10
ims:
unique-resource-name: ims
xa-data-source-class-name: com.ibm.db2.jcc.DB2XADataSource
xa-properties:
serverName: 10.141.86.14
portNumber: 50000
databaseName: XXX
user: XXX
password: XXX
driverType: 4
initQuery: set current sqlid = 'XXX'
testOnBorrow: true
validationQuery: select 1 from sysibm.sysdummy1
defaultAutoCommit: false
max-pool-size: 1
min-pool-size: 1
reap-timeout: 10
和pom部门:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
</dependency>
<dependency>
<groupId>com.ibm.db2</groupId>
<artifactId>db2jcc4</artifactId>
</dependency>
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-web-api</artifactId>
<scope>provided</scope>
</dependency>
</dependencies>
这是日志:
第一个请求:
atomikos connection proxy for com.ibm.db2.jcc.am.sf@8ecb39e: calling createStatement...
2017-11-07 10:36:09.930 DEBUG 14473 --- [nio-8080-exec-1] c.atomikos.jdbc.AbstractDataSourceBean : AtomikosDataSoureBean 'sql': getConnection()...
2017-11-07 10:36:09.930 INFO 14473 --- [nio-8080-exec-1] c.atomikos.jdbc.AbstractDataSourceBean : AtomikosDataSoureBean 'sql': init...
2017-11-07 10:36:09.931 DEBUG 14473 --- [nio-8080-exec-1] c.a.icatch.imp.CompositeTransactionImp : addParticipant ( XAResourceTransaction: 3132372E302E312E312E746D313531303034373336393836313030303031:3132372E302E312E312E746D32 ) for transaction 127.0.1.1.tm151004736986100001
2017-11-07 10:36:09.931 DEBUG 14473 --- [nio-8080-exec-1] c.a.datasource.xa.XAResourceTransaction : XAResource.start ( 3132372E302E312E312E746D313531303034373336393836313030303031:3132372E302E312E312E746D32 , XAResource.TMNOFLAGS ) on resource sql represented by XAResource instance com.ibm.db2.jcc.t4.ec@7286dbb3
2017-11-07 10:36:09.931 DEBUG 14473 --- [nio-8080-exec-1] c.a.icatch.imp.CompositeTransactionImp : registerSynchronization ( com.atomikos.jdbc.AtomikosConnectionProxy$JdbcRequeueSynchronization@a9b32dad ) for transaction 127.0.1.1.tm151004736986100001
2017-11-07 10:36:09.931 DEBUG 14473 --- [nio-8080-exec-1] c.atomikos.jdbc.AtomikosConnectionProxy : atomikos connection proxy for com.ibm.db2.jcc.am.sf@5e7e3715: calling createStatement...
2017-11-07 10:36:09.934 DEBUG 14473 --- [nio-8080-exec-1] c.atomikos.jdbc.AtomikosConnectionProxy : atomikos connection proxy for com.ibm.db2.jcc.am.sf@8ecb39e: close()...
2017-11-07 10:36:09.935 DEBUG 14473 --- [nio-8080-exec-1] c.a.datasource.xa.XAResourceTransaction : XAResource.end ( 3132372E302E312E312E746D313531303034373336393836313030303031:3132372E302E312E312E746D31 , XAResource.TMSUCCESS ) on resource ims represented by XAResource instance com.ibm.db2.jcc.t4.ec@44c74226
2017-11-07 10:36:09.935 DEBUG 14473 --- [nio-8080-exec-1] c.atomikos.jdbc.AtomikosConnectionProxy : atomikos connection proxy for com.ibm.db2.jcc.am.sf@5e7e3715: close()...
2017-11-07 10:36:09.935 DEBUG 14473 --- [nio-8080-exec-1] c.a.datasource.xa.XAResourceTransaction : XAResource.end ( 3132372E302E312E312E746D313531303034373336393836313030303031:3132372E302E312E312E746D32 , XAResource.TMSUCCESS ) on resource sql represented by XAResource instance com.ibm.db2.jcc.t4.ec@7286dbb3
2017-11-07 10:36:09.936 DEBUG 14473 --- [nio-8080-exec-1] c.a.icatch.imp.CompositeTransactionImp : commit() done (by application) of transaction 127.0.1.1.tm151004736986100001
2017-11-07 10:36:09.938 DEBUG 14473 --- [nio-8080-exec-1] c.a.datasource.xa.XAResourceTransaction : XAResource.prepare ( 3132372E302E312E312E746D313531303034373336393836313030303031:3132372E302E312E312E746D31 ) returning XAResource.XA_RDONLY on resource ims represented by XAResource instance com.ibm.db2.jcc.t4.ec@44c74226
2017-11-07 10:36:09.938 DEBUG 14473 --- [nio-8080-exec-1] c.a.datasource.xa.XAResourceTransaction : XAResource.prepare ( 3132372E302E312E312E746D313531303034373336393836313030303031:3132372E302E312E312E746D32 ) returning XAResource.XA_RDONLY on resource sql represented by XAResource instance com.ibm.db2.jcc.t4.ec@7286dbb3
第二次请求，直到例外:
second request until exception:
2017-11-07 10:36:12.635 DEBUG 14473 --- [nio-8080-exec-2] c.a.i.i.CompositeTransactionManagerImp : createCompositeTransaction ( 10000 ): created new ROOT transaction with id 127.0.1.1.tm151004737263500002
2017-11-07 10:36:12.635 DEBUG 14473 --- [nio-8080-exec-2] c.atomikos.jdbc.AbstractDataSourceBean : AtomikosDataSoureBean 'ims': getConnection()...
2017-11-07 10:36:12.635 INFO 14473 --- [nio-8080-exec-2] c.atomikos.jdbc.AbstractDataSourceBean : AtomikosDataSoureBean 'ims': init...
2017-11-07 10:36:22.693 WARN 14473 --- [ Atomikos:4] c.a.icatch.imp.ActiveStateHandler : Timeout/setRollbackOnly of ACTIVE coordinator !
2017-11-07 10:36:42.637 WARN 14473 --- [nio-8080-exec-2] com.atomikos.jdbc.AtomikosSQLException : Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.
2017-11-07 10:36:42.639 DEBUG 14473 --- [nio-8080-exec-2] c.a.icatch.imp.CompositeTransactionImp : rollback() done of transaction 127.0.1.1.tm151004737263500002
2017-11-07 10:36:42.640 DEBUG 14473 --- [nio-8080-exec-2] c.a.icatch.imp.CompositeTransactionImp : rollback() done of transaction 127.0.1.1.tm151004737263500002
2017-11-07 10:36:42.649 ERROR 14473 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.atomikos.jdbc.AtomikosSQLException: Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.] with root cause
com.atomikos.jdbc.AtomikosSQLException: Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.
推荐答案
我自己找到了解决方案.
I found the solution myself.
Atomikos交易管理器仅存在一个错误-我切换到Bitronix，现在一切正常.
The Atomikos Transaction Manager simply has a bug - i switched to Bitronix and all works fine now.
这篇关于Spring Boot Atomikos多个数据源连接池耗尽了普通的JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
