Java Database Connectivity (JDBC)

We have an application that creates two OracleXADataSource instances and attempts to do a two-phase commit across them. This works fine if the two data sources reside on different databases, but an error occurs if these data sources are different users on the same database.

We are using WebSphere 4.0 (Tried version 4.0.3 and 4.0.5) as the transaction manager, and the database version is 8.1.7. We are using the latest version of the Oracle JDBC drivers for Java 1.2 and later.

Here is a sample of code that causes the problem:
---------------------------------------------------
InitialContext ctx = new InitialContext();
DataSource ds1 = (DataSource)ctx.lookup("java:comp/env/jdbc/app1DB");
Connection conn1 = ds1.getConnection();
Statement st1 = conn1.createStatement();
st1.executeUpdate("insert into trans_test_1 values ('"+System.currentTimeMillis()+"')");
st1.close();
conn1.close();

DataSource ds2 = (DataSource)ctx.lookup("java:comp/env/jdbc/app2DB");
Connection conn2 = ds2.getConnection();
Statement st2 = conn2.createStatement();
st2.executeUpdate("insert into trans_test_2 values ('"+System.currentTimeMillis()+"')");
st2.close();
conn2.close();
---------------------------------------------

This code runs in a stateless session EJB with a container managed transaction.

The container seems to do the JDBC operations in the following order:
1. Creates the first OracleXADataSource.
2. Gets an OracleXAConnection from the data source.
3. Gets a OracleConnection from the XA connection.
4. Gets the OracleXAResource for the XA connection.
5. Calls start on the XA resource.
6. Lets the client app do the DB work on that data source.
7. Repeats steps 1-6 for the second data source
8. Client code finishes executing and container starts committing the managed transaction.
9. End is called on the second OracleXAResource.
10. Prepare is called on the second OracleXAResource, which returns '3' (which indicates read-only and doesn't seem quite right!)
11. End is called on the first OracleXAResource, which throws the following exception:

----------------------------------
java.sql.SQLException: ORA-00603: ORACLE server session terminated by fatal error
ORA-02051: another session in same transaction failed
ORA-02051: another session in same transaction failed

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:407)
at oracle.jdbc.xa.client.OracleXAResource.end(OracleXAResource.java:265)
-----------------------------------------

As mentioned previously, this error occurrs if both data sources reside under different users on the same database. If the data sources are on different databases then there is no difficulty; a two phase commit occurrs successfully.