UserTransaction中的Java多个数据库连接

static void clean() throws Exception {
  final UserTransaction tx = InitialContext.doLookup("UserTransaction");
  tx.begin();

  try {
    final DataSource ds = InitialContext.doLookup(Databases.ADMIN);
    Connection connection1 = ds.getConnection();
    Connection connection2 = ds.getConnection();
    PreparedStatement st1 = connection1.prepareStatement("XXX delete records XXX"); // delete data

    PreparedStatement st2 = connection2.prepareStatement("XXX insert records XXX"); // insert new data that is same primary as deleted data above

    st1.executeUpdate();
    st1.close();
    connection1.close();
    st2.executeUpdate();
    st2.close();
    connection2.close();
    tx.commit();
  } finally {
    if (tx.getStatus() == Status.STATUS_ACTIVE) {
      tx.rollback();
    }
  }
}

I have a web app, the DAO taking DataSource as the object to create individual connection to perform database operations.

我有一个Web应用程序,DAO将DataSource作为对象来创建单独的连接以执行数据库操作。

So I have a UserTransaction, inside there are two DAO object doing separated action, first one is doing deletion and second one is doing insertion. The deletion is to delete some records to allow insertion to take place because insertion will insert same primary key's data.

所以我有一个UserTransaction,里面有两个DAO对象做分离动作,第一个是删除,第二个是插入。删除是删除一些记录以允许插入,因为插入将插入相同的主键数据。

I take out the DAO layer and translate the logic into the code above. There is one thing I couldn't understand, based on the code above, the insertion operation should fail, because the code (inside the UserTransaction) take two different connections, they don't know each other, and the first deletion haven't committed obviously, so second statement (insertion) should fail (due to unique constraint), because two database operation not in same connection, second connection is not able to detect uncommitted changes. But amazingly, it doesn't fail, and both statement can work perfectly.

我拿出DAO层并将逻辑转换为上面的代码。有一点我无法理解,根据上面的代码,插入操作应该失败,因为代码(在UserTransaction内)采用两个不同的连接,它们彼此不认识,而第一个删除没有明确提交,所以第二个语句(插入)应该失败(由于唯一约束),因为两个数据库操作不在同一个连接中,第二个连接不能检测未提交的更改。但令人惊讶的是,它并没有失败,这两种说法都可以完美地运作。

Can anyone help explain this? Any configuration can be done to achieve this result? Or whether my understanding is wrong?

有人可以帮忙解释一下吗?可以通过任何配置来实现此结果吗?或者我的理解是否错误?

5 个解决方案

#1

Since your application is running in weblogic server, the java-EE-container is managing the transaction and the connection for you. If you call DataSource#getConnection multiple times in a java-ee transaction, you will get multiple Connection instances joining the same transaction. Usually those connections connect to database with the identical session. Using oracle you can check that with the following snippet in a @Stateless ejb:

由于您的应用程序在weblogic服务器中运行,因此java-EE-container正在为您管理事务和连接。如果在java-ee事务中多次调用DataSource#getConnection,则会有多个Connection实例加入同一事务。通常这些连接使用相同的会话连接到数据库。使用oracle,您可以使用@Stateless ejb中的以下代码段检查:

@Resource(lookup="jdbc/myDS")
private DataSource ds;

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
@Schedule(hour="*", minute="*", second="42")
public void testDatasource() throws SQLException {

    try ( Connection con1 = ds.getConnection();
          Connection con2 = ds.getConnection();
        ) {

        String sessId1 = null, sessId2 = null;
        try (ResultSet rs1 = con1.createStatement().executeQuery("select userenv('SESSIONID') from dual") ){
            if ( rs1.next() ) sessId1 = rs1.getString(1);
        };
        try (ResultSet rs2 = con2.createStatement().executeQuery("select userenv('SESSIONID') from dual") ){
            if ( rs2.next() ) sessId2 = rs2.getString(1);
        };

        LOG.log( Level.INFO," con1={0}, con2={1}, sessId1={2}, sessId2={3}"
               , new Object[]{ con1, con2, sessId1, sessId2}
               );
    }

}

This results in the following log-Message:

这会产生以下日志消息:

con1=com.sun.gjc.spi.jdbc40.ConnectionWrapper40@19f32aa, 
con2=com.sun.gjc.spi.jdbc40.ConnectionWrapper40@1cb42e0, 
sessId1=9347407, 
sessId2=9347407

Note that you get different Connection instances with same session-ID.

请注意,您将获得具有相同会话ID的不同Connection实例。

For more details see eg this question

有关详细信息,请参阅此问题

#2

The only way to do this properly is to use a transaction manager and two phase commit XA drivers for all databases involved in this transaction.

正确执行此操作的唯一方法是对此事务中涉及的所有数据库使用事务管理器和两阶段提交XA驱动程序。

#3

My guess is that you have autocommit enabled on the connections. This is the default when creating a new connection, as is documented here https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

我的猜测是你在连接上启用了自动提交。这是创建新连接时的默认设置,如此处所述https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

System.out.println(connection1.getAutoCommit());

will most likely print true.

很可能会打印出来。

You could try

你可以试试

connection1.setAutoCommit(false);

and see if that changes the behavior.

并查看是否会改变行为。

In addition to that, it's not really defined what happens if you call close() on a connection and haven't issued a commit or rollback statement beforehand. Therefore it is strongly recommended to either issue one of the two before closing the connection, see https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close()

除此之外,如果你在连接上调用close()并且事先没有发出commit或rollback语句,那么它并没有真正定义会发生什么。因此,强烈建议在关闭连接之前发出其中一个,请参阅https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close()

EDIT 1: If autocommit is false, the it's probably due to the undefined behavior of close. What happens if you switch the statements? :

编辑1:如果autocommit为false,则可能是由于close的未定义行为。如果切换语句会发生什么? :

st2.executeUpdate();
st2.close();
connection2.close();
st1.executeUpdate();
st1.close();
connection1.close();

EDIT 2: You could also try the "correct" way of doing it:

编辑2:您也可以尝试“正确”的方式:

st1.executeUpdate();
st1.close();
st2.executeUpdate();
st2.close();
tx.commit();
connection1.close();
connection2.close();

If that doesn't fail, then something is wrong with your setup for UserTransactions.

如果这没有失败,那么您的UserTransactions设置有问题。

#4

Depending on your database this is quite a normal case.

根据您的数据库,这是一个非常正常的情况。

An object implementing UserTransaction interface represents a "logical transaction". It doesn't always map to a real, "physical" transaction that a database engine respects.
For example, there are situations that cause implicit commits (as well as implicit starts) of transactions. In case of Oracle (can't vouch for other DBs), closing a connection is one of them.

实现UserTransaction接口的对象表示“逻辑事务”。它并不总是映射到数据库引擎所尊重的真实的“物理”事务。例如,有些情况会导致事务的隐式提交(以及隐式启动)。对于Oracle(不能担保其他DB),关闭连接就是其中之一。

From Oracle's docs:

来自Oracle的文档:

"If the auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is run".

“如果禁用了自动提交模式,并且在未显式提交或回滚上次更改的情况下关闭连接,则会运行隐式COMMIT操作”。

But there can be other possible reasons for implicit commits: select for update, various locking statements, DDLs, and so on. They are database-specific.

但是隐式提交可能有其他可能的原因:选择更新,各种锁定语句,DDL等。它们是特定于数据库的。

So, back to our code.
The first transaction is committed by closing a connection. Then another transaction is implicitly started by the DML on the second connection. It inserts non-conflicting changes and the second connection.close() commits them without PK violation. tx.commit() won't even get a chance to commit anything (and how could it? the connection is already closed).

所以,回到我们的代码。通过关闭连接来提交第一个事务。然后,第二个连接上的DML隐式启动另一个事务。它插入非冲突的更改,第二个connection.close()在没有PK违规的情况下提交它们。 tx.commit()甚至没有机会提交任何东西(怎么可能?连接已经关闭)。

The bottom line: "logical" transaction managers don't always give you the full picture.
Sometimes transactions are started and committed without an explicit reason. And sometimes they are even ignored by a DB.

底线:“逻辑”交易经理并不总能为您提供全面的信息。有时,事务在没有明确原因的情况下启动和提交。有时他们甚至被DB忽略了。

PS: I assumed you used Oracle, but the said holds true for other databases as well. For example, MySQL's list of implicit commit reasons.

PS:我以为你使用过Oracle,但是对于其他数据库来说也是如此。例如,MySQL的隐式提交原因列表。

#5

If auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is executed.

如果禁用了自动提交模式,并且在未显式提交或回滚上次更改的情况下关闭连接,则会执行隐式COMMIT操作。

Please check below link for details:

请查看以下链接了解详情:

http://in.relation.to/2005/10/20/pop-quiz-does-connectionclose-result-in-commit-or-rollback/

#1

@Resource(lookup="jdbc/myDS")
private DataSource ds;

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
@Schedule(hour="*", minute="*", second="42")
public void testDatasource() throws SQLException {

    try ( Connection con1 = ds.getConnection();
          Connection con2 = ds.getConnection();
        ) {

        String sessId1 = null, sessId2 = null;
        try (ResultSet rs1 = con1.createStatement().executeQuery("select userenv('SESSIONID') from dual") ){
            if ( rs1.next() ) sessId1 = rs1.getString(1);
        };
        try (ResultSet rs2 = con2.createStatement().executeQuery("select userenv('SESSIONID') from dual") ){
            if ( rs2.next() ) sessId2 = rs2.getString(1);
        };

        LOG.log( Level.INFO," con1={0}, con2={1}, sessId1={2}, sessId2={3}"
               , new Object[]{ con1, con2, sessId1, sessId2}
               );
    }

}