Support per connection transaction affinity

Here is the thread where this was discussed:

http://www.nabble.com/Read-write-data-on-the-same-transaction-but-different-connections-to20558121.html#a20586753

I've started working on this feature and got a preliminary version working. I've uploaded it here:
http://snapshots.repository.codehaus.org/org/codehaus/btm/btm/1.3.3-20090207/btm-1.3.3-20090207.jar
you can enable the affinity by setting to true the new 'singleConnectionPerTransaction' datasource property.

Please note that the current implementation has a limitation that will prevent the test above from working: connections are enlisted in the transaction only when a statement is created. Since you create the two connections upfront in your example you'll get two different ones. If you create the 2nd connection after the prepareStatement() call on the 1st one then you get the same connection twice.

I'm trying to find a smart way to get rid of this limitation but so far the only way I've found is to pin the connection to the thread which is a bit ugly as it duplicates some of the TX management logic.

This is a very important feature for my next project, so I would be very happy to see it resolved - including fixing the limitation of the current patch.

Overcoming this limitation won't be easy as the pool will have to track connections without the help of the transactional context. I'm busy thinking about a solution that would not require a lot of boilerplate to implement and that takes time. Any suggestion is welcome.

Due to the recent spike in critical bugs this new feature is postponed to a later release.

Added new configuration property 'shareAccessibleConnections'. When this is set, an ACCESSIBLE connection within a global transaction will be shared to callers of getConnection(). The default is non-shared connections.

Note this fix does not address the specific example above, which is somewhat contrived, but will address most real-world use-cases. For example:

tx.begin();

Connection conn1 = ds.getConnection();
Connection conn2 = ds.getConnection();

tx.commit();

Will not result in conn1 and conn2 being the same physical connection to the database. However,

tx.begin();

Connection conn1 = ds.getConnection();
Statement stmt = conn1.createStatement(...);  // causes transaction enlistment

Connection conn2 = ds.getConnection();

tx.commit();

will result in conn1 and conn2 being the same physical connection. This is because the connection resource is not enlisted in the global transaction until a statement, or call, is created or prepared. After the connection resource is enlisted, further calls to getConnection() within the same global transaction will result in the same physical connection being returned – if shareAccessibleConnections is true. Most if not all real world scenarios are likely to fit the later example.

Attached patch and checked in code for second approach to connection sharing. All unit tests, including new connection sharing unit tests, are passing.

This approach also fixes the original use-case in this defect. In this approach, the XAPool maintains a mapping of current transaction ID's to XAStatefulHolder objects.

If shareTransactionConnections is true, when a connection is obtained from XAPool, the mapping will be checked for an existing XAStatefulHolder associated with the current transaction. If none is found then a new XAStatefulHolder is obtained from the pool and is added to the mapping for the transaction.

If there is no transaction, the mapping is not consulted and XAStatefulHolders are not put into the map.

The mapping is maintained until the transaction completes (either commit or rollback), and is deleted by a call to a transaction synchronization callback.

Ludovic,

Do you think we should make the association based not only on transaction, but also thread? Is it possible that a global transaction can span multiple threads? Or is a transaction always mapped to a single thread? If a transaction can span threads, then multiple threads would share the same Connection (and cached PreparedStatements), which is likely to break. If a transaction can't span threads, then there should be no problem.

Heh, talking to myself ... answering my own questions. I was able to create a testcase in which I create a transaction, suspend() it, pass it to two threads, which both resume() it and then obtain connections and create statements. So, I assume it is possible for a transaction to be running on multiple threads. As a result, I've updated the code (checked-in) to account for thread association. So, while a connection within a transaction will be shared, it will only be shared to callers on the same thread. This will avoid any issues with two threads interacting with the same cached PreparedStatement.

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

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.

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:

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.

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.

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.