hsqldb continually restarts, potentially corrupting data

There is a bug in JIRA 2.4.1, where the hsqldb database bundled with the Standalone distribution gets occasionally restarted in memory, as queries come and go. This can potentially result in data loss. Gory technical details to follow.

This issue arose in 2.4 when we had to switch from commons-dbcp 1.0 to JOTM (it being transaction-aware - and transactions being forced on us by an Ofbiz upgrade..). JOTM uses xapool, which expires open connections after a default period of 10 minutes. Thus, despite the minimum size of the pool being 2, these connections would expire and close after 10 minutes. When the last connection closed, the hsqldb Database object finds itself with no Java References pointing to it, so is marked for garbage collection.

However! The shutdown-and-clean-up logic for hsqldb's Database object is in a finalize() method, so it is not executed immediately the last connection is closed. If the JVM is lazy about garbage collection, then an unused Database object could be in a pending-shutdown state for a long time. If during this time, another request comes in, a new Database object is created, which starts writing to the log file. Then the JVM's garbage collector kicks in, triggering the finalize() method on the old Database object. Being unaware of the new Database, the old one will go through its shutdown process, which involves optimizing the .script file by rewriting it as a series of INSERTs. The new (current) Database is now completely inconsistent wrt. the .script file, and data loss results.

The solution we've implemented in 2.5 is to keep a guardian connection open to hsql, independent of the connection pool. This stops hsql from ever being restarted and garbage-collected.