Create a PostgreSQL DS with check-valid-connection-sql="select 1": <datasource jta= " false " jndi-name= "java:jboss/datasources/PostgresDS" pool-name= "PostgresDS" enabled= " true " use-java-context= " true " > <connection-url>jdbc:postgresql: //localhost:5432/postgres</connection-url> <driver>postgres</driver> <pool> <min-pool-size>1</min-pool-size> <max-pool-size>1</max-pool-size> <prefill> true </prefill> <use-strict-min> true </use-strict-min> </pool> <security> <user-name>postgres</user-name> <password>postgres</password> </security> <validation> <check-valid-connection-sql>select 1</check-valid-connection-sql> <validate-on-match> true </validate-on-match> </validation> </datasource> deploy an application with a servlet containing: Connection connection = dataSource.getConnection(); connection.setAutoCommit( false ); // connection.rollback(); // <- this would be a workaround to close possible transaction connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // <- here the code fails PreparedStatement preparedStatement = connection.prepareStatement( "select 1" ); preparedStatement.close(); connection.commit(); connection.setAutoCommit( true ); connection.close(); access the servlet twice, the first attempt succeeds, the second fails with: javax.servlet.ServletException: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction. org.jboss.as.quickstarts.helloworld.HelloWorldServlet.doGet(HelloWorldServlet.java:79) javax.servlet.http.HttpServlet.service(HttpServlet.java:687) javax.servlet.http.HttpServlet.service(HttpServlet.java:790) io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85) ...
- Create a PostgreSQL DS with check-valid-connection-sql="select 1":
- deploy an application with a servlet containing:
- access the servlet twice, the first attempt succeeds, the second fails with:
PostgreSQL driver only allows changing the transaction isolation level when transaction is not opened. Under certain circumstances, an application can receive a connection with already opened transaction and an attempt to change transaction isolation level will lead to exception.
This happens with the PostgreSQL driver and with CheckValidConnectionSQL checker configured to run a select statement to verify connections retrieved from the pool.
The scenario is as follows:
- A connection is retrieved from the pool for the 1st app and CheckValidConnectionSQL verifies it by running a select statement (autocommit is set to true by default). This statement is run directly via the jdbc connection, not the wrapper.
- 1st app receives the connection, sets autocommit=false, perform some work and commits a transaction.
- The connection is returned to the pool, cleanup() method is called on LocalManagedConnection wrapper, which sets autocommit=true. This however doesn't reset autocommit on the wrapped jdbc connection yet, which would only happen just before executing another SQL statement f.i.
- The same connection is retrieved from the pool for the 2nd app and CheckValidConnectionSQL runs the query. Because the jdbc connection has still autocommit=false, new transaction is opened.
- 2nd app receives the connection and calls setTransactionIsolation(), which throws an exception because the transaction is open.
Possible solution could be that the cleanup() method propagates the autocommit=true to the wrapped jdbc connection immediately.