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