Isn't the purpose of MVCC to avoid locking rows?
On Aug 13, 2010, at 7:19 AM, Paul Hilliar wrote:
> H2 is absolutely great and its performance and usability have
> 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
>
> --
> You received this message because you are subscribed to the Google Groups "H2
> Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
