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

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

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • 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) ...

    Description

      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.

      Attachments

        Issue Links

          clones

          Bug - A problem that impairs or prevents the functions of the product. JBJCA-1338 CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (PostgreSQL)

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Resolved
          is cloned by

          Bug - A problem that impairs or prevents the functions of the product. JBEAP-8694 CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (PostgreSQL)

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Verified

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: