Spring XA Transactions With Oracle JDBC Driver

Question

I am trying to enable distributed transactions using bitronix transaction manager. Configuration Details include

1. Using OCI JDBC Driver and oracle.jdbc.xa.client.OracleXADataSource. This datasource is wrapped around by UCP connection pooling datasource - oracle.ucp.jdbc.PoolDataSourceImpl
2. Using spring JdbcTemplate to execute queries.
3. Using Bitronix Transaction Manager for handling distributed transactions
4. Spring Declarative Transactions using Annotations

The issue I am facing is that the queries executed using the JDBCTemplate are not being executed in a transaction. My test case executes two queries using a JDBCTemplate and they do not rollback when the method throws a runtime exception after the query execution. I could also see the auto commit status of the connection is set to true.

<tx:annotation-driven transaction-manager="distributedTransactionManager"/>

<bean id="distributedTransactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
    <property name="transactionManager" ref="bitronixTransactionManager"/>
    <property name="userTransaction" ref="bitronixTransactionManager"/>
    <property name="allowCustomIsolationLevels" value="true"/>
</bean>

<bean id="bitronixTransactionManager" factory-method="getTransactionManager"
      class="bitronix.tm.TransactionManagerServices" depends-on="bitronixConfiguration"
      destroy-method="shutdown">
</bean>

The data source is created as follows

    PoolDataSourceImpl pds = new PoolDataSourceImpl();
    try {
        pds.setConnectionPoolName(dataSourceName);
        pds.setConnectionFactoryClassName("oracle.jdbc.xa.client.OracleXADataSource");
        pds.setConnectionFactoryProperties(getOracleDataSourceProperties());
        pds.setDataSourceName(dataSourceName);
        pds.setServerName("v-in-sd-tst-12");
        pds.setPortNumber(1521);
        pds.setUser("ForTestCasesAmit");
        pds.setPassword("adept");
        pds.setMinPoolSize(10);
        pds.setMaxPoolSize(100);
        pds.setMaxIdleTime(1800);

        pds.startPool();
    } catch (SQLException e) {
        throw new RuntimeException("Cannot create project datasource " + dataSourceName, e);
    }
    return pds;

Any suggestions on what could be the reason why the queries are not executed in a transaction?

Update 1

Adding the method which executes queries using jdbcTemplate and throws an exception at the end

@Transactional(propagation = Propagation.REQUIRED)
public void execute() {
    System.out.println("Starting with the service method");
    jdbcTemplateForDSOne.update("UPDATE T1 SET COL1 = 'Date1' WHERE COL2 = 1");

    jdbcTemplateForDSOne.update("UPDATE T1 SET COL1 = 'Start Date1' WHERE COL2 = 2");

    waitForUserInput();
    throw new RuntimeException("Rollback Now");
}

Update 2

Oracle JDBC Developer Guide mentions that

"The default auto-commit status on a connection obtained from XAConnection is false in all releases prior to Oracle Database 10g. Starting from Oracle Database 10g, the default status is true."

I am using Oracle 11g r2. Any idea what configuration changes should be done while using Distributed Transactions to have the auto commit status as false?

Update 3

The transactions work if I use the bitronix pooling data source instead of oracle ucp PoolDataSource. Using bitronix PoolingDataSource gave an opportunity to bitronix to set the autocommit status to false. Will investigate more to figure the difference between the two.

Answer 1

Do you have the method, which contains this code, annotated with @Transactional or defined any Aspects which would tell Spring to execute this method in a transaction?

Answer 2

As far as I can see, they are executed in a transaction but the transaction isn't what you expect. When autoCommit is true, each query becomes a transaction. You have to configure the connection properly to avoid that.

That said, a comment about XA: XA doesn't work in corner cases like network problems and timeouts. That is, it will work 99.9995% times but in a few critical cases, it won't and those are the cases which you care for.

Make sure that your data structures don't get corrupted when XA eventually fails you.

Instead of XA, I suggest to evaluate methods that allow to run transactions again. Example: You want to transfer some records from database A to B. So you read the rows with FOR UPDATE and for each transmitted row, you set the processed column to true.

On the other side, you only add rows which aren't already present.

After the tx in B has been committed, delete the rows in A where processed = 'true' or mark them in another way.

That means you can run this as often as you need.

[EDIT]

To disable auto commit, you must call con.setAutoCommit(false); The problem is, of course, that you're using Spring, so you never explicitly ask for a connection anywhere.

My suggestion: Extend PoolDataSourceImpl and override the various get*Connection() methods. Before you return them, set auto commit to false. You could also decompile the JDBC driver code to see whether PoolDataSourceImpl already contains something like that but apparently it doesn't.