executeQuery of CALL NEXT VALUE for a simple sequence fails after brief connection idle time when LOCK_MODE=SERIALIZED

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create connection to new read/write database with FILE_LOCK=SERIALIZED
2. Create a new sequence
3. Wait at least 1 second
4. Get the next sequence value using executeQuery()

Test code to reproduce:

      Class.forName("org.h2.Driver");
      Connection con = DriverManager.getConnection("jdbc:h2:"
            + new File(System.getProperty("java.io.tmpdir"), "test").getAbsolutePath() + ";FILE_LOCK=SERIALIZED");

      Statement statement = con.createStatement();
      statement.executeUpdate("create sequence if not exists seq");
      statement.close();

      Thread.sleep(1000);

      statement = con.createStatement();
      ResultSet results = statement.executeQuery("call next value for seq");
      System.out.println(results.first() ? results.getLong(1) : "no result");
      results.close();
      statement.close();

What is the expected output? What do you see instead?
Expected: Successful query of next sequence value.
Actual: Getting this exception when querying next sequence value.

java.io.IOException: org.h2.jdbc.JdbcSQLException: The database is read only; 
SQL statement:
call next value for seq [90097-154]
...
Caused by: org.h2.jdbc.JdbcSQLException: The database is read only; SQL 
statement:
call next value for seq [90097-154]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:167)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.message.DbException.get(DbException.java:133)
    at org.h2.engine.Database.checkWritingAllowed(Database.java:1613)
    at org.h2.store.PageStore.logUndo(PageStore.java:990)
    at org.h2.index.PageDataLeaf.remove(PageDataLeaf.java:412)
    at org.h2.index.PageDataIndex.remove(PageDataIndex.java:324)
    at org.h2.table.RegularTable.removeRow(RegularTable.java:362)
    at org.h2.engine.Database.removeMeta(Database.java:718)
    at org.h2.engine.Database.update(Database.java:1340)
    at org.h2.schema.Sequence.flush(Sequence.java:127)
    at org.h2.schema.Sequence.getNext(Sequence.java:92)
    at org.h2.expression.SequenceValue.getValue(SequenceValue.java:30)
    at org.h2.command.dml.Call.query(Call.java:67)
    at org.h2.command.CommandContainer.query(CommandContainer.java:78)
    at org.h2.command.Command.executeQuery(Command.java:181)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:76)
...

Please use labels and text to provide additional information.

Running version 1.3.154 on Windows Vista

As a workaround, changing FILE_LOCK to NO, FILE, or SOCKET resolves the error. 
Removing the 1 second sleep (or reducing to about 300 milliseconds or less, to 
keepalive the connection) resolves the error. Obtaining a fresh connection 
after the sleep resolves the error. Using execute() instead of executeQuery() 
as shown below resolves the error.

      statement = con.createStatement();
      if (statement.execute("call next value for seq")) {
         ResultSet results = statement.getResultSet();
         System.out.println(results.first() ? results.getLong(1) : "no result");
         results.close();
      } else {
         System.out.println("execute failed");
      }
      statement.close();

Original issue reported on code.google.com by noslowerdna@gmail.com on 14 Apr 2011 at 10:40

[GoogleCodeExporter]

In my application I'll change executeQuery() to execute() for now, since it 
isn't really clear whether using executeQuery() to get the next sequence value 
should be allowed or not. In any case, the behavior of H2 in this particular 
scenario should be consistent (always succeed or always fail) regardless of 
locking scheme or connection idleness.

Original comment by noslowerdna@gmail.com on 14 Apr 2011 at 10:52

[GoogleCodeExporter]

Hi,

I'm afraid it's not so easy to fix this problem. One solution would be to 
automatically switch to 'write' mode, and re-run the query in this case. This 
would require a few changes in JDBC code. I'm not sure if this is a very 
important use case, so I will not implement it. Instead, I will document the 
limitation, and add a feature request to to roadmap.

Therefore, I will change the status to 'in roadmap' (because priorities are 
tracked there).

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 15 May 2011 at 4:00

• Changed state: InRoadmap

[GoogleCodeExporter]

Thanks for the update. That sounds good. I've switched to AUTO_SERVER=TRUE, so 
am no longer affected by this bug.

Original comment by noslowerdna@gmail.com on 16 May 2011 at 10:26

[GoogleCodeExporter]

Hello,

I use an ORM (Hibernate) to access the H2 database and can't figure out how to 
use execute() instead of executeQuery() then. Does any one know how to handle 
this situation ?
The SERIALIZED locking is the only way for us to allow concurrent access 
without installing a H2 server on the machine that hosts the database. We try 
to provide zero data administration for the users of our application : they 
only have to care about a directory where the data resides and most of the 
time, there is no concurrent access and they even don't know where data is 
stored.

If there is no workaround in this context, I would really appreciate that this 
issue is reopened. Thomas, maybe you could tell me what H2 code you would have 
changed and I could test a patch even if it is not commited in an official 
release ?

Thank you

Regards,
Laurent

Original comment by lrichard...@gmail.com on 10 Aug 2011 at 10:52

[GoogleCodeExporter]

See https://groups.google.com/d/msg/h2-database/igbPHNQjL4g/BewqmHdmA84J for a 
proposed patch that works fine on H2 1.3.158 in my use case. The switch to 
write mode is targeted only on the retrieving of the sequence value and the 
overhead applies only to SERIALIZED file_lock.

Original comment by lrichard...@gmail.com on 20 Aug 2011 at 9:51

[GoogleCodeExporter]

Hi,

In your patch, 'beforeWriting' is called too late (see where it is called 
otherwise). I have now implemented a solution however, it is already committed. 
Could you check if it works for you (you need to build H2 yourself using the 
source code in the trunk).

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 21 Aug 2011 at 7:02

[GoogleCodeExporter]

Thank you very very much Thomas.
I just built the trunk to test it in my application and everything works fine. 
This definitely makes H2 my database of choice to embed.
Your responsiveness is greatly appreciated.

Best regards,
Laurent

Original comment by lrichard...@gmail.com on 21 Aug 2011 at 7:34