What are the best settings of the H2 database for high concurrency?

Question

There are a lot of settings that can be used in H2 database. AUTO_SERVER, MVCC, LOCK_MODE, FILE_LOCK and MULTI_THREADED. I wonder what combination works best for high concurrency setup e.g. one thread is doing INSERTs and another connection does some UPDATEs and SELECTs?

I tried MVCC=TRUE;LOCK_MODE=3lFILE_LOCK=NO but whenever I do some UPDATEs in one connection, the other connection does not see it even though I commit it. By the way the connections are from different processes e.g. separate program.

Answer 1

I cannot reproduce your findings with MVCC=TRUE and the default LOCK_MODE, but FILE_LOCK=NO "is unsafe as another process is able to open the same database, possibly leading to data corruption." See also File Locking Protocols. Do your existing tests suggest a problem with the default settings?

Addendum: Although I haven't used it, your subsequent results seem consistent with the documented behavior of AUTO_SERVER, described in Automatic Mixed Mode. The feature appears to permit fast embedded mode access with periodic remote access, as for maintenance.