Shared Transaction between different OracleDB Connections

Question

After several days passed to investigate about the issue, I decided to submit this question because there is no sense apparently in what is happening.

The Case

My computer is configured with a local Oracle Express database. I have a JAVA project with several JUnit Tests that extend a parent class (I know that it is not a "best practice") which opens an OJDBC Connection (using a static Hikari connection pool of 10 Connections) in the @Before method and rolled Back it in the @After.

public class BaseLocalRollbackableConnectorTest {
private static Logger logger = LoggerFactory.getLogger(BaseLocalRollbackableConnectorTest.class);
protected Connection connection;

@Before
public void setup() throws SQLException{
    logger.debug("Getting connection and setting autocommit to FALSE");
    connection = StaticConnectionPool.getPooledConnection();
}

@After
public void teardown() throws SQLException{ 
    logger.debug("Rollback connection");
    connection.rollback();
    logger.debug("Close connection");
    connection.close();
}

StacicConnectionPool

public class StaticConnectionPool {

private static HikariDataSource ds;

private static final Logger log = LoggerFactory.getLogger(StaticConnectionPool.class);

public static Connection getPooledConnection() throws SQLException {

    if (ds == null) {
        log.debug("Initializing ConnectionPool");
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(10);
        config.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");
        config.addDataSourceProperty("url", "jdbc:oracle:thin:@localhost:1521:XE");
        config.addDataSourceProperty("user", "MyUser");
        config.addDataSourceProperty("password", "MyPsw");
        config.setAutoCommit(false);
        ds = new HikariDataSource(config);

    }
    return ds.getConnection();

}

}

This project has hundreds tests (not in parallel) that use this connection (on localhost) to execute queries (insert/update and select) using Sql2o but transaction and clousure of connection is managed only externally (by the test above). The database is completely empty to have ACID tests.

So the expected result is to insert something into DB, makes the assertions and then rollback. in this way the second test will not find any data added by previous test in order to maintain the isolation level.

The Problem Running all tests together (sequentially), 90% of times they work properly. the 10% one or two tests, randomly, fail, because there is dirty data in the database (duplicated unique for example) by previous tests. looking the logs, rollbacks of previous tests were done properly. In fact, if I check the database, it is empty) If I execute this tests in a server with higher performance but the same JDK, same Oracle DB XE, this failure ratio is increased to 50%.

This is very strange and I have no idea because the connections are different between tests and the rollback is called each time. The JDBC Isolation level is READ COMMITTED so even if we used the same connection, this should not create any problem even using the same connection. So my question is: Why it happen? do you have any idea? Is the JDBC rollback synchronous as I know or there could be some cases where it can go forward even though it is not fully completed?

These are my main DB params: processes 100 sessions 172 transactions 189

Answer 1

After all confirmation from your answers that I am not mad with Rollbacks and transactions behavior in unit tests, i deeply checked all queries and all possible causes and fortunately (yes furtunately...even if I'm ashamed for that, I make my mind free) all works as expected (Transactions, Before, After, etc).

There are some queries that get the result of some complex views (and radically deep configured into the DAO layer) to identify the single row information. This view is based on the MAX of a TIMESTAMP in order to identify latest of a particular event (in the real life the events coming after several months).

Doing the preparation of the database to proceed with the unit tests, these events are added sequentially by each test. In some cases, when these insert queries under the same transaction are particular fast, more events related to the same object are added in the same Millisecond (The TIMESTAMP is added manually using a JODA DateTime) and the MAX of a date, returns two or more values. For this reason it is explained the fact that on more performant computers/servers, this happened more frequently than the slower ones. This view is used in more tests and depending by the test, the error is different and random (NULL value added as Primary Key, duplicated primary Key, etc) .

For Example: in the following INSERT SELECT query is evident this bug:

INSERT INTO TABLE1 (ID,COL1,COL2,COL3) 
  SELECT :myId, T.VAL1, T.VAL2, T.VAL3 
  FROM MyView v 
  JOIN Table2 t on t.ID = v.ID
  WHERE ........

the parameter myId is added afterwards as Sql2o Parameter

MyView is

SELECT ID, MAX(MDATE) FROM TABLEV WHERE.... GROUP BY ...

When the view returns at least 2 results due to the same Max Date, it fails because the ID is fixed (generated by a sequence at beginning but stored using the parameter in a second time). This generates the PK constraint violated.

This is only one case but make me (and my colleagues) crazy due to this randomly behaviours...

Adding a sleep of 1 millisecond between those events insert, it is fixed. now we are working to find a different solution even though this case (an user that interact two times in the same millisecond) cannot happen in production system but the important things is that no magic happens as usual!

Now you can insult me :)

Answer 2

Try configure audit on all statements in Oracle. Then find sessions which live simultaneously. I think that there is the problem in tests. JDBC rollback is synchronous. Commit can be configured as commit nowait but I don't think you do it special in your tests.

Also pay attention on parallel dml. On one table in the same transaction you can't do parallel dml + any other dml without commit because you get Ora-12838.

Do you have autonoumous transaction? Business logic in tests can manually rollback them and during tests autonoumous transaction is like another session and it doesn't see any commits from parent session.

Answer 3

If your problem just needs to be "solved" (e.g. not "best practice") regardless of performance to just make the tests complete in order, try to set:

config.setMaximumPoolSize(1);

You might need to set a timeout higher since the tests in the test queue will wait for its turn and might timeout. I usually don't suggest solutions like this but your setup is suboptimal, it will lead to race conditions and data loss. However, good luck with the tests.

Answer 4

Like all other answers have pointed out, it's hard to say what goes wrong with the provided information. Further more, even if you manage to find the current issue by audit, it doesn't mean that your tests are free from data errors.

But here's an alternative: because you already have a blank database schema, you can export it to a SQL file. Then before each test:

1. Drop the schema
2. Re-create the schema again
3. Feed the sample data (if needed)

It would save lots of time debugging, make sure the database in its pristine state every time you run the tests. All of this can be done in a script.

Note: Oracle Enterprise has the flashback function to support your kind of operation. Also, if you can manage to use Hibernate and the likes, there's other in-memory databases (like HSQLDB) that you can utilize to both increase testing speed and maintain coherence in your data set.

EDIT: It seems implausible, but just in case: connection.rollback() only takes effect if you don't call commit() before it.

Answer 5

Not sure if this will fix it, but you could try:

public class BaseLocalRollbackableConnectorTest {
  private static Logger logger = LoggerFactory.getLogger(BaseLocalRollbackableConnectorTest.class);
  protected Connection connection;
  private Savepoint savepoint;

  @Before
  public void setup() throws SQLException{
    logger.debug("Getting connection and setting autocommit to FALSE");
    connection = StaticConnectionPool.getPooledConnection();
    savepoint = connection.setSavepoint();
  }

  @After
  public void teardown() throws SQLException{ 
    logger.debug("Rollback connection");
    connection.rollback(savepoint);
    logger.debug("Close connection");
    connection.close();
    while (!connection.isClosed()) {
      try { Thread.sleep(500); } catch (InterruptedException ie) {}
    }
}

Really there are two 'fixes' there - loop after the close to be sure the connection IS closed before returning to the pool. Second, create a savepoint before the test and restore it afterwards.

Answer 6

I have run into the same problem 2-3 years ago (I have spent a lot of time to get this straight). The problem is that the @Before and @After is not always really sequential. [You could try this by starting the process in debug and place some breakpoints in the annotated methods.

Edit: I was not clear enough as Tonio pointed out. The order of @Before and @After is guarantied in terms of running before the test and afterwards it. The problem was in my case that sometimes the @Before and the @After was messed up.

Expected:

@Before -> test1() -> @After -> @Before -> @test2() -> @After

But sometimes I experienced the following order:

@Before -> test1() -> @Before -> @After -> @test2() -> @After

I am not sure thet it is a bug or not. At the time I dug into the depth of it and it seemed like some kind of (processor?) scheduling related magic. The solution to that problem was in our case to run the tests on a single thread and call manually the init and cleanup processes... Something like this:

public class BaseLocalRollbackableConnectorTest {
    private static Logger logger = LoggerFactory.getLogger(BaseLocalRollbackableConnectorTest.class);
    protected Connection connection;

    public void setup() throws SQLException{
        logger.debug("Getting connection and setting autocommit to FALSE");
        connection = StaticConnectionPool.getPooledConnection();
    }

    public void teardown() throws SQLException{ 
        logger.debug("Rollback connection");
        connection.rollback();
        logger.debug("Close connection");
        connection.close();
    }

    @Test
    public void test() throws Exception{
        try{
            setup();
            //test
        }catch(Exception e){ //making sure that the teardown will run even if the test is failing 
            teardown();
            throw e;
        }
        teardown();
    }
}

I have not tested it but a much more elegant solution could be to syncronize the @Before and @After methods on the same object. Please update me if You have the chanse to give it a try. :)

I hope it will solve your problem too.

Answer 7

You can do one thing increase the no. of connections in max pool size and rollback the operation in the same place where you committed the operation instead of using it in @after statement. Hope it will work.