带有 Oracle JDBC 驱动程序的 Spring XA 事务

Spring XA Transactions With Oracle JDBC Driver

提问by Andy Dufresne

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

我正在尝试使用 bitronix 事务管理器启用分布式事务。配置详细信息包括

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

1. 使用 OCI JDBC 驱动程序和 oracle.jdbc.xa.client.OracleXADataSource。该数据源由 UCP 连接池数据源包裹 - oracle.ucp.jdbc.PoolDataSourceImpl
2. 使用 spring JdbcTemplate 执行查询。
3. 使用 Bitronix 事务管理器处理分布式事务
4. 使用注解的 Spring 声明性事务

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.

我面临的问题是使用 JDBCTemplate 执行的查询没有在事务中执行。我的测试用例使用 JDBCTemplate 执行两个查询，并且当该方法在查询执行后抛出运行时异常时，它们不会回滚。我还可以看到连接的自动提交状态设置为 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;

<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

更新 1

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

添加使用 jdbcTemplate 执行查询并在最后抛出异常的方法

@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");
}

@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

更新 2

Oracle JDBC Developer Guidementions that

Oracle JDBC Developer Guide提到

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

“在 Oracle Database 10g 之前的所有版本中，从 XAConnection 获得的连接的默认自动提交状态为 false。从 Oracle Database 10g 开始，默认状态为 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?

我正在使用 Oracle 11g r2。知道在使用分布式事务使自动提交状态为 false 时应该进行哪些配置更改吗？

Update 3

更新 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.

如果我使用 bitronix 池化数据源而不是 oracle ucp PoolDataSource，则事务有效。使用 bitronix PoolingDataSource 为 bitronix 提供了将自动提交状态设置为 false 的机会。将进行更多调查以找出两者之间的差异。

回答by dunni

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?

您是否有包含此代码的方法，使用@Transactional 进行注释或定义任何会告诉 Spring 在事务中执行此方法的方面？

回答by Aaron Digulla

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

据我所知，它们是在交易中执行的，但交易不是您所期望的。当autoCommitis 时true，每个查询都成为一个事务。您必须正确配置连接以避免这种情况。

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.

也就是说，关于 XA 的评论：XA 在网络问题和超时等极端情况下不起作用。也就是说，它将工作 99.9995% 次，但在一些关键情况下，它不会，而那些是您关心的情况。

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

确保当 XA 最终使您失败时，您的数据结构不会损坏。

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 UPDATEand for each transmitted row, you set the processedcolumn to true.

我建议评估允许再次运行事务的方法，而不是 XA。示例：您想将一些记录从数据库 A 传输到 B。因此，您读取带有FOR UPDATE每个传输行的行，并将processed列设置为 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.

提交 B 中的 tx 后，删除 A 中的行where processed = 'true'或以其他方式标记它们。

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.

要禁用自动提交，您必须调用con.setAutoCommit(false);问题当然是您使用的是 Spring，因此您永远不会在任何地方明确要求连接。

My suggestion: Extend PoolDataSourceImpland 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 PoolDataSourceImplalready contains something like that but apparently it doesn't.

我的建议：扩展PoolDataSourceImpl和覆盖各种get*Connection()方法。在返回它们之前，将自动提交设置为 false。您还可以反编译 JDBC 驱动程序代码以查看是否PoolDataSourceImpl已经包含类似的内容，但显然没有。