Database lock acquisition failure and hsqldb

Question

I was trying to connect to a hsql db. I created one by running from C:\myhsql:

java -cp .;C:\hsql\lib\hsqldb.jar org.hsqldb.Server -database.0 file:db\mydb -dbname.0 MYDB

This created mydb in a directory called db. This folder now has a .lck,tmp,script,properties files with name mydb, and similar files with name MYDB in current folder .

In java code I tried

Class.forName("org.hsqldb.jdbcDriver");
connection = DriverManager.getConnection("jdbc:hsqldb:file:db/sjdb", "SA", "");

When I run the program, I am getting this error:

java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@f3811c1a[file =C:\myhsql\db\mydb.lc
k, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2010-10-19 12:46:09 heartbeat - read: -6750 ms.
...

Here is the stacktrace:

java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@f3811c1a[file =C:\myhsql\db\sjdb.lc
k, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2010-10-19 12:46:09 heartbeat - read: -6750 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(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at ConnectHSQLDB.main(ConnectHSQLDB.java:20)
Caused by: org.hsqldb.HsqlException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@f3811c1a[file =C:\myhsql\db\sjdb.lck, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2010-10-19 12:46:09 heartbeat - read: -6750 ms.
        at org.hsqldb.error.Error.error(Unknown Source)
        at org.hsqldb.persist.LockFile.newLockFileLock(Unknown Source)
        at org.hsqldb.persist.Logger.acquireLock(Unknown Source)
        at org.hsqldb.persist.Logger.openPersistence(Unknown Source)
        at org.hsqldb.Database.reopen(Unknown Source)
        at org.hsqldb.Database.open(Unknown Source)
        at org.hsqldb.DatabaseManager.getDatabase(Unknown Source)
        at org.hsqldb.DatabaseManager.newSession(Unknown Source)
        ... 6 more
java.lang.NullPointerException
        at ConnectHSQLDB.main(ConnectHSQLDB.java:32)

Can somebody tell me what I am doing wrong? I can connect to the db using SwingDBManager and can insert, delete, and select records in the db. I was not running DBManager when I tried the java code. Still the lock problem happens.

Muckraker

Answer 1

The first command starts a server. This server locks the database files so that "others" cannot modify them. You should use "-dbname.0 mydb" instead of "MYDB" as it should be in lowercase.

Your Java connection URL to connect to the database is wrong. You should use "jdbc:hsqldb:hsql://localhost/mydb" as the connection string. While the database files are locked by the server, you can access the database server but you cannot access the database "in-process" with a file: URL.

Kimmel

Answer 2

I face this error because I wanted to view a currently opened database in another client like IntelliJ database while the server is using the same db

so to make hsql db able to be connected to multiple clients, use

hsqldb.lock_file=false

so the connection url will be like

jdbc:hsqldb:file:./db/myDbInFile;hsqldb.lock_file=false

Wheeled

Answer 3

If you have any other client running that connects to your db you need to close that.

Peder

Answer 4

Whatever way you have tried is correct.

You don't have to start the HSQLDB Server using seperate java command, below line is not required, as it will lock the database. Prevent other process from starting and locking db.

java -cp .;C:\hsql\lib\hsqldb.jar org.hsqldb.Server -database.0 file:db\mydb -dbname.0 MYDB

just run the jdbc program

java -cp hsqldb.jar  HSQLAccess 

below line

jdbc:hsqldb:file:db/sjdb

will start the database and will give result.

in this way you don't have to start the server seperately, just have to run the program, which will start and stop HSQLDB for you.

import java.sql.*;

public class HSQLAccess {

    public static void main(String args[]) throws Exception
    {
        Connection con = null;
        try
        {
            Class.forName("org.hsqldb.jdbcDriver");         
            con = DriverManager.getConnection("jdbc:hsqldb:file:db/sjdb", "sa","");    

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM CMDS_WO_MASTER");
            while(rs.next())
            {
                System.out.println(rs.getString(1));
            }

            con.close();

        }
        catch(Exception ex )
        {
            ex.printStackTrace();
        }
        finally
        {
            if(con!=null)
            {
                 con.close();
            }
        }
    }
}

Sochi

Answer 5

You can access HSQLDB database that has lock by:

1- updating my-db-name.properties file and add:

hsqldb.lock_file=false

2- or removing my-db-name.lck file.

3- or connect to the database using pass the properties as parameters:

#Without Sqltool:
    java -cp [jar-path]/hsqldb-2.4.0.jar --inlineRc=url=jdbc:hsqldb:file:/[my-db-file-path]/[db-name];readonly=true;hsqldb.lock_file=false,user=sa

#With Sqltool
    java -cp [jar-path]/hsqldb-2.4.0.jar:[jar-path]/sqltool-2.4.0.jar org.hsqldb.cmdline.SqlTool --inlineRc=url=jdbc:hsqldb:file:/[my-db-file-path]/[db-name];readonly=true;hsqldb.lock_file=false,user=sa

Other HSQLDB database parameters can be found here, however note that only few of them can be updated after first time. http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html

Parrisch

Answer 6

try using the following connection url in windows connection = DriverManager.getConnection("jdbc:hsqldb:file:///c:/hsqldb/mydb", "SA", "");

Stewardson

Answer 7

I just closed NetBeans, deleted database.lck and executed the application again. Everything worked fine.

Unimposing

Answer 8

on my Mac, the port for Connector on HTTP changed from 8080 to 8443 on server.xml. and that is what was giving me this error: both HTTP and HTTPS schema were using the same port

Sadden

Answer 9

I had the same situation with this error:

Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@3ad4d20b[file =E:\workspace-dev\hexagonal\base\.lck, exists=true, locked=false, valid=false, ] method: 

I closed the : HSQL Database Manager, and it worked fine for me!

I also had to put a slash at the end of the database url

@Bean
public DataSource dataSource() {
    var ds = new SimpleDriverDataSource();
    ds.setDriverClass(org.hsqldb.jdbcDriver.class);
    ds.setUrl("jdbc:hsqldb:file:E:/workspace-dev/hexagonal/base/");
    ds.setUsername("SA");
    ds.setPassword("1234");
    return ds;
}

Approachable

Answer 10

I had the same error, the problem was caused by spaces at the of the Database URL from copy/pasting 😅

Dit