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. On Friday, September 1, 2023 at 10:29:41 AM UTC-4 Ronery Coder wrote: > And stupidly I forgot the link to the github project. Here it is: > > https://github.com/dtonhofer/testing_h2_and_spring_jdbc > > On Friday, September 1, 2023 at 2:48:44 PM UTC+2 Ronery Coder wrote: > >> 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? >> >> >> >> -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d5738b60-946a-4ea2-a749-309dd6e8b9ebn%40googlegroups.com.
