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 On Saturday, September 2, 2023 at 4:47:18 AM UTC+2 Andrei Tokar wrote: > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d872c16f-408a-445a-9a4a-7261f9bdfb5fn%40googlegroups.com.
