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

If you use the "locks" transaction model and commit after each SELECT you will get synchronised results. The writers and readers work exclusively of each other.

Fred 

On Sat, Oct 29, 2022, at 14:44, Mark Raynsford via Hsqldb-user wrote:
> 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
>
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user