Lock not cleaned up when using file hsqldb url

[justone]

I whipped up this script to try out what persistence might look like in a babashka script:

#!/usr/bin/env bb

(require '[babashka.pods :as pods])
(pods/load-pod "pod-babashka-hsqldb")
(require '[pod.babashka.hsqldb :as sql])

(def db "jdbc:hsqldb:file:test.db;sql.syntax_mys=true")

(println "create")
(time (sql/execute! db ["create table if not exists entries ( id int not null auto_increment, name varchar(20) not null);"]))

(println "update")
(time (sql/execute! db ["insert into entries (name) values ('foo');"]))

(println "select")
(time (sql/execute! db ["select count(*) from entries"]))

It executes well and fast:

$ ./db.clj
create
"Elapsed time: 112.652498 msecs"
update
"Elapsed time: 11.302146 msecs"
select
"Elapsed time: 12.757723 msecs"
[{:C1 1}]

But if I run it again right away, the first statement takes a lot longer:

$ ./db.clj
create
"Elapsed time: 8268.376065 msecs"
update
"Elapsed time: 10.922549 msecs"
select
"Elapsed time: 10.109986 msecs"
[{:C1 2}]

It appears that the .lck file is not getting cleaned up by the pod process:

$ ls -d test.db.*
test.db.lck  test.db.log  test.db.properties  test.db.script  test.db.tmp

I wrote a version of the script that just uses a custom compiled babashka with hsqldb included, and it does not exhibit this behavior.

[justone]

Here's my non-pod version:

#!/home/nate/projects/babashka/bb

(require '[next.jdbc :as jdbc])

(def db "jdbc:hsqldb:file:test.db;sql.syntax_mys=true")

(println "create")
(time (jdbc/execute! db ["create table if not exists entries ( id int not null auto_increment, name varchar(20) not null);"]))

(println "update")
(time (jdbc/execute! db ["insert into entries (name) values ('foo');"]))

(println "select")
(time (jdbc/execute! db ["select count(*) from entries"]))

[borkdude]

@justone Thanks! I'll have to do some googling of what's the proper way to let HSQLDB release the lock. Maybe calling .destroy on the process that started it is too brutal. Any help is welcome.

[justone]

I found the shutdown flag by looking through the docs. This makes HSQLDB shutdown the database after every statement. Adding this (so the URL is now "jdbc:hsqldb:file:test.db;sql.syntax_mys=true;shutdown=true" makes the issue go away, at the cost of a little overhead:

$ ./db.clj
create
"Elapsed time: 201.496945 msecs"
update
"Elapsed time: 173.84929 msecs"
select
"Elapsed time: 172.589169 msecs"
[{:C1 14}]

Each statement takes 150-250ms now instead of 10-50, but no lock file is left.

[borkdude]

I think a shutdown operation for pods is appropriate here. Since you don't see this behavior when compiling with the hsqldb feature flag, I expect that hsqldb has some shutdown hook to automatically release the lock when the process exits. When a pod receives a shutdown message, it will just exit and babashka will wait for that process to have properly exited.

[justone]

Agreed.

I just found if I added this to the script, it cleans up the log:

(sql/execute! db ["shutdown"])

[borkdude]

This should be fixed with the newly released binaries and with the next version of babashka. I'll close the issue when the next bb is out.

[justone]

I just tried it out by compiling both master branches and it works wonderfully! Thanks!