Embedded H2 database: Multithreaded access

user_5037147 0
Viewed 811

I have a program which parses some data out of HTML files and then does some computations on those files depending on the current date.

Since the parsing of the files takes a lot of time and they are not changing, I am now storing the data in an embedded H2 database and only parsing it if the data from that file is not in the database already. Otherwise I am just querying it from the database. Still the initial parsing and also when there are new files, it takes a lot of time.

So I am now at a point where I am trying to multithread the parsing. I want to do this using the ExecutorService and splitting the files into multiple tasks (one per file is probably not a good idea, probably going to be 10-20 tasks to use all cores). Now I don’t know, how I should implement the database access. Should I only have one connection and create the needed PreparedStatements per Task or should I use a connection pool (and also create the PreparedStatements per task) or something else?

h2 jdbc multithreading database java
1 Answers

In JDBC, the Connection object may have multiple Statement and PreparedStatement instances, but they can't do their work concurrently. If you perform some commands concurrently, they will be executed one by one. It's safe if they don't depend on each other and you don't modify settings of the connection in them, but your threads will wait for completion of commands from other threads. If your commands don't work fast enough these additional delays can be noticeable. In that case you can create an own Connection per thread and create Statement or PreparedStatement instances for this thread from its own connection. Different connections can execute commands concurrently.

In H2 you also need a recent version of the database and you need to use the default MVStore engine. The legacy PageStore engine (;MV_STORE=FALSE) runs in single-threaded mode. Older versions of H2 also use single-threaded mode by default with both storage engines and it's not fully safe to enable multi-threading in them explicitly.

user_117319870
Related