MAX function does not return updated value until commit in SERIALIZABLE isolation level

[Venkat Pedapati]

Hi,

I'm using the latest version (1.4.200) in embedded memory mode and I've set the isolation level to SERIALIABLE. 

I've opened a single connection to the database in my JVM and I added a bunch of rows to a table. Let us say there is a column Column1 which is just an integer value. When I do:

SELECT max(Column1) FROM Table1

It does not return the up-to-date result, until I commit, even in the same connection which added the rows. When I use READ COMMITTED isolation level, however, I don't see this issue. The max value is accurate. 

Is there any caveat with how aggregate functions are computed in serializable isolation level? 

Thank you,

Venkat.

[Evgenij Ryazanov]

Hello.

I can reproduce this issue in presence of index on aggregated column with H2 1.4.200, but I can't reproduce this issue with H2 compiled from the current sources.

You can compile H2 from the current sources and test it in your application if you wish.

Note that H2 doesn't have true SERIALIZABLE level, it actually works like non-standard SNAPSHOT isolation level. All three read phenomena are prevented, but equivalence of sequential and parallel execution of transactions in not ensured.

[Venkat Pedapati]

> You can compile H2 from the current sources and test it in your application if you wish.

Thank you! I'll try this. But it is quite strange that recompiling same sources again would work in a different way. Could this be an intermittent bug that does not show up some times? 

In any case, would you agree it is a bug, if it happens?

 Venkat.

[Evgenij Ryazanov]

If you can reproduce this bug with H2 compiled from the current sources, please provide a complete standalone test case.

I can reproduce it with 1.4.200, but the same queries return correct results with current H2.