Java more than one DB connection in UserTransaction

Question

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.

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.

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.

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

Answer 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:

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

For more details see eg this question

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