I am working on a java webapp (Spring 4, Jpa 2, Hibernate 4, HikariCp 2.3.8) that connects to hsqldb 2.3.3 . My customer reported a lock after testing for a brief time with "Database Lock Acquisition Failure". I am using a jdbc url like this:
jdbc:hsqldb:file:/path-to-database/maindb;shutdown=true;hsqldb.write_delay=false;
I have two doubts: - it is correct to use the standalone file mode of hsqldb in a "connection pool" context? I was wondering if this lock is due to the fact that the database can be opened just for one process at a time in standalone mode. - it is correct to use shutdown=true in a "connection pool" context? it is not constantly doing shutdown on database when closing each connection? what happens to the others?
Thanks for helping me
The database can indeed be opened just for one process at a time. You need a server to manage connections from multiple processes. The "Database lock acquisition failure" is always thrown when a second process attempts to connect directly.
It's fine to use file mode with a connection pool as all the connections are from the same process. Running a server is preferable as you have access to database outside the web app.
Note shutdown=true is provided for use in test contexts and shouldn't be used here. An explicit SHUTDOWN should be issued when required.