H2 in Tomcat SqlException thrown by another process when inline mode

Question

My web project runs on Tomcat 7. My server provider already has a MySql server, however I want to use H2 as it gives me more flexibility and speed. My server provider imposed a restriction on me, which said that I should not start a new thread, so that this would automatically stop tomcat. My project is in alpha beta testing, so it is tested locally. I get a SqlException thrown by another process. This happens when Tomcat updates the thread pool, in most cases when you automatically publish Eclipse (which is not a deal, as it will not happen on the server), but sometimes it happens by accident. My application does not directly access the database, but through the shell, which I'm sure it does not create a problem, but also prevents it a lot. It seems like Tomcat is sending a thread that maintains a connection to the background, and any foreground thread will fail (my application runs intensively in the database). Open the connection in server mode, fix the problem, but it will work in the new process, which I did not hear aloud. I would like to save H2, so before switching to MySql, I need a solution for any of the following answers:

• Is it possible to somehow connect to the embedded H2 without collisions of the Tomcat thread pool?
• Is it possible to connect to server mode H2 without H2, creating a new process?

Note. I can not post any actual code. I am sure that this is not my problem with the application. I do not think this is necessary, but I will write a description of how my shell handles the connection, if necessary, but the problem has been identified as described above.

Answer 1

Edit: accuracy at build-deploy phase

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

• In the H2 documentation, it is clear that H2 accepts several simultaneous connections from the same JVM (link: Function - multiple connections in the h2database documentation)
• In the same documentation, it is clear that H2 does not accept direct connections from several processes (it is required that one of them acts as a server and the other a TCP connection) - this is not a problem for you, since your provider does not allow you to create other processes, but this is the cause of your mistakes.
• I could perform some tests that open the H2 database (in embedded mode) in one process with several connections and could handle a simultaneous query (5 connections, one query each, read 5 results, one row for each at a time)
• I could perform a similar test with two synchronized threads of one process (thread1 row1, thread2 row1, thread1 row2, thread2 row2 ...)
• as soon as the database is opened in another process (h2console), I get the error message Database may be already in use: "Locked by another process"

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

Since you will not have Eclipse (or any other process accessing the database) in a production environment, this should not be a problem.

You can confirm that with these simple steps:

• create war under eclipse
• exit eclipse
• run a new instance of Tomcat (on your development machine)
• deploy the application under this instance and connect to it preferably from two different browsers to provide at least 2 connections.

An exception should not occur.

Optionnaly, if you’re used to it, you can emphasize the application using a tool like Apache JMeter

Even if Eclipse does not access the database on its own, a lot can happen during the deployment phase. An easy way to get rid of this would be to carefully terminate and deploy the application before deploying the new version in the development machine. If the problem no longer occurs, you should have no problems with production (as soon as you follow these steps).