Uploaded image for project: 'WildFly'
  1. WildFly
  2. WFLY-8032

CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (PostgreSQL)

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 11.0.0.Beta1
    • 10.1.0.Final
    • JCA
    • None
    • Hide
      • 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)
...
      Show
      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) ...

      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:

      1. 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.
      2. 1st app receives the connection, sets autocommit=false, perform some work and commits a transaction.
      3. 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.
      4. 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.
      5. 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.

              thofman Tomas Hofman
              thofman Tomas Hofman
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: