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. -joseph On May 27, 8:06 am, chenson42 <[email protected]> wrote: > We are using H2 as an embedded POS database (it have been very fast > and very stable, thanks!) > > One issue I am trying to solve is caused by updates to items and > prices being loaded while the the register is being used during the > business day. Does anybody have recommendations on how to best > accomplish concurrent read and update/insert access to the database? > > I tried turning on MVCC mode, but it slows down our initial data loads > by a factor of 4-5. It would be acceptable to take the performance > hit after an initial data load has completed. Can MVCC mode be turned > on dynamically? Is MVCC stable enough for production usage? > > What does setting MULTI_THREADED=1 accomplish? I can't glean from the > documentation what MULTI_THREADED will buy us. > > Note that our background item/price loads can (currently) happen in > fairly large transactions. Maybe my best option would be to just > ensure that transactional updates are broken up into smaller > transactions and make sure that the LOCK_TIMEOUT is set high enough to > allow reads to slip in during loads? > > Thanks in advance for any help that can be provided! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
