Oracle XA Problems

[danewton]

I have just upgraded from JBoss 2.4.6 to JBoss 3.0.2, as packaged for download, and I am having problems using an oracle XA connection pool.

I am using stateless session beans (1.1) with container managed transactions to make dynamic SQL calls to oracle 9 using the JBoss Oracle XA connection pool. These beans previously worked without any problems in JBoss 2.4.6.

I have configured JBoss 3.0.2 as instructed -- I have the oracle-xa-service.xml file appropriately installed with the correct parameters for our database. I have also added the "Pad" attribute value of "true" to the XidFactory mbean, as directed.

The pool is created without any problems, and single connection transactions are working fine.

I have some instances of multiple selects occurring in the same transaction. Sometimes the 2nd or 3rd retrieval of a connection seems to hang for several tens of seconds. Then I get the following warning:

XAException: tx=XidImpl ... errorCode XA_RETRY

Now, sometimes the selects completes, and sometimes it fails. Either way, when an attempt is made to close the connection, the next warning is:

XAException: tx=XidImpl ... errorCode XAER_RMERR

Once this has happened, it seems to continue to happen when attempting to make the same query. Also, there seems to be some inserts, updates, and deletes that never commit, without any error messages.

The resultSet, statement, and connections used in the application are always properly closed (except obviously when the above error happens on a connection close).

It seems that somehow the use of multiple connections in the same transaction is the root cause, but I am hoping someone else sees something familiar about these problems.

-Doug Newton
[e-mail]

[davidjencks]

There have been plenty of problems with Oracle, and I can't help too much because I don't have a copy.

Can you try setting the maxSize of the pool at 1 and running one client for a while to see if this problem ever appears? This should force all work to go over the same physical connection.

I think I need to write another connection manager that essentially makes all work on one tx go over the same physical connection. Even though this is specifically contrary to the xa spec, at least one vendor seems to require it.

[danewton]

David,
I was unable to run the test you suggested (with a single connection in the pool) because more than one connection is being held open at a time during the same transaction.

We have single transactions that have a scenario like this:
Connection 1 retrieved.
Connection 2 retrieved.
Connection 2 released.
Connection 1 released.
(We have to do a specialized query during the processing of the first result set).

These are the scenarios that are causing problems across the board.

Is this type of use of connections improper within a single transaction? Like I said before, this same code worked in JBoss 2.4.6 with XA oracle connections.

Thanks again,
Doug Newton
[e-mail]

[davidjencks]

I don't know why whether it worked would have changed between 2.4 and 3.x, I thought I was doing basically the same thing.

Why do you need two connections? can't you get 2 statements and run 2 queries on one connection as long as they are in the same transaction?

Is there any way for you to record the start/end flags sent to Oracle? That would help in figuring out what is going on.

There are a couple of changes in jboss 4 that I will be backporting to 3.2 fairly soon and might help, but I think they would have basically the same effect as using just one connection.

[neilblue]

danewton,

Could you tell me where you found the information on the Oracle XA connection pool please. I don't seem to be able to find it in the getting started guide or the administration and development guide

[larsinge]

There are a lot of problems with Oracle generaly. Oracle does not support distributed transactions so the XA-interface is a quite slim. Eg. Oracle does not support a real XADatasource.

It does not support a real DataSource connection in other Operating systems than Windows and Solaris (eg. the oci-driver). There should not be a problem to include the oci-driver. We only need to change the .getConnection()-statement. But it will only work in Windows and Solaris.

It's working as long as we are using one instance of Oracle that does not need a distributed transaction.

I'm working on a transaction-"manager" that will support distributed transactions with Oracle (if I can get my idea to work). But don't wait for me. I have just started to design it and made a dirty core implementation, to see what I can do. The road is bumpy and long... =:o)