Unable to reuse a PostgreSQL connection in a transaction when specifying a default transaction isolation level
capgen628 opened this issue · 0 comments
capgen628 commented
Describe the bug
When using a default transaction isolation level on AtomikosDataSource with an underlying PGXADataSource, it is not possible to take a connection from the data source twice in a transaction.
To Reproduce
The following test case demonstrates the issue
private AtomikosDataSourceBean atomikosDataSource;
private UserTransaction atomikosTransactionManager;
@Before
public void setUp() throws Exception {
XADataSource dataSource = (XADataSource)Class.forName("org.postgresql.xa.PGXADataSource").newInstance();
dataSource.getClass().getMethod("setURL", String.class).invoke(dataSource, "jdbc:postgresql://localhost:5432/postgres");
dataSource.getClass().getMethod("setUser", String.class).invoke(dataSource, "postgres");
dataSource.getClass().getMethod("setPassword", String.class).invoke(dataSource, "manager");
final UserTransactionManager atomikosTransactionManager = new UserTransactionManager();
atomikosTransactionManager.init();
this.atomikosTransactionManager = atomikosTransactionManager;
atomikosDataSource = new AtomikosDataSourceBean();
atomikosDataSource.setMaxIdleTime(10*60);
atomikosDataSource.setMinPoolSize(0);
atomikosDataSource.setMaxPoolSize(5);
atomikosDataSource.setUniqueResourceName("Test Data Source");
atomikosDataSource.setXaDataSource(dataSource);
atomikosDataSource.setDefaultIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
}
@After
public void tearDown() {
atomikosDataSource.close();
}
@Test
public void testConnectionReuse() throws Exception {
atomikosTransactionManager.begin();
try {
try (Connection connection = atomikosDataSource.getConnection()) {
try (PreparedStatement statement = connection.prepareStatement("SELECT 1")) {
statement.executeQuery();
}
}
try (Connection connection = atomikosDataSource.getConnection()) {
try (PreparedStatement statement = connection.prepareStatement("SELECT 1")) {
statement.executeQuery();
}
}
} finally {
atomikosTransactionManager.rollback();
}
}
This results in the following warning and the then exception that fails the test
13:15:09,416 WARN [com.atomikos.jdbc.internal.JdbcConnectionProxyHelper] (main) cannot set isolation level to 2
org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:928)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.postgresql.ds.PGPooledConnection$ConnectionHandler.invoke(PGPooledConnection.java:337)
at com.sun.proxy.$Proxy8.setTransactionIsolation(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.postgresql.xa.PGXAConnection$ConnectionHandler.invoke(PGXAConnection.java:148)
at com.sun.proxy.$Proxy8.setTransactionIsolation(Unknown Source)
at com.atomikos.jdbc.internal.JdbcConnectionProxyHelper.setIsolationLevel(JdbcConnectionProxyHelper.java:31)
at com.atomikos.jdbc.internal.AtomikosXAPooledConnection.doCreateConnectionProxy(AtomikosXAPooledConnection.java:92)
at com.atomikos.jdbc.internal.AtomikosXAPooledConnection.doCreateConnectionProxy(AtomikosXAPooledConnection.java:30)
at com.atomikos.datasource.pool.AbstractXPooledConnection.createConnectionProxy(AbstractXPooledConnection.java:74)
at com.atomikos.datasource.pool.ConnectionPoolWithConcurrentValidation.concurrentlyTryToRecycle(ConnectionPoolWithConcurrentValidation.java:50)
at com.atomikos.datasource.pool.ConnectionPoolWithConcurrentValidation.recycleConnectionIfPossible(ConnectionPoolWithConcurrentValidation.java:31)
at com.atomikos.datasource.pool.ConnectionPool.findExistingOpenConnectionForCallingThread(ConnectionPool.java:144)
at com.atomikos.datasource.pool.ConnectionPool.borrowConnection(ConnectionPool.java:111)
at com.atomikos.jdbc.internal.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:349)
at TestPostgresql.testConnectionReuse(TestPostgresql.java:60)
com.atomikos.datasource.ResourceException: XA resource 'Test Data Source': resume for XID 'XID: 31302E34342E32302E3134342E746D313634383231343130383934363030303031:31302E34342E32302E3134342E746D31' raised -3: the XA resource detected an internal error
at com.atomikos.datasource.xa.XAResourceTransaction.resume(XAResourceTransaction.java:225)
at com.atomikos.datasource.xa.session.BranchEnlistedStateHandler.<init>(BranchEnlistedStateHandler.java:40)
at com.atomikos.datasource.xa.session.NotInBranchStateHandler.checkEnlistBeforeUse(NotInBranchStateHandler.java:46)
at com.atomikos.datasource.xa.session.TransactionContext.checkEnlistBeforeUse(TransactionContext.java:58)
at com.atomikos.datasource.xa.session.SessionHandleState.notifyBeforeUse(SessionHandleState.java:163)
at com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy.enlist(AtomikosJdbcConnectionProxy.java:88)
at com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy.updateTransactionContext(AtomikosJdbcConnectionProxy.java:61)
at com.atomikos.jdbc.internal.AbstractJdbcConnectionProxy.prepareStatement(AbstractJdbcConnectionProxy.java:64)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.atomikos.util.DynamicProxySupport.callProxiedMethod(DynamicProxySupport.java:159)
at com.atomikos.util.DynamicProxySupport.invoke(DynamicProxySupport.java:116)
at com.sun.proxy.$Proxy10.prepareStatement(Unknown Source)
at TestPostgresql.testConnectionReuse(TestPostgresql.java:61)
...
Caused by: org.postgresql.xa.PGXAException: Invalid protocol state requested. Attempted transaction interleaving is not supported. xid=XID: 31302E34342E32302E3134342E746D313634383231343130383934363030303031:31302E34342E32302E3134342E746D31, currentXid=null, state=IDLE, flags=2,097,152
at org.postgresql.xa.PGXAConnection.start(PGXAConnection.java:210)
at com.atomikos.datasource.xa.XAResourceTransaction.resume(XAResourceTransaction.java:219)
... 47 more
Additional context
This can be avoided with a simple check in JdbcConnectionProxyHelper to avoid setting the isolation level if it is already correct.