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

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.