H2 in Tomcat SqlException locked by another process when embedded mode

Question

My web project is running in Tomcat 7. My server provider has already a MySql server, however I want to use H2 as it give me a lot more flexibility and speed. My server provider has impose me a limitation that said that I should not start a new thread, doing so will automatically result in a tomcat stop. My project is in an alpha near beta stage, so it is been tested locally. I am getting an SqlException Locked by another process often. This is happening when Tomcat updates the thread pool, most of the times happend when automatically publishing from Eclipse (which it is not a deal, as it won't happend on the server) but some times it just happends randomly. My application doesn't access the database directly but through a wrapper that I am confident about it is not creating the issue but also prevents it to happend a lot. It looks like Tomcat it is sending the thread that holds the connection to the background and any foreground thread will fail (my app it is database intensive). Open a connection in server mode will fix the problem, but it will run in a new process which I'm not aloud to. I will like to keep H2, so before I have to switch to MySql I will need a solution for any of the following answers:

• Can I somehow connect to embedded H2 without Tomcat thread pool collisions?
• Can I connect to server mode H2 without H2 creating a new process?

Note: I can not post any actual code. I am sure is not my application issue. I don't think it is necessary but I will write a description of how my wrapper handle the connection if required, but the problem has been identified as explained above.

Answer 1

Edit : precision on build-deploy step

I could not exactly reproduce your problem because I do not have an Eclipse environment. But :

• H2 documentation is clear on H2 accepting multiple simultaneous connections from the same JVM (ref: Feature - Multiple connections in h2database documentation)
• the same documentation is clear on H2 not accepting direct connections from multiple processes (need that one acts as a server and the other use a TCP connection) - it is not a concern for you as your provider do not allow you to create other processes, but it is the reason of your errors
• I could do some tests opening a H2 database (in embedded mode) in one process with multiple connections and could process simultaneous request (5 connections, one request on each, read the 5 resultsets one row for each at a time)
• I could do a similar test with 2 synchonized threads of same process (thread1 row1, thread2 row1, thread1 row2, thread2 row2 ...)
• as soon as the database is opened in another process (h2console), I get error Database may be already in use: "Locked by another process"

So I now have good reasons to say that the problem occurs because Eclipse opens the database as part of the compilation-deployment process (I know that Netbeans can do that ...), or because it has a view to allow the developper (you) to directly access the database (Netbeans can do that too ....). And you can have a race condition if the application starts before Eclipse closes the database.

As you won't have Eclipse (nor any other process accessing to the database) on your production environment, this should not be a problem.

You can confirm that with those simple steps :

• generate a war under Eclipse
• exit from Eclipse
• start a fresh Tomcat instance (on your developpement machine)
• deploy your application under that instance and connect to it preferently from two different browsers to ensure at least 2 connections

The exception should not occurs.

Optionnaly, if you are used to that, you could stress the application using a tool like Apache JMeter

Even if Eclipse does not access the database by itself, many things may happen in deployement step. A simple way to get rid of that would be to carefully stop and undeploy the application before deploying a new version in the developpement machine. If the problem do not occur any longer, you should have no problem in production (as soon as you respect those steps).