How to close all connections in HSQLDB (also prevents a locking defect)

Nice comments…

Thank your working…

The file may get corrupted. At worst you have to manually truncate it.

But what should I do when I debug with my IDE and terminate abruptly my application? This will lead to an not terminated database connection.

Is there any way to get over this effect?

Nice site and ur information is nice.
i appriciate this.

Jakkireddy

@Stefan

stmt.executeUpdate(”SHUTDOWN”);

where stmt is the java.sql.Statement you have created earlier from HSQLDB java.sql.Connection.

Hello,

i have the same problem.

You’ve wrote:
***********************
you must explicitly issue a command as an admin user. (”SHUTDOWN …”).
***********************

Can you please give me a small code example of that shutdown procedure?

Thank you
Stefan

@Campbell

Thanks for your detailed insight into this issue.

As you can see from my code above that I am normally closing my connection to the database. I had opened the database as a single user from my application (in other words not as a server). So the rules of server requiring a shutdown shouldn’t really be applicable here.

I am with you wrt. your locking logic in a server scenario. However as a single user closing the connection should be sufficient cue to release any lock to the database.

I think most use cases to HSQLDB is in single user (non-server) mode. Often we use it to load test cases for junit tests etc. In these cases ability to rapidly restart is a great benefit.

What do you think?

No. This is not a defect.

I did an extensive analysis and wrote the current hsqldb lock file and priority timer queue implementation: I disagree with your analysis.

HSQLDB is now like Oracle or MySQL, et al: to correctly shut down the database, you must explicitly issue a command as an admin user. (”SHUTDOWN …”).

The main difference is that, unlike Oracle/MySQL, etc., which are fairly rigidly “single installation per machine/release version/data file set” products, it has been observed much more likely that maybe serveral HSQLDB installations/running JVM instances on a machine and each may attempt to access a specific database file set.

That is, we have the problem msaccess had (has?), when the database files were (are?) on a network drive and accessed by several different client machines simultaneously.

But our problem is even more likely to occur, because Java does not provide a shared-mem/ipc ODBC connection manager that allows multiple JVM processes to acccess the same local database file (unless you use the JDBC/ODBC driver, which is not available to type 4 implementations, without writing JNI layer)

Anyway, if you abort the JVM (ctrl-c), kill, etc., or for any other reason the JVM dies abruptly, does a halt() (or even exit() on older JDKS where deleteOnExit is not available), then sure… the .lck file does not get deleted.

In the next release or so, we will auto-detect availability of shutdownHook and add handler there, but this still will not help of true JVM abends.

But back to the point: if the .lck file does not get deleted, then there’s a 10 second buffer zone because the lock file is touched at 10 second intervals.

I chose this number so that in cases where there is really heavy CPU and disk usage going on, there’s a fairly safe buffer zone: Java and most operating systems do not do really accurate timing, especially under high load. Just think about the case of a really high res, high frame-rate video with high bit-rate audio on a file) does not constitute an OS-enforced cross-process lock on the file.