I am working in a Spring Boot project, it is not very old, but should be considered to be a bit legacy. For some reason, someone decided to use in-memory HSQL db in tests, unfortunately in 1.X version. I am trying to upgrade it to the latest version 2.X.
After upgrading a dependency, a lot of tests stopped working. There are a few crucial changes between versions 1.X and 2.X, i.e.: read uncommitted was a default isolation level in old version, transaction model is different now (I use MVCC).
I found an interesting case in the application. Let's assume I have two tables: Second with some columns and First with id, second_id and other columns. second_id is a foreign key. Now, I start a test and there is an SQL script that inserts two rows:
Second table: a row with id=1,First table: a row with id=1, second_id=null.The script ends with COMMIT instruction, so these rows are committed. So, after the execution of the script, we have two rows which are not linked together by a foreign key.
Now, inside the application, new transaction starts (through @Transactional, propagation=REQUIRED). The transaction attempts to run update query on a row with id=1 from Second table (UPDATE Second SET ... WHERE id=1). Transaction is ongoing, not committed yet.
The code hits another @Transactional method, however with propagation=REQUIRES_NEW. Current transaction is suspended and a new one is spawned. Inside it, the following query is executed: update First set second_id=1 where id=1 - so basically we want to link both rows together through a foreign key. However, it fails with foreign key no parent.
What happens here: row which we are trying to link is currently being modified by the first transaction and it is not visible to that new transaction. And it fails. It seems to be expected according to HSQLDB transaction model, which is a bit strange comparing with other relational dbs.
Do you have any idea how to make it working without modifying the existing code? I can only touch tests and their configuration. The code has been working fine for years (yeah, the design is not nice, I know) and I am not allowed to change anything related to transaction at the moment. There are a lot of new transactions started with REQUIRES_NEW propagation, even in some simple cases, what is an overkill according to me.
I would be very grateful for any ideas how to proceed with that.
The issue with HSQLDB was fixed recently. See below:
https://sourceforge.net/p/hsqldb/bugs/1684/
You can build the jar from the SVN snapshot and use it for tests.