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.
I tried it with plain JDBC and JdbcTemplate with no result. It is the same.
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);
}
}
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);
}
}
DataSource configuration:
@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();
}
}
Transaction Manager:
@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);
}
}
application 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
and the pom deps:
<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>
Here is the Log:
First request:
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.
The Atomikos Transaction Manager simply has a bug - i switched to Bitronix and all works fine now.