mvcc & row level locks in h2

[Nick Evgeniev]

Hi,

would someone explain me how row level locks are working in h2 in mvcc
mode? W/o mvcc option h2 uses table locks, obviously in XXI century
this isn't an option :). There are two controversial statements on h2
site regarding mvcc & row locks:

1. http://www.h2database.com/html/advanced.html#mvcc :
"If multiple connections concurrently try to update the same row, this
database fails fast: a concurrent update exception is thrown."

2. http://www.h2database.com/html/features.html#comparison :
"Row Level Locking Yes *9 Yes No Yes Yes
*9 H2 supports row level locks when using multi version concurrency."


suppose two two concurrent transactions wants to lock an entity for
further modification. with databases having row level locks but no
SELECT FOR UPDATE syntax you can do this using

UPDATE T SET ID = ID WHERE ID = <PARTICULAR ID>
SELECT * FROM T WHERE ID = <PARTICULAR ID>

statement. But in h2 (according to statement #1) second transaction
would fail with concurrent modification exception is it a feature?
bug? bug in docs?

what is expected behavior?

[Thomas Mueller]

Hi,

> 1. http://www.h2database.com/html/advanced.html#mvcc :
> "If multiple connections concurrently try to update the same row, this
> database fails fast: a concurrent update exception is thrown."

This is no longer the case. I will change the documentation to: "If
multiple connections concurrently try to update the same row, the
database waits until it can apply the change, but at most until the
lock timeout expires.". I hope it is understandable...

> bug in docs?

A bug in the documentation.

> what is expected behavior?

The second connection should be able to update (you may need to change
the lock timeout).

Regards,
Thomas

[Nick Evgeniev]

Hi,

Thank you very much :)  if mvcc is stable enough why not to make it default mode then?
I think this will dramatically increase chances to find remaining bugs :)

[Thomas Mueller]

Hi,

> if mvcc is stable enough why not to make it default
> mode then?

Because it's not stable :-)

> I think this will dramatically increase chances to find remaining bugs :)

I think the risk is too high at the moment. Once I feel it's enough
stable, I will create a new version (probably 1.2.x) that is marked as
'beta' - and enable it by default in this version. But currently I
don't plan to do that - the new storage format is more important I
believe.

Regards,
Thomas