Row level locking
gügi
H2 is a very good database: everything is so small, fast and easy.
But the lacking of row level locking is a killer for our application.
I noticed that not so many people are requesting this feature,
although it is the key for a performant multi connection system as
ours. We hope that row level locking will be supported in the next
release of H2. I hope that my prayers are answered. Does anybody know
when? Thanks a lot. Gügi
maro
I'm also eagerly waiting for this feature!
Best,
Manfred
Thomas Mueller
As multi-version concurrency (MVCC), row level locking doesn't solve all problems. For example, for large updates or deletes it is still required to lock the whole table. Another problem is that serializable isolation level is hard to achieve with row level locking. Did you have a look at the MVCC features of H2? What are the advantages of row level locking over MVCC in your case?
Regards,
Thomas
gügi
MVCC was a great hint. It seems to come up with our requirements.
I was not aware of that feature. We now will make further tests and I
will report the results here.
Many thanks for your help. Regards, Gügi alias Jürg
On Feb 14, 7:07 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
>
> - Show quoted text -
gügi
I made some simple tests (with an SQL terminal) with the MVCC feature.
What is good now:
I can update 2 different rows at the same time (with 2 connections) or
insert a new one while updating another one, as expected.
What I did not expect:
When I am updating a certain row with key=1 on connection 1 (no commit
yet) and then make a "select * from tablename" on
connection 2, that row with key=1 is not visible (readable) anymore on
connection 2, until I make a commit on connection 1, then I can
read the row with key=1 again, and with the new values. It seems, that
as long as an uncommitted update is running on a certain row,
this row disappears for all other connections until the update gets
committed.
I was expecting to see the row with key=1 with the old values as long
as it is not committed (isolation).
Do I have to change the LOCK_MODE?
Thanks. Jürg
Thomas Mueller
Sorry I can't reproduce the problem. This is what I have tested (using
the H2 Console):
open connection 1 to jdbc:h2:~/test;MVCC=TRUE
create table test(id int primary key, name varchar);
insert into test values(1, 'Hello World');
set autocommit false;
update test set name='Hallo Welt' where id=1;
select * from test;
-- result: 1 Hallo Welt
open connection 2 (same url)
select * from test;
-- result: 1 Hello World
Could you try that and tell me what the result is? Maybe your test
case is a bit different?
Regards,
Thomas
gügi
Thanks for your support. I think we are both German speaking people -
right?
I could exactly reproduce your test case, and it was working as you
described above.
So I was going to work out the difference between our 2 test cases,
and I think I found the problem.
I use a standalone server with the URL
jdbc:h2:tcp://localhost:9101/test;MVCC=true
I start the server with: java -Xms256m -Xmx1024m -cp h2.jar
org.h2.tools.Server -tcp -tcpPort 9101
When I repeat your test case using this standalone server, then your
test case does not work either.
The row is not visible in connection 2 until I make a commit in
connection 1.
That would point to a problem in H2. I hope you get now the same
result as I do.
Regards Jürg
On Feb 19, 10:29 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
Kevin Conner
Thomas Mueller wrote:
> Sorry I can't reproduce the problem. This is what I have tested (using
> the H2 Console):
I have come across what I feel may be a similar problem, we are also
using MVCC and read committed concurrently.
We have a query which we expect to return a number of rows (four in this
instance), each representing a different object mapped through
hibernate. Most of the time this is successful but occasionally we are
seeing missing objects. When I tracked this down it appears that the
query is still returning four rows, the only issue is that it is
returning the *same* row four times.
This problem coincides with other threads updating these rows.
I have downloaded the source for h2 and plan on going through it this
weekend.
Just to let you know,
Kev
Thomas Mueller
> query is still returning four rows, the only issue is that it is
> returning the *same* row four times.
I have a reproducible test case now. This will be fixed in the next
release. I suggest not to use the MVCC feature until then, sorry!
Regards,
Thomas
Kevin Conner
Thomas Mueller wrote:
> I have a reproducible test case now. This will be fixed in the next
> release. I suggest not to use the MVCC feature until then, sorry!
Thanks very much for this. I got so far tracking this down, going
through the LocalResult/TableFilter/MultiVersionCursor but had still to
identify the issue.
Have you decided on a date for the next release? Could this issue be
something I could help you out with?
Thanks,
Kev
Thomas Mueller
> Have you decided on a date for the next release?
Releases are usually every 2-4 weeks.
> Could this issue be something I could help you out with?
Of course! You could try it out in the trunk at
http://code.google.com/p/h2database/source/checkout
> difference in the MVCC feature between an embedded and a standalone server.
I don't think the difference is the access mode. But probably it has
to do who opened the database first:
Currently the MVCC mode if set when the first connection opens the
database, and stays like this until the database is closed. If the
first connection opens the database with MVCC mode
(jdbc:h2:test;MVCC=true), then it is switched on (for this and all
following connections). If the first connection opens the database
without MVCC (jdbc:h2:test), then it is switched off for all following
connections as well. The setting stays until the database is closed
(until the last connection closes).
> I think we are both German speaking people - right?
Yes, even Swiss German ;-)
Regards,
Thomas
Kevin Conner
I have been going through this issue over the last few days but it looks
as if you may already have addressed it, is this the case? I certainly
can't reproduce the errors I was previously seeing with revision 529.
Thanks,
Kev
P.S. Do you have a JIRA repository (or similar) with current issues?
Thomas Mueller
> I have been going through this issue over the last few days but it looks
> as if you may already have addressed it, is this the case?
It will be fixed in the next release.
> P.S. Do you have a JIRA repository (or similar) with current issues?
Yes: http://code.google.com/p/h2database/issues/list
There are not that many issues are most issues are fixed immediately
when found or reported. In this case they are in the change log:
http://www.h2database.com/html/changelog.html
(the duplicate row issue is also in the change log: "When using
multi-version concurrency (MVCC=TRUE), duplicate rows could appear in
the result set when running queries with uncommitted changes in the
same session.").
Regards,
Thomas
gügi
the bug I reported in my reply on Feb 20 6:22p (missing rows during
updates) with version 1.0.64 has disappeared in version 1.0.66.
MVCC as a replcement feature for Row Level Locking (RLL) is nothing
for us. The code changes would produce too much work. We still hope
that RLL will be implemented in H2 in the near future. It is a feature
that most databases have. Do you still have not fixed the time plan
for RLL?
Regards Jürg
On Mar 6, 8:33 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> Do you still have not fixed the time plan
> for RLL?
Sorry, no, there is no fixed time plan. I agree row level locking is
important, but at the moment I can not promise when it will be
implemented.
Regards,
Thomas