What are the expected semantics for a read-only database with multiple processes?

Hello!

I have an application where (for reasons that aren't all that
interesting), I'm not able to have a server process mediating access to
a database. I noticed that the HSQLDB documentation makes various
references to it being compatible with multi-process use. In my case,
there'll be one writer process that is occasionally started and will
occasionally write some data to a database. There'll also be multiple
reader processes that attempt to read from the database somewhat more
frequently.

The following pair of test programs show behaviour that seems less than
ideal, however:

--8<--
public final class Writer
{
  private Writer()
  {

  }

  public static void main(
    final String[] args)
    throws Exception
  {
    try (var c =
           DriverManager.getConnection("jdbc:hsqldb:file:testdb", "SA","")) { 
      c.setAutoCommit(false);

      try (var st = c.prepareStatement("create cached table t (x integer not null)")) { 
        st.execute();
        c.commit();
      } catch (final SQLException e) {
        // OK
      }

      int x = 0;
      while (true) {
        try (var st = c.prepareStatement("insert into t (x) values
      (?)")) { st.setInt(1, x);
          st.execute();
          c.commit();
          System.out.println(x);
        }
        ++x;
        Thread.sleep(1_000L);
      }
    }
  }
}
--8<--


--8<--
public final class Reader
{
  private Reader()
  {

  }

  public static void main(
    final String[] args)
    throws Exception
  {
    try (var c =
           DriverManager.getConnection(
             "jdbc:hsqldb:file:testdb;readonly=true",
             "SA",
             "")) {
      c.setAutoCommit(false);

      while (true) {
        try (var st = c.prepareStatement(
          "select t.x from t order by t.x desc limit 1")) {
          try (var rs = st.executeQuery()) {
            rs.next();
            System.out.println(rs.getInt(1));
          }
        }
        Thread.sleep(1_000L);
      }
    }
  }
}
--8<--

The Writer program opens a database and starts adding rows with an
incrementing integer once per second. The output isn't surprising:

0
1
2
3
4
5
...

The Reader program opens a database in read-only mode, and
(inefficiently!) reads whatever is the highest integer in the
table right now, once per second. However, the output is surprising:

4
4
4
4
4
...

I suspect what it's doing is some kind of caching of the first result
of the query, and it's never invalidating the cache because it assumes
the database is read-only and therefore can't change. Is this correct?

Is there a way to get SQLite[0]-like behaviour where readers see
up-to-date information, and simply block if there are writers changing
the data at the time they try to read?

[0] https://www.sqlite.org

-- 
Mark Raynsford | https://www.io7m.com