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
Category:	Connector/J	Severity:	S2 (Serious)
Version:	5.0.0-beta	OS:	Microsoft Windows (Windows XP)
Assigned to:		Target Version:

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.