hsqldb : Database lock acquisition when connecting to a file db

Question

I am trying to connect to a hsqldb (using version 2.2.9).

I created one using the following:

java -cp hsqldb-2.2.9.jar org/hsqldb/util/DatabaseManagerSwing

When trying to connect to the db thru my ui I'm getting the following exception:

Exception occured :  java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@76e90d02[file =/rhel5pdi/home/mgnyp/workspace/src/Project/webapp/WEB-INF/lib/testDataBase.lck, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2013-04-23 10:35:22 heartbeat - read: -8403 ms.
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)

I presumed the db is locked because the DatabaseManagerSwing established a connection before my ui did, so I closed that one. This caused the following exception:

 Exception occured :  java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@76e90d02[file =/rhel5pdi/home/mgnyp/workspace/src/PmtMetricsUI2/webapp/WEB-INF/lib/testDataBase.lck, exists=false, locked=false, valid=false, ] method: openRAF reason: java.io.FileNotFoundException: /rhel5pdi/home/mgnyp/workspace/src/Project/webapp/WEB-INF/lib/testDataBase.lck (Permission denied)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)

I'm using spring mvc and c3p0. Connection details (absolute path to the db provided):

Connection con = DriverManager.getConnection("jdbc:hsqldb:file:/home/mgnyp/workspace/src/Project/webapp/WEB-INF/lib/testDataBase", "SA", "");

I've tried this approach, unsuccessful unfortunately: Database lock acquisition failure and hsqldb

I also looked up the docs at hsql.org

Apologies if the issue is obvious, I've never used hsql before.

I would appreciate any help or hints.

Thank you.

Answer 1

I was able to disable the lock file by including ;readonly=true as a property on the additional connection string. Otherwise I've found that the lock is always held for a file database. A sample connection string that worked for me was jdbc:hsqldb:file:my/file/location;readonly=true