Do i need to manage concurrency regarding database access?

Question

I've 4 threads running in concurrence manner( inserting/selecting rows in/from the database ), do i have to care about synchronizing the access or H2 Databse has already mechanism to manage access between threads ?

PS: The Connection conn variable is shared between threads, so the connection is made one time, because i'm using the embedded mode, so that only one connection is permitted..

Cheers.

Answer 1

There are two possible issues here. One is whether the connection object is threadsafe (meaning that concurrent calls won't put the object in a bad state) and the other is about granularity (whether the built-in synchronization is appropriate for your needs).

According to H2's documentation:

Thread safety: the connection is thread-safe, because access is synchronized. However, for compatibility with other databases, a connection should only be used in one thread at any time.

As far as H2 is concerned accessing the connection from multiple threads concurrently should be ok. (It won't be speedy because the threads will be waiting in line to get the connection, but the connection object will not suffer some kind of breakage due to race conditions causing things to get corrupted.) The warning here is that if you want to use the same code with other databases, you should not count on the other databases' synchronizing as H2 does: even if the JDBC spec mandates that the connection be threadsafe (it might, I don't recall), some drivers may not be compliant.

At this point if you don't care about transactions and only need autocommit (meaning each JDBC call is committed separately) then you're fine (however see Mark R's point in the comments about resultsets getting closed when using autocommit). However, if you are grouping multiple JDBC calls into transactions (setting autocommit to false and calling commit on the connection), then you will want to manage access to the connection yourself (have a thread acquire a connection exclusively, run the JDBC statements, then call commit, then release the lock on the connection) so that you have control over what goes into a transaction. Otherwise when you call commit on a connection there's no telling which threads' work is getting committed.

The safest route is to synchronize access to the entire units of work. Consider having your threads queue up their SQL commands and have them processed by a dedicated thread that has sole access to the connection.