Removing DB2 in-doubt transactions when transaction manager fails

[msanda]

Hi,

I've been doing some testing using WebSphere MQ as a TM to coordinate MQ and DB2/Windows resources, seeing we already have both products in-house.  We're comfortable with commit and rollback testing.  However, if I set a breakpoint on the MQ commit statement, then kill the transaction manager (MQ processes) without invoking the commit, DB2 is still left with an in-doubt transaction from the update that was never committed.

I've worked with IBM on this and they've assured us that that's normal behavior and that even after bringing the queue manager back up, it's not its responsibility to tell DB2 to remove in-doubt transactions.  MQ does nicely remove the message that was put to a queue though; just not the DB2 row that was inserted (or the DB2 lock).

So a colleague recommended we check out JBoss.  But before we launch a whole new R&D task to do so, I'm hoping someone can tell me how JBoss would react to this.  Please understand that I'm not referring to "in the middle of the two-phased commit, AFTER a commit was issued, but before the entire 2PC was completed".  Rather, the commit was never issued, but resources (MQ PUT and DB2 INSERT) were updated as part of the transaction.

Thank you.

[msanda]

Continuing my search for answers... I found this:

https://community.jboss.org/thread/145369

Relevant?

[wdfink]

As I understand it correct,

the XA-PREPARE is done and the XA-COMMIT not and in this situation you kill the process.

Looks for me simmilar to another issue.

So it must ensured that the transaction is closed, if not there is a recovery mechanism to do that. Not all implementations have such.

I have had this with Oracle.

So in that case you have such in-doubt Tx and you have to cleanup by hand.

In my understanding this is not related to the applicationserver but to the DB (JCA) resource/driver

[msanda]

Not exactly my scenario.  As I understand, when my code tells MQ to "commit", only then does MQ issue prepare, then later commit, statements to resource managers.  In my case, I kill MQ while stopped at a breakpoint on the MQ commit statement.  Therefore, as I understand it, MQ never even issued the prepare, correct? 

I wouldn't have expected this to even cause a DB2 in-doubt transaction because what's the doubt?  No 2PC was ever begun!   I would've hoped that DB2 would detect that its transaction from this trans mgr lost a connection and will never be asked to commit or rollback, and therefore DB2 would've just released the lock and rolled back.

BTW, here is some more [outdated?] info on "presumed abort": http://www.devwebsphere.com/devwebsphere/2003/09/presumed_abort_.html

[wdfink]

As I said before, and in the article, the transaction is still enlisted and there is no commit/rollback.

Maybe the connection is still open because it will be often a pooled connection.

So In JCA there is a recovery mechanism for XA, but it depends on the implementation and driver whether this will supported.