Database file modification

[Sylvain]

Hello,

I have a H2 database on a machine that is read by several java
processes. The file is open in SERIALIZED mode, mainly because it's
mainly read only (some update can be done manually from time to time
via org.h2.tools.Shell utility).

Everything works good, but I noticed some behavior I was not
expecting. Let's say two process A and B are accessing the same
database

1) A and B are started and open the database
2) A runs 'SELECT * FROM tableA', the query is very fast (a couple of
ms - tableA is empty)
4) B runs the query 'SELECT * FROM tableB' -> the .h2.db file is
modified
5) A runs the same query on tableA, the query takes around 700 ms.
6) If A runs the query again it's fast again

tableA and tableB are empty tables, and both processes use
Statement.executeQuery(). I understand that there is some
synchronization involved but I was not expecting any penalties (step
5) on read only request. Am I missing something? would it be better to
use the ";AUTO_SERVER=TRUE" ?

I don't have a test case, but it should be easy enough to provide one
if needed !

Best,
Sylvain.

[Sylvain]

I forgot to mention that I'm using the last version 1.3.157

[Sylvain]

I tried the AUTO_SERVER, I also set
System.setProperty("h2.bindAddress", "localhost"); and it works very
well. I'll probably go with this solution.

[Christian Peter]

Hi Sylvain,

if the connections all come from one computer, and they are not in the
same VM, then AUTO_SERVER is the best way to do this. AUTO_SERVER only
has problems if you access the database via different networks
(imagine a mobile user starting the server, and other users accessing
this server via the mobile connection, just to get connected to a
database located in the same network).

SERIALIZED is the best way to do a really file only database access,
eg. for databases located on pure network shares.

If you can provide a simple test case, I can have a look why the
select changes the database.

Regards

Christian

[Sylvain Archenault]

Hi Christian,

Thank you for your answer, I will use for now on the AUTO_SERVER mode.

I wrote a quick class to show the initial problem. The class will open
the file in SERIALIZED mode and run a select query every second and
will print the time taken by the executeQuery method. The output looks
like this:

9:22:14 AM Database last modified: 9:22:14 AM
9:22:14 AM Select query: 31
9:22:15 AM Database last modified: 9:22:14 AM
9:22:15 AM Select query: 0
9:22:16 AM Select query: 0
9:22:17 AM Select query: 0
/*...*/

Now I open a H2 shell:
C:\Eclipse\bug h2>java -cp *.jar org.h2.tools.Shell -url
"jdbc:h2:test;FILE_LOCK=SERIALIZED" -user sa

As soon as I run this command, the java program outputs something like :
9:25:10 AM Database last modified: 9:25:10 AM
9:25:10 AM Select query: 1032
9:25:12 AM Database last modified: 9:25:11 AM
9:25:12 AM Select query: 0
9:25:13 AM Select query: 0

If in the shell, I run "select * from tableB;", you will also see an
initial delay in the java output.

Let me know if you have any questions.

Sylvain

[Christian Peter]

Hi Sylvain,

thank you for the test case. Its not the select that causes the
modification, it's the connection from a new client. I'm sorry this is
currently by design. The SERIALIZED mode was never meant to be equal
in performance, so I think thats Ok.

Maybe this changes in the future, but currently we don't plan to
improve that area.

Regards

Christian

>  file_lock_serialized.zip
> 1KViewDownload

[Sylvain]

Hi Christian,

I agree on the fact that the connection of new client causes the
modification and this is fine.

But it seems to me that also "select" queries cause the file
modification. On my windows (and also on SunOs), the test case is
running. Then I run the h2shell tool, when it connects, the file is
modified and also every time I run a query (on table tableB).

Best,
Sylvain,

On Jul 9, 2:31 pm, Christian Peter <christian.peter...@googlemail.com>
wrote:

[Thomas Mueller]

Hi,

I have now implemented an experimental feature so that the database
file is only modified if there is anything to roll back, or when
writing. To test it, you need to use the trunk, and set the system
property "h2.modifyOnWrite". I didn't test it together with serialized
file locking yet. But it should speed up the serialized file lock.

This is committed in the trunk now.

Regards,
Thomas