#1338 write_delay can lead to data-loss

[Paul Millar]

I believe I have traced a problem in HSQLDB v2.3.1 which is arguably
either poor documentation or a bug -- either way, I only discovered
what was happening by looking at the source-code.

While using v1.2.8 of HikariCP with HSQLDB, I noticed my application
(which makes changes to a database and exits quickly) was suffering
from data-loss: information added as committed transactions were
missing on subsequent runs.

The problem seems to be a combination of two things: v1.2.8 of
HikariCP has a 'shutdown' that does nothing and that HSQLDB, by
default, relies on an orderly shutdown to guarantee data integrity.

I should point out that newer versions of HikariCP shutdown method
close all open connections, which (along with 'shutdown=true' in the
jdbc connection string) eliminates the data-loss problem.

The HSQLDB documentation for WRITE DELAY states:

If the property is true, the default WRITE DELAY property of the
database is used, which is 500 milliseconds. If the property is
false, the WRITE DELAY is set to 0 seconds. The log is written to
file regardless of this property. The property controls the fsync
that forces the written log to be persisted to disk. The SQL
command for this property allows more precise control over the
property.

This strongly suggests that data is written into the OS' file-system
cache (VFS-layer, for Linux) after a COMMIT, and write_delay controls
the periodic calls to fsync. Calling fsync is meant to guard against
certain hardware failures (e.g., power-cut), if the data has been
written to the VFS layer then it will be written to disk after the
application closes (with extremely high likelihood, if not actually
guaranteed), so committed transactions are safe from software
failures.

In practise, org.hsqldb.scriptio.ScriptWriterBase shows that output is
written through a BufferedOutputStream. The forceSync method first
flushes this buffer prior to calling fsync(). This behaviour is not
at all described above.

There are two issues here:

1. It seems that HSQLDB requires applications to close any open
   Connection (and, perhaps shutdown the database) before
   guaranteeing transactions have been processed. This doesn't
   appear to be mentioned in HSQLDB documentation and IMHO isn't part
   of the JDBC spec.

2. The documentation for WRITE DELAY is misleading: it suggests that
   it is limited to delaying calls to fsync and neglects to mention
   that there is an internal buffer that is also flushed.

I cannot say whether the performance benefits outweighs the risks
associated with buffering the output in Java; I suspect this is a
decision that can only be made on an application-by-application bases.

If I may, I would suggest two actions:

a. update the WRITE DELAY documentation to better reflect current
behaviour,

b. consider adding an option that removes the write buffer so that
HSQLDB can write data on commit but continue to support delayed
calling fsync.

[Fred Toussi]

Thanks. The documentation should be revised to include a mention of the internal buffer. However, the name of the property, WRITE DELAY, does indicate the write to disk is delayed by up to the amount in milliseconds.

[Matheus da Silva Freire]

Hello to everyone.

I use the hsql in project in my company, and the version of hsqldb 2.3.2 and the write delay is working. I think this bug was corrected in this version