Database – Java multiple database connections in UserTransaction

static void clean() throws Exception {

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 application. Dao uses datasource as an object to create a separate connection to perform database operations

So I have a UserTransaction, in which two Dao objects do the separation action, the first is to delete and the second is to insert Delete is to delete some records to allow insertion, because the insertion will insert the same primary key data

I took out the Dao layer and converted the logic into the above code I can't understand that based on the above code, the insert operation should fail because the code (in UserTransaction) uses two different connections, they don't know each other, and the first deletion is not obviously committed, so the second statement (insert) should fail (due to the unique constraint), because the two database operations are not in the same connection, The second connection cannot detect uncommitted changes But surprisingly, it did not fail, and both statements worked perfectly

Can someone help explain? Can this be achieved through any configuration? Or is my understanding wrong?

Solution

Because your application runs in a WebLogic Server, Java EE container is managing transactions and connections for you If datasource#getconnection is called multiple times in a Java EE transaction, multiple connection instances will join the same transaction Usually these connections use the same session to connect to the database With Oracle, you can use the following code snippet in @ stateless EJB to check:

@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 messages:

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

Note that you will get different connection instances with the same session ID

For more information, see, for example, this question