"org.postgresql.util.PSQLException: Cannot change transaction isolation" occurs at calling java.sql.Connection#setTransactionIsolation when enabling connection validation in JBoss EAP 6

Solution Unverified - Updated

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP) 6

Issue

When enabling connection validation (<check-valid-connection-sql>select 1</check-valid-connection-sql> or <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>) in EAP 6 with PostgreSQL, the following "org.postgresql.util.PSQLException: Cannot change transaction isolation" was thrown at calling java.sql.Connection#setTransactionIsolation(): 

WARN  [org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory] (http-/127.0.0.1:8080-1) 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  ...(snip)...

Resolution

This issue has been reported in This content is not included.BZ#1195079. This has been addressed in This content is not included.Red Hat JBoss Enterprise Application Platform 6.4 Update 16 .

note
You can workaround this issue by adding commit to the validation query of <check-valid-connection-sql> (For example, specify "select 1; commit;" instead of "select 1").

Root Cause

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.