The latest documentation reads "MVCC can not be used at the same time as MULTI_THREADED. The MVCC feature is not fully tested yet."
FYI, when I ported my application (which currently runs on both Postgres and Oracle) to use H2 as an out-of-the-box embedded database option to facilitate one-click installs for quick demonstration purposes, I found that "MVCC=true" was required. Without it, my application would quickly run into lock timeout issues. So I increased my lock timeout to 10 seconds, which I think should be plenty of time to acquire a lock, but it still failed. My best guess (without looking too deeply into the issue) is that if I have multiple threads working in concert to simultaneously gather data to render a single page of my application, that accessing the various tables in different orders naturally leads to deadlock situations (which the lock timeout then resolves, but it does so by aborting one or more of the transactions trying to access the db). Even with LOCK_MODE=3, which according to the documentation is supposed to provide the best non-MVCC concurrent profile, my application was still getting the lock timeouts. By the way, after reading your post I tried flipping the MULTI_THREADED bit to true, but that's didn't seem to have any affect, and I continued to see failures. OK, so next I tried flipping the MVCC bit to true. That *DID* seem to solve the concurrency issues I was having, but after navigating around the application for a while I started noticing some oddities. When I perform operations that add or remove, say, a few thousands rows at a time, it appears there are some inconsistencies with respect to the expected isolation level (in this case, read committed). From the docs: "When using MVCC in this database, delete, insert and update operations will only issue a shared lock on the table. An exclusive lock is still used when adding or removing columns, when dropping the table, and when using SELECT ... FOR UPDATE. Connections only 'see' committed data, and own changes." However, it doesn't appear that this read committed semantic is always adhered to (maybe that's why this feature is still experimental?). It seems that depending on which connection I got from the pool on a particular request, I sometimes saw the updated data, sometimes saw the old data. It's really the strangest thing. Basically, I can perform some action in my application that changes lots of rows across different parts of the model, hit refresh a bunch of times, and literally see the data flip flop back and forth between the old and the new. Though after enough time passe4d (anywhere between 1 and 30 seconds) the page eventually "stabilized" and I consistently saw the new data. I'm kind of hoping that Thomas replies telling us that we're just not passing the correct options in the connection URL, and that there is some special combination that will get us the semantics we're looking for. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
