MySQL Bugs: #17408: XAConnection should report about the same RA for one server

Bug #17408	XAConnection should report about the same RA for one server
Submitted:	15 Feb 2006 2:07	Modified:	11 Nov 2009 2:36
Reporter:	Sergey Vladimirov	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.0-beta	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
XAConnection.getResource() == XAConnection should report that used (before) connection to the same server have have the same XAResourceAdapter

Otherwise such scenario is possible:
 - obtain XAConnection from pool
 - enlist in Transaction Manager (TM)
 - obtain Connection 
 - "close" connection
 - delist (with suspend) from TM
 - return XAConnection to pool

 - obtain XAConnection from pool
>>> - enlist in Transaction Manager (TM) with RESUME
 - obtain Connection 
 - "close" connection
 - delist (with suspend) from TM
 - return XAConnection to pool

The selected item is preffered, as it should be. But it is not possible, if ResourceAdapter returns FALSE from isSameRM(XAResource xares).

How to repeat:
Take one XAConnection, start/end
Take another XAConnection and try to enlist in TM

TM will start new branch, but he need to resume an old one.

Suggested fix:
Reimplement XAConnection & XAResource

Not sure what you're getting at here. Are you saying that the XAResource.isSameRM() should report "true" when the physical connection is to the same server?

We've tested the current implementation on BEA, and JBoss, and they _seem_ to only ever create one instance of the XAResource for a given global transaction, which is why the current implementation works in those cases. What application server(s) are you having issues with so we can test?

It would also be helpful if you are _very_ specific about what method calls you expect to work and how. There is no "getResource()" on XAConnection, so I assume you're talking about getXAResource(), and there is no XAResourceAdapter in the JTA API, so I assume you're talking about a concrete implementation of XAResource? I'd rather not guess at what you mean so we can get on the "same page" quicker :)

Ok, let me try to describe. I use JOTM as TM, and triyng to write my own ConnectionPool. I have a lot experience with debugging XA Pool and XAConnection of Derby database, and a lot of documentation about XA transaction near my hand (Including official standart, of course). Good reference will be "XA Exposed" by http://jroller.com/page/pyrasun?catname=%2FXA 

According to my vision of XA & database pooling, this scanario should be correct:

Roles:
- XADataSource by com.mysql.jdbc.jdbc2.optional.MysqlXADataSource or org.apache.derby.jdbc.EmbeddedXADataSource.
- ConnectionPool by mine class with methods:
 - - Connection borrowConnection()
 also extended ConnectionEventListener and PoolableObjectFactory
- TransactionManager & UserTransaction by org.objectweb.jotm.Current (JOTM)
- Component, just some component which have access to ConnectionPool

Scenerio. Part I. Borrow and return connection.

1. Container starts transaction by calling UserTransaction.begin()
2. Container calls some method of Component
3. Component borrows connection from ConnectionPool.
4. ConnectionPool take any free XAConnection from the pool and 
5. ConnectionPool calls transactionManager.getTransaction().enlistResource(xaConnection.getXAResource());
6. TransactionManager check if this XAResource was already delisted. Answer is NO.
7. TransactionManager calls XAResource.start (javaxxid, flag), where javaxxid is new created Xid and flag=XAResource.TMNOFLAGS
8. ConnectionPool return to user xaConnection.getConnection()
9. Component does some work with returned Connection
9. Component calls Connection.close()
10. Instead of closing physical connection Connection calls ConnectionPool.connectionClosed(ConnectionEvent)
11. ConnectionPool calls transactionManager.getTransaction().delistResource(xaConnection.getXAResource(), XAResource.TMSUCCESS);
12. TransactionManager calls XAResource.end(XAResource.TMSUCCESS)
13. ConnectionPool returns XAConnection to pool.

The transaction is still active :)

Part II.

14. Container calls another (or the same) method of Component
15. Component borrows connection from ConnectionPool.
16. ConnectionPool take any free XAConnection from the pool and 
17. ConnectionPool calls transactionManager.getTransaction().enlistResource(xaConnection.getXAResource());
18. TransactionManager check if this XAResource was already delisted. Answer should be YES. (YES in Derby, NO in MySQL)
19. TransactionManager calls XAResource.start (javaxxid, flag), where javaxxid is new created Xid and flag=XAResource.TMRESUME
20. ConnectionPool return to user xaConnection.getConnection()
21. Component does some work with returned Connection
22. Component calls Connection.close()
23. Instead of closing physical connection Connection calls ConnectionPool.connectionClosed(ConnectionEvent)
24. ConnectionPool calls transactionManager.getTransaction().delistResource(xaConnection.getXAResource(), XAResource.TMSUCCESS);
25. TransactionManager calls XAResource.end(XAResource.TMSUCCESS)
26. ConnectionPool returns XAConnection to pool.

Part III...
25. Container calls UserTransaction.commit()
26. TransactionManager calls XAResource.commit()

So, the problem with MySQL XADataSource occures at 18th step. IMHO, MySQL should use one XAResource for any connection to one server.

The issue is that MySQL doesn't allow a client to issue XA commands for a given global transaction on any connection, only on the physical connection that started the global transaction (except for recovery, of course).

Most appservers and JTA transaction managers I'm familiar with (BEA, Weblogic, WebSphere) take this into account, as it's a common restriction with quite a few resource managers, and have ways of being configured to maintain a one-to-one relationship between the XAResource/Connection and a global transaction so that the same physical XAResource is always used until the global transaction ends and has been committed or rolled back.

Does JOTM not have this capability?

it should be not the feature of TransactionManager, but ConnectionPool - to store connection in special place for transaction until it is commit or roollback. But it is mean that connection pool need to know very much about transactions (more code, more mistakes)

And such strategy called hack, not feature, IMHO.

Since Xid passed to XAResource.start() and XAResource.end() is not equals to transaction.getXid(), so i need to extract them from XAResource.

It means that I will need to create Wrapper for XAConnection and for XAResource, and, may be for Connection also.

But XAConnection inteface (extends PooledConnection) is already designed to do it simply, if assume, that XAConnection is not assotiated one-by-one with physical connection, but switches them during start() and end() methods.

If what you're looking for is the ability to dispatch any XA control method (start, end, prepare, etc.) on an XAResource that is where .isSameRM() holds true, then there's nothing the JDBC driver can do about that, it's a current limitation of the server.

It also happens to be a limitation of Oracle and DB2 (I'm sure they're more vendors with the same limitation), which is why most application server TMs have features like BEAs "KeepXAConnTillTxComplete="true" and JBoss's "track-connection-by-tx" configuration properties (Websphere seems to have this behavior intrinsically)  for their XA connection pools.