Clever Geek Handbook

Java more than one DB connection in UserTransaction

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 that DAO

takes DataSource

as an object to create a separate connection to perform database operations.

So I have UserTransaction

, inside there are two objects DAO

doing split action, delete is done first, and second is insert. Deletion is to delete some of the records to allow insertion as the insert will insert the same primary key data.

I take out the layer DAO

and translate the logic into the code above. There is one thing that I could not figure out based on the above code, the insert operation should fail because the code (internally UserTransaction

) uses two different joins, they don’t know each other, and the first delete doesn’t explicitly, so the second statement (insert) should fail (due to unique constraint) because two database operations are not on the same connection, the second connection cannot detect uncommitted changes. Surprisingly, it doesn't fail, and both statements can work just fine.

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

+3


source to share


5 answers


Since your application is running on a weblogic server, the java-EE container manages the transaction and connection for you. If you call DataSource#getConnection

multiple times in java-ee transaction you will get multiple instances Connection

joining the same transaction. Typically these connections connect to the database with an identical session. Using oracle you can check this with the following snippet in @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 are getting different instances Connection

with the same session ID.

See for example this question for more details

+5


source


The only way to do this correctly is to use a transaction manager and two XA phase keys for all databases participating in this transaction.



+2


source


I assume you have autocommit enabled on your connections. This is the default when creating a new connection as described here. https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

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

will most likely print true

.

You may try

connection1.setAutoCommit(false);

and see if that changes the behavior.

In addition to that, it hasn't defined what happens if you call close () on a connection and haven't posted a commit or rollback command ahead of time. So it is highly 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 ()

EDIT 1: If autocommit is false, it might be due to the undefined behavior of the closure. What happens if you switch statements?

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

EDIT 2: You can also try the "correct" way to do this:

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

If that doesn't work, then something is wrong with your setting for UserTransactions.

0


source


This is perfectly normal depending on your database.

An object that implements an interface UserTransaction

is a "logical transaction". It does not always correspond to a real, "physical" transaction that the database engine respects.
For example, there are situations that cause implicit commits (as well as implicit starts) of transactions. In the case of Oracle (cannot vouch for other DBs), closing the connection is one of them.

From Oracle docs :

"If autocommit is disabled and you close the connection without explicitly committing or undoing your most recent changes, then an implicit COMMIT is performed."

But there may be other possible reasons for implicit commits: select to update, different locking statements, DDL, etc. They are database specific.

So, back to our code.
The first transaction is done by closing the connection. Then another transaction is implicitly started by DML on the second connection. It inserts non-conflicting changes and the second connection.close()

commits them without breaking PK. tx.commit()

won't even get a chance to do anything (and how can it be connected already closed).

The bottom line: "Logical" transaction managers don't always give you the full picture.
Sometimes transactions start and occur for no apparent reason. And sometimes they are even ignored by the DB.

PS: I assumed you were using Oracle, but this is true for other databases as well. For example, a MySQL list of reasons for implicit commit .

0


source


If auto-commit mode is disabled and you close the connection without explicitly committing or undoing your most recent changes, then an implicit operation is performed COMMIT

.

See the link below for more details:

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

0


source






More articles:


All Articles