Help with JBossTS and Oracle: XAER_NOTA

[mauromol]

Hello!
We are using JBossTS embedded into a webapp application, where the transaction manager is managed by Spring.
We are accessing two different databases. If these two databases are on a PostgreSQL or SQLServer the whole thing works perfectly. With Oracle, however, we have a problem.

First of all, our two database resources are configured in this way:
- the Oracle instance is the same (i.e.: same host, same port, same sid)
- the usernames and passwords are different
- in Oracle, each user is allowed to access a different database (i.e.: two different table spaces)

The first problem we encountered was an issue with the enlisting of resources: reading similar problems on the Internet and debugging, we discovered that the isSameRM method of OracleXAResource returns true if the jdbc connection URLs are the same... In our case, they were actually the same for the two DBs because we have the same host name, port and sid. We solved this problem by either:
- specify username/password in the URL
or
- invert the ordering of the specification of the hostname and port in the URL (it's ugly, but it works)

Anyway, once we have solved that problem, we are now encountering another one: often (but not always) Oracle is giving us an XAER_NOTA when committing a transaction that was just correctly prepared in the 2PC process. We did some investigations on the Internet and found that this corresponds to ORA-24756 error and that seems to be bound to transaction timeout issues.

In jbossjta-properties.xml we have tried to set the com.arjuna.ats.arjuna.coordinator.defaultTimeout parameter to either 0 or 120000, but the result is the same.

We tried with both Oracle Database Enterprise Edition 9i 9.2.0.8.0 and 10g 10.2.0.1.0 and the result is the same. We also tried the driver versions 10.2.0.4.0 and 11.1.0.7.0 and the only difference between them is that the first one hides the XAER_NOTA by mapping it to an XAER_RMFAIL, therefore making the diagnosis even harder.

Any hint to identify the problem would be really appreciated.

Mauro.

[mauromol]

I investigated further.

With Oracle Driver 10.2.0.4.0, the XAER_NOTA is "translated" by the driver to an XAER_RMFAIL, so JBossTS rises an exception.

Oracle Driver 11.1.0.7.0, instead, preserves the XAER_NOTA, so the return value of the commit issued on the XAResource (in com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord.topLevelCommit()) is XAException.XAER_NOTA: the _recovered field is false, so the method returns TwoPhaseOutcome.FINISH_ERROR. The calling method, com.arjuna.ats.arjuna.coordinator.BasicAction.doCommit(boolean, AbstractRecord), then adds the corresponding recordlist to the failedList (line 3205 of BasicAction; I'm using JBossJTA 4.5.0 GA), but then it returns TwoPhaseOutcome.FINISH_OK. From that point on, the commit seems to be ok, but I can't see any further processing of the failedList. I don't know if this is a problem or not.

Please note another important fact. The resources that cannot be committed in my case are all "read only" resources (the commit is issued by line 2159 of com.arjuna.ats.arjuna.coordinator.BasicAction.phase2Commit(boolean)). Maybe this is the reason for which JBossTS doesn't care to process the failedList.

So you might be wondering why I disabled the readOnlyOptimisation and I'm committing read-only resources. The reason is issue JBTM-492, which is described here:
http://www.jboss.org/index.html?module=bb&op=viewtopic&t=150777

Because I can't wait for JBossTS 4.6.0GA to be available, I had to disable that optimisation. However, this causes the problem I'm describing with Oracle.

So, my questions are:

1) can I ignore the fact that the commit of readonly resources fail with XAER_NOTA and the consequent JBossTS warnings? Or should I better re-enable readOnlyOptimisation and patch JBossTS by myself to resolve issue JBTM-492?

2) why do you think Oracle is complaining when committing readonly resources and it is returning XAER_NOTA instead of XA_RDONLY?

Mauro.

[marklittle]

The failed list is driven through the recovery subsystem.

You'd have to talk with Oracle about their use of NOTA versus RDONLY.