BTM
  1. BTM
  2. BTM-35

Support per connection transaction affinity

    Details

    • Type: Improvement Improvement
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.3.2
    • Fix Version/s: 2.0.0
    • Labels:
      None
    • Testcase included:
      yes
    • Number of attachments :
      2

      Description

      Situations such as the below test case currently don't work with BTM:

      UserTransaction utx = TransactionManagerServices.getTransactionManager();
      utx.begin();

      DataSource ds = ... ; // Get an istance of bitronix.tm.resource.jdbc.PoolingDataSource here

      Connection connection = ds.getConnection();
      Connection connection2 = ds.getConnection();

      PreparedStatement stmt1 = null, stmt2 = null;
      ResultSet rs = null;
      try {
      stmt1 = connection.prepareStatement("INSERT INTO TEST_TABLE (ID, DESCR) VALUES (?, ?)");
      stmt1.setInt(1, 1);
      stmt1.setString(2, "test");
      stmt1.execute();

      stmt2 = connection2.prepareStatement("SELECT * FROM TEST_TABLE WHERE ID=?");
      stmt2.setInt(1, 1);
      rs = stmt2.executeQuery();
      while (rs.next())

      { System.out.println(rs.getInt(1) + " -- " + rs.getString(2)); }

      } catch (Exception e)

      { System.out.println(e); }

      finally

      { rs.close(); stmt1.close(); stmt2.close(); connection.close(); connection2.close(); }

      utx.commit();

      This may not be a problem when using XA transactions from some vendors but is a problem with postgresql and in the nonXA use cases. If BTM's pool could track connections requested from the pool and return the same connection for every ds.getConnection() in the same transaction then situations such as this would work in the PostgreSQL and the nonXA cases.

      There may be more work involved such as close suppression of various JDBC interfaces if that is not done already so that the connection is not returned to the pool until the transaction is committed.

      Mike

      1. patch.txt
        9 kB
        Brett Wooldridge
      2. patch3.txt
        21 kB
        Brett Wooldridge

        Activity

        Hide
        Brett Wooldridge added a comment -

        Marking fixed. Please re-open if this solution is not acceptable.

        Show
        Brett Wooldridge added a comment - Marking fixed. Please re-open if this solution is not acceptable.
        Hide
        Ludovic Orban added a comment -

        This unfortunately is yet another failed attempt:

        Brett Wooldridge
        A connection that was shared was allowed to be recycled back to IN_POOL, which is problematic because it might still be in use.

        Let's hold down any further development on this feature before we have a clear picture on how to do it.

        Show
        Ludovic Orban added a comment - This unfortunately is yet another failed attempt: Brett Wooldridge A connection that was shared was allowed to be recycled back to IN_POOL, which is problematic because it might still be in use. Let's hold down any further development on this feature before we have a clear picture on how to do it.
        Hide
        Brett Wooldridge added a comment -

        I have attached a new patch (patch3.txt). This patch implements usage counting on JdbcPooledConnection. I believe the result is both concise and correct. The changes are extremely minimal, and I believe contain no "surprises" for the user in terms of behavior. It satisfies the BTM-35 use case.

        Connections within the same transaction are shared (provided that sharing is enabled). A usage counter prevents shared connections from being recycled prematurely. Outside of a transaction, connections are not shared. To share connections in a local transaction context would break user expectations, as in the following example:

        Example.java
        Connection conn1 = ds.getConnection();
        Connection conn2 = ds.getConnection();
        
        Statement stmt = conn1.createStatement("...");
        stmt.executeUpdate();
        
        Statement stmt2 = conn2.createStatement("...);
        stmt2.executeUpdate();
        
        conn1.commit();
        conn2.rollback();
        

        Sharing connections in this case would result in stmt2 being committed, which is incorrect.

        All unit tests are passing, including the new shared connection unit tests.

        Show
        Brett Wooldridge added a comment - I have attached a new patch (patch3.txt). This patch implements usage counting on JdbcPooledConnection. I believe the result is both concise and correct. The changes are extremely minimal, and I believe contain no "surprises" for the user in terms of behavior. It satisfies the BTM-35 use case. Connections within the same transaction are shared (provided that sharing is enabled). A usage counter prevents shared connections from being recycled prematurely. Outside of a transaction, connections are not shared. To share connections in a local transaction context would break user expectations, as in the following example: Example.java Connection conn1 = ds.getConnection(); Connection conn2 = ds.getConnection(); Statement stmt = conn1.createStatement( "..." ); stmt.executeUpdate(); Statement stmt2 = conn2.createStatement("...); stmt2.executeUpdate(); conn1.commit(); conn2.rollback(); Sharing connections in this case would result in stmt2 being committed, which is incorrect. All unit tests are passing, including the new shared connection unit tests.
        Hide
        Brett Wooldridge added a comment -

        Ludovic,

        I updated patch3.txt with some minor changes. I removed two 'if' statements that always evaluate to true (what was I thinking?), fix a few cosmetic formatting issues, and improved the inline documentation quite a bit.

        It should diff cleanly to the head.

        Show
        Brett Wooldridge added a comment - Ludovic, I updated patch3.txt with some minor changes. I removed two 'if' statements that always evaluate to true (what was I thinking?), fix a few cosmetic formatting issues, and improved the inline documentation quite a bit. It should diff cleanly to the head.
        Hide
        Brett Wooldridge added a comment -

        I am marking this resolved/fixed. The code for this enhancement is checked in and has been tested in production for several weeks without issue.

        Show
        Brett Wooldridge added a comment - I am marking this resolved/fixed. The code for this enhancement is checked in and has been tested in production for several weeks without issue.

          People

          • Assignee:
            Ludovic Orban
            Reporter:
            Mike Youngstrom
          • Votes:
            3 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: