MVCC and waiting for a row level lock until the competing transaction has committed
Paul Hilliar
absolutely transformed several aspects of the project that I am
working on.
However I am struggling to understand a behaviour to do with locking
when MVCC mode is enabled (we need row level locking so have enabled
MVCC).
The test below illustrates the scenario (tested against latest stable
build : h2-1.2.139.jar).
Auto-commit is off, lock timeout is set to a minute
Two threads are competing to insert a row.
The first thread inserts the row but waits before committing the
transaction
The second thread inserts the row and....
When MVCC mode is off, the second thread waits for the first thread to
commit. You can see this because the 'T1 Committing' message happens
before the exception.
When MVCC mode is on, the second thread fails immediately. You can
see this because the exception happens, then afterwards, the 'T1
Committing' message happens.
The ideal behaviour that I would like is to be able to use row level
locking but to have the second thread wait. Does anyone have a
suggestion please?
Best regards
Paul.
==========
import java.sql.Connection;
import java.text.DateFormat;
import java.util.Date;
import javax.sql.DataSource;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
public class LockingTestHarness {
public static void main(String[] args) throws Exception{
final DataSource ds = new SimpleDriverDataSource(new
org.h2.Driver(), "jdbc:h2:mem:test_db;MVCC=TRUE", "sa", "");
ds.getConnection().createStatement().execute("create table TEST
(ID integer primary key)");
ds.getConnection().createStatement().execute("SET
DEFAULT_LOCK_TIMEOUT 60000");
Runnable r = new Runnable() {
public void run() {
try {
Connection c1 = ds.getConnection();
c1.setAutoCommit(false);
String sql = "insert into TEST (ID) values (1)";
log(sql);
c1.createStatement().execute(sql);
log("Inserted row - now sleeping before commit");
Thread.sleep(10 * 1000); //sleep for 10 seconds
log("Committing");
c1.commit();
}
catch (Exception e) {
log("Exception " + e);
e.printStackTrace();
}
}
};
new Thread(r, "T1").start();
Thread.sleep(1000);
new Thread(r, "T2").start();
Thread.sleep(1000 * 1000);
}
static void log(String str) {
System.out.println(DateFormat.getTimeInstance(DateFormat.MEDIUM).format(new
Date()) + " " +
Thread.currentThread().getName() + " " +
str);
}
The test ran with the third party jars:
commons logging
h2-1.2.139.jar
commons-logging-1.1.jar
org.springframework.core-3.0.2.RELEASE.jar
org.springframework.jdbc-3.0.2.RELEASE.jar
James Gregurich
> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
Paul Hilliar
table level locking
Rami Ojares
Also the first statement on h2's website reads
"The MVCC feature allows higher concurrency than using (table level or
row level) locks."
And from this sentence one can deduce that mvcc is not the same as using
row level locks.
- rami
Paul Hilliar
H2
Row Level Locking Yes *9
*9 When using MVCC (multi version concurrency).
...and from this sentence one can deduce that row level locking is
only supported when running mvcc
So which is it guys? I am neither stupid or unwilling to read
documentation but I can't work it out.
Rami Ojares
I quess Thomas will answer this in his next batch of emails
- rami
Paul Hilliar
when you use MVCC. If you take MVCC out of the connection string then
the second thread waits for the first one to commit. Leave it in and
you get both of the threads inserting with no delay.
public class RowLevelLockingTestHarness {
public static void main(String[] args) throws Exception{
final DataSource ds = new SimpleDriverDataSource(new
ds.getConnection().createStatement().execute("create table TEST
(ID integer primary key)");
ds.getConnection().createStatement().execute("SET
new LockingTest(ds, 1).start();
new LockingTest(ds, 2).start();
Thread.sleep(1000 * 1000);
}
static class LockingTest extends Thread {
DataSource ds;
private int rowId;
LockingTest(DataSource ds, int rowId) {
setName("Insert " + rowId);
this.ds = ds;
this.rowId = rowId;
}
@Override
try {
Connection c1 = ds.getConnection();
c1.setAutoCommit(false);
c1.createStatement().execute("insert into TEST (ID) values (" +
rowId + ")");
log(" Inserted " + rowId);
Thread.sleep(10 * 1000);
log(" Committing");
c1.commit();
log(" Commit complete");
}
catch (Exception e) {
e.printStackTrace();
Paul Hilliar
present any more
http://groups.google.com/group/h2-database/browse_thread/thread/56a0b36cacd33a1e
TM: > This is no longer the case. I will change
TM: > the documentation to: "If multiple connections
TM: > concurrently try to update the same row, the
TM: > database waits until it can apply the change,
TM: > but at most until the lock timeout
TM: > expires.". I hope it is understandable...
So all the more confusing that my initial test case seems to have the
fail fast behaviour
Jesse Long
Hi Paul,
By default, H2 only allows one statement to be executed on the database
at a time. It's not possible for the second transaction to wait for the
first transaction's completion, as this would require multiple
concurrent statements to be executed.
So:
1: tx1: insert row
2: tx2: insert row
3: tx1: commit
At line 2, tx2 should wait for tx1 to commit, but it cant, because tx1
cannot execute the statement on line 3 until the statement on line 2 is
completed (one statement at a time).
Without MVCC, you get a lock timeout when tx2 waits for the lock (why, I
dont know - it's not like anything can actually release the lock, I
think you should get the exception immediately, without even a wait. Oh
well, what's a few milliseconds between friends).
With MVCC, you get a unique key violation. I think this is wrong, since
there is no such value in the table, at least no such value COMMITTED to
the table. The problem is the same however, tx2 could not possible wait
for the completion of tx1. I would say an immediate lock timeout
exception, or concurrency problem exception would do here.
H2 can be configured to allow concurrent statements, by using the
MULTI_THREADED=TRUE setting, but this is marked experimental (although
Thomas has indicated it is much more tested now), and more importantly,
you cant use MULTI_THREADED=TRUE with MVCC.
Cheers,
Jesse
Paul Hilliar
because the database is single threaded, the second thread should not
wait because that statement cannot possibly acquire the lock.
So is there a way to allow multiple threading and row level locking in
H2?
When I run the same harness against Derby / Oracle / SQL server I do
get the desired result from the two test classes.
But H2 is so much easier to deal with than those - I would much rather
stick with it.
Paul Hilliar
still get table not row locks
Jesse Long
does allow the second statement to happen while one is locked. So, dont
take my word for it on the behaviour. Will have to do more research....
Cheers,
Jesse
Thomas Mueller
>> And in this post Thomas says that the fail-fast behaviour isn't
>> present any more
It looks like a bug. Unfortunately I only fixed the 'update' case, and
not the 'insert' case. So trying to update the same row concurrently
will not fail immediately, but trying to insert two rows with the same
key does currently fail immediately (before the first transaction is
committed).
I will try to fix this problem for the next release (probably this weekend).
Regards,
Thomas
James Gregurich
http://en.wikipedia.org/wiki/Multiversion_concurrency_control
As to exactly how H2 implements and uses MVCC, I can't comment on that. I don't know the details yet.
-James
On Aug 16, 2010, at 3:59 AM, Paul Hilliar wrote:
> I thought the purpose of MVCC was to give you row level locking not
> table level locking
>
Dario Fassi
MVCC is a concurrency strategy to minimize "locks" (and deadlocks) with a the gold rule in which "writers" don't block "readers" , or anti-lock pattern.
At the same moment you can have many writers inserting or updating a row with the same primary key and every one see their own version of that row, in the mean while any reader still see the last committed version without blocking.
At commit time, MVCC require a fair ordering for serialization of pending commits with conflicts detection where some or all can get ordered committed and conflict's victims are rolled back.
For the case, two pending updates of the same row can be serialized but two pending inserts don't .
In MVCC the problem is to get a lock since MVCC is a database model, not an state. You must to use specific grammar like LOCK TABLE or SELECT ... WITH LOCK to get an concurrency contention effective & exclusive lock at row or table level ( it's a temporal
and scope limited suspension of MVCC).
I can't imagine how a db engine without parallel transaction processing capabilities (multi_threaded ++) would do MVCC.
In H2 terms , MVCC without MULTI_THREADED don´t seem to have much sense, you end locked on a row or blocked by serialized processing engine.
A database that I know has a reasonable MVCC implementation it's Postgresql, many others claim to be MVCC but in real use don't behave as if they were.
I hope help, regards.
Dario.
El 16/08/10 15:02, James Gregurich escribió:
Paul Hilliar
Paul Hilliar
Thanks very much for the fix
Paul.