[h2] Thread Blcoking when multiple threads reads from a Table

[aneesh C P]

Hi,

We have a system which uses H2 as metadata lookup during the processing,
during the performance testing it found that the queries to H2 database are
getting blocked during selection if multiple threads are accessing one
table.

This is not an expected behavior, can you pls suggest if any this which we
can implement to overcome this behavior.

Regards
Aneesh C P

[Evgenij Ryazanov]

Hello.

Which version of H2 do you use? How your JDBC connection URL looks like?

There are two possible sources of locking.

1. Table-level locks can cause deadlocks between transactions if tables are
not locked in the same order when MVCC feature is not used.

2. A long command blocks all other commands if multi-threaded mode is not
enabled. You can enable it by appending
;MULTI_THREADED=TRUE
to your connection URL. (In the next version of H2 it will be enabled by
default for MVStore engine.)