How to close all connections in HSQLDB (also prevents a locking defect)
By Angsuman Chakraborty, Gaea News NetworkSaturday, March 5, 2005
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class TestHSQLDB {
public static void main(String args[]) throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
Connection connection =
DriverManager.getConnection("jdbc:hsqldb:file:test",
"sa", "");
connection.close();
}
}
 kemalcakmak |
April 1, 2006: 12:49 am
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? |
 jakkireddy |
|
April 6, 2005: 2:09 pm
@Stefan stmt.executeUpdate(”SHUTDOWN”); where stmt is the java.sql.Statement you have created earlier from HSQLDB java.sql.Connection. |
 Stefan C |
April 6, 2005: 1:39 am
Hello, i have the same problem. You’ve wrote: Can you please give me a small code example of that shutdown procedure? Thank you |
|
April 4, 2005: 3:30 pm
@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? |
 Campbell |
March 11, 2005: 10:40 am
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. |
Balıkçı