Bug 1195079 - [GSS](6.4.z) "org.postgresql.util.PSQLException: Cannot change transaction isolation" at calling Connection#setTransactionIsolation() when enabling connection validation in EAP 6
Summary: [GSS](6.4.z) "org.postgresql.util.PSQLException: Cannot change transaction is...
Alias: None
Product: JBoss Enterprise Application Platform 6
Classification: JBoss
Component: JCA
Version: 6.1.0,6.3.3
Hardware: Unspecified
OS: Unspecified
Target Milestone: CR1
: EAP 6.4.16
Assignee: Tomas Hofman
QA Contact: Jiří Bílek
Depends On:
Blocks: eap6416-payload 1440733
TreeView+ depends on / blocked
Reported: 2015-02-22 20:48 UTC by Masafumi Miura
Modified: 2019-05-20 11:32 UTC (History)
12 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2017-06-22 09:23:07 UTC
Type: Bug

Attachments (Terms of Use)

System ID Private Priority Status Summary Last Updated
Red Hat Issue Tracker JBEAP-8694 0 Major Verified CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (Postgr... 2020-01-23 16:45:15 UTC
Red Hat Issue Tracker JBEAP-8789 0 Major Verified (7.0.z) JBJCA-1338 - CheckValidConnectionSQL can open a transaction, preventing application from changing transaction is... 2020-01-23 16:45:15 UTC
Red Hat Issue Tracker JBJCA-1338 0 Major Resolved CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (Postgr... 2020-01-23 16:45:15 UTC
Red Hat Issue Tracker WFLY-8032 0 Major Closed CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (Postgr... 2020-01-23 16:45:15 UTC
Red Hat Knowledge Base (Solution) 1218443 0 None None None 2017-06-18 02:14:12 UTC

Description Masafumi Miura 2015-02-22 20:48:33 UTC
Created attachment 994295 [details]
test.jsp (reproducer)

### Description of problem:

Getting the following "org.postgresql.util.PSQLException: Cannot change transaction isolation" at calling java.sql.Connection#setTransactionIsolation() when enabling connection validation for data source in EAP 6:

WARN  [org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory] (http-/ Error resetting transaction isolation : org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
    at org.postgresql.jdbc2.AbstractJdbc2Connection.setTransactionIsolation(AbstractJdbc2Connection.java:929)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.cleanup(BaseWrapperManagedConnection.java:357) [ironjacamar-jdbc-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.returnConnection(SemaphoreArrayListManagedConnectionPool.java:479)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.returnConnection(AbstractPool.java:586)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.returnManagedConnection(AbstractConnectionManager.java:452)
    at org.jboss.jca.core.connectionmanager.listener.NoTxConnectionListener.connectionClosed(NoTxConnectionListener.java:90)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.closeHandle(BaseWrapperManagedConnection.java:584) [ironjacamar-jdbc-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
    at org.jboss.jca.adapters.jdbc.WrappedConnection.close(WrappedConnection.java:265) [ironjacamar-jdbc-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
    at org.apache.jsp.test_jsp._jspService(test_jsp.java:116)

### How reproducible:

Anytime when using PostgreSQL doing the following steps to reproduce.

### Steps to Reproduce:

1. Configure the following data source in EAP 6 standalone.xml

                <datasource jndi-name="java:jboss/PostgresDS" pool-name="PostgresDS" enabled="true">
                        <check-valid-connection-sql>select 1</check-valid-connection-sql>
                        <!-- <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/> -->
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>

2. Access to the attached test.jsp **twice**. The first request will succeed without any exception but the second one will fail with the exception.

### Actual results:

Connection#setTransactionIsolation() failed with "org.postgresql.util.PSQLException: Cannot change transaction isolation"

### Expected results:

Connection#setTransactionIsolation() succeed wihtout "org.postgresql.util.PSQLException: Cannot change transaction isolation"

### Additional info:

 - Setting min-pool-size/max-pool-size to 1 was used here for ease to reproduce this issue.
 - Same issue occurred with <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/> instead of using <check-valid-connection-sql>.
 - This exception did not occur when connection validation is not enabled.
 - This issue did not occur with Oracle and MySQL
   (I guess this different result is just caused by the implementation difference between JDBC driver. As noted in  https://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#setTransactionIsolation(int), it says "If this method is called during a transaction, the result is implementation-defined.")
 - The application does not use JTA transaction but just use JDBC transaction.
 - This issue occurred regardless of the "jta" attribute configuration (same issue occurred with jta="true" (default) and jta="false".)
 - This issue is originally reported in EAP 6.1.0 but same issue is reproducible with the latest EAP 6.3.3

Comment 1 Jesper Pedersen 2015-02-25 06:31:29 UTC
Works for me with postgresql-9.4-1200.jdbc41.jar

Comment 2 Masafumi Miura 2015-02-25 13:49:47 UTC
Did you test with the actual same JSP (test.jsp) which I attached? I guess you did some modification...

I tested with the following latest jdbc drivers, which are available from <https://jdbc.postgresql.org/download.html>, and I could reproduce the issue with postgresql-9.4-1200.jdbc41.jar and other jdbc drivers in my environment (EAP 6.3.3 + PostgreSQL 9.3.6 database server on Fedora 20):

 - postgresql-9.2-1004.jdbc4.jar
 - postgresql-9.2-1004.jdbc41.jar
 - postgresql-9.3-1103.jdbc4.jar
 - postgresql-9.3-1103.jdbc41.jar
 - postgresql-9.4-1200.jdbc4.jar
 - postgresql-9.4-1200.jdbc41.jar

By the way, please note that https://access.redhat.com/articles/111663 says EAP 6 is actually tested with PostgreSQL 9.2 and JDBC4 Postgresql Driver. (I think this issue is not related to the version of PostgreSQL, though.)

Comment 3 Masafumi Miura 2015-02-25 13:51:49 UTC
By the way, I found a workaround for this issue by coincidence. 

I found that this issue did not occur when java.sql.Connection#getMetaData() was called (like the following) before calling db.setAutoCommit(false) at the line 25 in the attached test.jsp.

     DatabaseMetaData dbmd = db.getMetaData();
     System.out.println("=====  Database info =====");
     System.out.println("DatabaseProductName: " + dbmd.getDatabaseProductName() );
     System.out.println("DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() );
     System.out.println("DatabaseMajorVersion: " + dbmd.getDatabaseMajorVersion() );
     System.out.println("DatabaseMinorVersion: " + dbmd.getDatabaseMinorVersion() );
     System.out.println("=====  Driver info =====");
     System.out.println("DriverName: " + dbmd.getDriverName() );
     System.out.println("DriverVersion: " + dbmd.getDriverVersion() );
     System.out.println("DriverMajorVersion: " + dbmd.getDriverMajorVersion() );
     System.out.println("DriverMinorVersion: " + dbmd.getDriverMinorVersion() );

I just intended to print the version of Database and JDBC driver but it works like a charm.

This java.sql.Connection#getMetaData() is internally org.jboss.jcaadapters.jdbc.WrappedConnection() in ironjacamar and it calls "checkTransaction()". I thinks this method-call propagates auto-commit mode to underlyingAutoCommit then it ends JDBC tx as auto-commit mode is true at that time, so I think this is why it works like a charm.

Comment 4 Tomas Hofman 2015-02-27 09:37:05 UTC
Reproduced with postgresql jdbc driver 9.3.6 and ironjacamar 1.0.31.Final.

The problem is that jdbc connection is left in autocommit=false mode from the previous request, in spite of application code setting autocommit back to true on WrappedConnection. It should be set back to true during cleanup phase anyway, but this is never propagated to jdbc connection, since autocommit is propagated only during prepareStatement() call.

Because of that, when the application server is verifying jdbc connection for current request (by executing "select 1"), new transaction is started and calling setTransactionIsolation() later fails.

Solution would be for JCA to propagate autocommit state to jdbc connection during cleanup phase.

Comment 5 Tomas Hofman 2015-03-03 10:46:47 UTC
Created discussion here https://developer.jboss.org/message/920378

Comment 6 Masafumi Miura 2015-03-05 05:27:08 UTC
We've found another workaround which does not need code changes in their application.

Setting "select 1;commit;" instead of "select 1" to <check-valid-connection-sql> does work. For example:

   <check-valid-connection-sql>select 1; commit;</check-valid-connection-sql>

Comment 12 Tomas Hofman 2017-02-13 11:29:11 UTC
PR: https://github.com/ironjacamar/ironjacamar/pull/605

Comment 13 Tomas Hofman 2017-02-13 11:30:45 UTC
Upstream Issue: https://issues.jboss.org/browse/JBEAP-8789
Upstream PR: https://github.com/ironjacamar/ironjacamar/pull/604

Comment 17 Jiří Bílek 2017-06-09 13:00:13 UTC
Verified with EAP 6.4.16.CP.CR1

Comment 18 Petr Penicka 2017-06-22 09:23:07 UTC
Released on June 20 2017 as part of the EAP 6.4.16 maintenance release.

Note You need to log in before you can comment on or make changes to this bug.