DB2 XA in doubt state not recovered

[yujianming]

Hello,

I am using Jboss Two Phase commit by setup XA data source for DB2 (version 9) and Oracle database (9i) in our application. But the issue is when the transaction fails in the middle, I check the DB2 database the transaction is still in doubt state. Jboss did not rollback the in-doubt transaction automatically. Would you please give me a help to setup the Jboss 4.2.1 to rollback those indoubt transaction when fail over automatically?

Thanks,

Best regards,

James

<xa-datasource>
<jndi-name>sm</jndi-name>
<xa-datasource-class>
com.ibm.db2.jcc.DB2XADataSource
</xa-datasource-class>
<xa-datasource-property name="DatabaseName">
sm1
</xa-datasource-property>
<xa-datasource-property name="User">
user
</xa-datasource-property>
<xa-datasource-property name="Password">
password
</xa-datasource-property>
<min-pool-size>10</min-pool-size>
<max-pool-size>100</max-pool-size>

<type-mapping>DB2</type-mapping>

</xa-datasource>

[jhalliday]

> Would you please give me a help to setup the Jboss 4.2.1 to rollback those indoubt transaction when fail over automatically?

What do you mean by 'fail over automatically'? Are you running JBoss in some kind of cluster configuration? If so, please specify exactly how you have it configured.

[yujianming]

Thanks for the reply.

I am not using the clustering. Only one server is running and the transaction is stopped in the middle. Does Jboss should rollback or commit all the transactions that is in doubt state on database?

Thanks,

Best regards,

James

[jhalliday]

Can you please clarify exactly what you are doing to cause the issue?

> Only one server is running and the transaction is stopped in the middle.

How exactly do you achieve that?

[yujianming]

I installed DB2 Client, and using XA in the application. And the transaction met exception in the middle of a transaction, so the transaction rolled back, actually this transaction is only read data from the db2 database. But the DB2 still say that transaction is still in doubt state. I have another Oracle database using XA. BUt in this transaction, Oracle database tables is not being used.
Thanks,

Best regards,

James

[jhalliday]

> the transaction met exception in the middle of a transaction, so the transaction rolled back,

So the app server considers the transaction to have completed, but the database does not? That's a bug then. Send me a test case to reproduce it and I'll take a look.

[yujianming]

In our application, we sending request to another server using multi-threading, but the remote server did not response quick enough and pending. Then we shutdown our jboss. When we restart the jboss, the transaction is still in the DB2 database.

Thanks,

[jhalliday]

Assuming the prepare has not yet been sent when you shut down JBoss, the DB2 server should presume abort for the tx when the TCP/IP connection drops. It's not in doubt unless the prepare has been sent already.

[yujianming]

Yes. This is the problem. When I check the indoubt transactions on DB2, by using Db2 list indoubt transactions with prompting, I can always see the transaction since the transacton start. Is it should be there only after the prepare has been send?

Thanks,
Best regards,

james

[jhalliday]

There is a critical difference between 'presumed abort' and 'in doubt' for a transaction. Up until the transaction manager (JBossAS) asks the resource manager (DB2) to prepare, the transaction is presumed abort i.e. if anything goes wrong, the tx should rollback. DB2 should thus be aborting the tx when the app server closes its connection to the db during shutdown/crash. The transaction manager assumes this and does no cleanup. By contrast, after the prepare is done, DB2 can't make an independent decision on how to resolve the transaction, it has to wait for instruction from the app server. Thus the tx is 'in doubt' until the app server issues its decision. It sounds like DB2 is not implementing presumed abort, but instead considering an XA tx to be in doubt from the time it is started rather than the time it is prepared. If that is the case, the different assumptions made by our tx manager and DB2 make them incompatible.

[yujianming]

Thanks.

But is there anyone less met the same problem for DB2 v9? Or is there some DB2 setup can make it works as Jboss expected?

Thanks,

James

[jhalliday]

I don't know about configuring DB2, but it is possible to work around such a mismatch by implementing a custom recovery module for JBossTS that will detect and rollback these tx. That would cause the transaction manager, when it restarts, to ask the DB2 server for a list of all in-doubt tx and rollback those it does not know about (it has no permanent record of the tx until the prepare is done, as under presumed abort it's not needed before then) However, check the DB2 docs first - presumed abort is near ubiquitous and I'm very surprised DB2 does not use it. For information on implementing recovery modules, see the JBossTS crash recovery docs.

[jhalliday]

Hmm, my brain has not yet fully recovered from the vacation... Come to think of it, you need the recovery module anyhow. There is a window of time between the DB returning 'prepared' and the transaction manager writing its commit decision to disk. If JBoss crashes in that interval, recovery code is needed to resolve the in-doubt tx. As a side effect it would also resolve transactions that should be presumed-abort, albeit in a less timely manner than if the DB server did it when the connection dropped. I'll get it implemented in a future release, or you could contribute your implementation if you get there first.

[yujianming]

May I know if my DB2 XA setup on JBoss is correct? I installed DB2 client and using the DB2 client account to run jboss.

Thanks,

James

[marklittle]

DB2 supports presumed abort and (depending on the version) presumed nothing.