CODEWITHSUNDEEP
javah2
Thorsten
Thorsten

Reputation: 325

JdbcSQLNonTransientConnectionException: Database may be already in use: "Waited for database closing longer than 1 minute"

We are using H2 started as database server process and listening on standard TCP/IP port 9092.

Our application is deployed in a Tomcat using connection pooling. We do a purge during idle time which at the end results in closing all connections to H2. From time to time we observe errors when the application tries to open the connection to H2 again:

SCHEDULERSERVICE schedule: Exception: Database may be already in use: "Waited for database closing longer than 1 minute". Possible solutions: close all other connection(s); use the server mode [90020-199]
org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "Waited for database closing longer than 1 minute". Possible solutions: close all other connection(s); use the server mode [90020-199]
       at org.h2.message.DbException.getJdbcSQLException(DbException.java:617)
       at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
       at org.h2.message.DbException.get(DbException.java:205)
       at org.h2.message.DbException.get(DbException.java:181)
       at org.h2.engine.Engine.openSession(Engine.java:209)
       at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)
       at org.h2.engine.Engine.createSession(Engine.java:161)
       at org.h2.server.TcpServerThread.run(TcpServerThread.java:160)
       at java.lang.Thread.run(Thread.java:748)

       at org.h2.message.DbException.getJdbcSQLException(DbException.java:617)
       at org.h2.engine.SessionRemote.done(SessionRemote.java:607)
       at org.h2.engine.SessionRemote.initTransfer(SessionRemote.java:143)
       at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:431)
       at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:317)
       at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:169)
       at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:148)
       at org.h2.Driver.connect(Driver.java:69)
       at java.sql.DriverManager.getConnection(DriverManager.java:664)

The problem occurs when the Tomcat connection pool closes all idle connection (unused) and one connection still in use is closed afterwards.

The next attempt to open a new connection fails, a retry is successfully after some wait time.

It sounds to me that H2 does a database close after the last connection has been closed.

Thx in advance Thorsten

Upvotes: 0

Views: 1342

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

Embedded database in web applications needs careful handling of its lifecycle.

You can add a javax.servlet.ServletContextListener implementation (marked with @WebListener annotation or included into web.xml) and add explicit database shutdown to its contextDestroyed() methods.

You can force database shutdown here with connection.createStatement().execute("SHUTDOWN"). If your application needs to write something to database during unload, it should do it before that command.

Without the explicit shutdown H2 closes the database when all connections are closed, if some other behavior wasn't configured explicitly (with parameters in JDBC URL, for example). For example, DB_CLOSE_DELAY sets the additional delay, maybe your application uses that setting and therefore H2 doesn't close the database immediately, or application doesn't close all connections immediately.

Anyway, when you're trying to update the web application of the fly, Tomcat tries to initialize the new version before its old version is unloaded. If H2 is in classpath of the web application itself, the new version will be unable to connect to the database during short period of time when the new version is already online but the old version isn't unloaded yet.

If you don't like it, you can run the standalone H2 Server process and use remote connections to it in your web applications.

Another option is to move H2 to the classpath of Tomcat itself and configure the connection pool as resource in the server.xml, in that case it shouldn't be affected by the lifecycle of your applications.

In both these cases you shouldn't use the SHUTDOWN command.

UPDATED

With client-server connections to a remote server such exception means that server decided to close the database because there are no active connection. This operation can't be interrupted and reverted in the middle. On attempt to open a new connection to the same database during this process it waits at most for 1 minute for completion of this process to re-open the database again. This timeout is not configurable.

There are two possible solutions.

  1. DB_CLOSE_DELAY setting can be used with some large value in seconds. When all connections are closed, database will stay online for the specified number of seconds. -1 also can be used to set an infinite timeout.

  2. You can try to speed up the shutdown process, but you have to figure out what takes so much time by yourself. The file compaction procedure is limited to 200 milliseconds by default, it may take a longer time, but I think it shouldn't be that long. Maybe you have a lot of temporary objects or uncommitted data. Maybe you have a very high fragmentation of database file. It's hard to say what's going wrong without further investigation.

Upvotes: 2

Related Questions