It is my pleasure to present some transaction-testing code...

[Ronery Coder]

Good day!

I burned some time to write Java code which tests H2 database transaction behaviour through Spring Data JDBC. Well documented. Nothing particularly complex or groundbreaking, just JUnit tests to experiment with. This may be of interest to people who want to do their own tests.

This project contains the tests, along with two other subjects, namely an exercise involving agents who communicate via the H2 database and an exercise that verifies that Java Instants are indeed properly stored and retrieved.

What to report on the exploration of transactions:

1. I have been unable to generate the "Phantom Read" phenomenon in isolation level ANSI "REPEATABLE READ" . This may be because H2 preventatively nixes it at that level, where it is supposed to still occur. It's possible, depending on how transactions are implemented. Or I just don't use a predicate that is complex enough.
   
2. Trying to elicit "Non-Repeatable Read" and "Phantom Read" phenomena at isolation level ANSI "READ COMMITTED", we find that the phenomena are unexpectedly *absent* in ~0.18% of cases, apparently randomly: H2 implements stronger transactions than expected. I *don't* think it is my test code that is the reason, but then again, I'm not sure.
3. There is some problem in Spring Data JDBC "translating" an `org.h2.jdbc.JdbcSQLTimeoutException`, leading to the user code receiving a confusing `org.springframework.transaction.TransactionSystemException` with the message `JDBC rollback failed`. This should probably be fixed at the Spring level.
4. H2 is quite radical in generating "deadlock exceptions" (`org.h2.jdbc.JdbcSQLTransactionRollbackException`). As long as two transaction T1 and T2 were active concurrently, T1 wrote X, then committed and T2 read or wrote _something_ then (after T1's commit), writes X too, a deadlock is detected. If I reflect on what could go wrong in such scenarios, I don't see the reason for throwing. Is H2 just extremely pessimistic/conservative?
   

[Ronery Coder]

And stupidly I forgot the link to the github project. Here it is:

https://github.com/dtonhofer/testing_h2_and_spring_jdbc

[Andrei Tokar]

Hi Ronery Coder,

I haven't had a time to look at your code yet, but any tests are very match appreciated, of course.

In a meantime, here are my 2c on your report from the implementer point of view:

#1,2 Absence of the phenomena at particular level does not mean that implementation is wrong, just MAYBE inefficient. In case of H2, multi-version nature of it allows for a cheap snapshot (at the start of a transaction for REPEATABLE_READ, or at the start of a SQL statement for READ_COMMITTED), which would indeed prevent any "phantom read". If you can come up with implementation, which would benefit from allowing phantom reads, your patch is welcome.

#3 As you said, "This should probably be fixed at the Spring level."
#4 It is correct In NON_REPEATABLE_READ mode, because T1 updated X, so T2 may see at different times (diferent statements with tx) committed value of X, or previous one. Since transactions are concurrent, T2's read may be not repeatable.

In READ_COMMITTED mode your scenario should cause NO exceptions.

But In any case, it's not a deadlock. For a deadlock you need at least two records to be updated by T1 and T2 in different order. It you have an example of a deadlock exception in a different scenario, please let me know.

[Ronery Coder]

Thank you Andrei,

If H2 is stricter than necessary, that's fine.

I have update all my code to make it better structured as clarify testing scenarios.

For the "deadlock elicitation" tests, I have written fresh text and explanatory graphics:

https://github.com/dtonhofer/testing_h2_and_spring_jdbc/blob/master/README.md#test-6-eliciting-deadlock

I have not tried any scenarios where I access two records crosswise to create a deadlock but I *do* indeed get "org.h2.jdbc.JdbcSQLTransactionRollbackException: Deadlock detected" relatively readily.

I'm not sure what other databases would do with my tests, I will have to try.

With best regards,

-- David