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.
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.
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.
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
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)