Multi-version concurrency / duplicate primary key after rollback.

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. create a DB in embedded mode, open it with MVCC, create a table with one
primary key column (e.g. BIGINT) plus other columns
2. insert a row, e.g. with key=1 
INSERT INTO t1 VALUES(1, ...);
3. do the following:
MERGE INTO t1 VALUES(1, ...); // UPDATE
DELETE FROM t1 WHERE key=1;
ROLLBACK;

What is the expected output? What do you see instead?

Neither the update nor the delete should be visible.
However, the result is, that t1 now contains 2 identical rows, both with key=1.

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2 117, Linux, ext3

Do you know a workaround?

How important/urgent is the problem for you?
Very, as it violates the primary key condition.

In your view, is this a defect or a feature request?
Defect.

Please provide any additional information below.

Original issue reported on code.google.com by mb555...@gmail.com on 14 Aug 2009 at 11:45

[GoogleCodeExporter]

Here's a trace of the actual transactions. The actual output cannot be seen, but
lines 489-531 prove that there are actually now 2 rows in the table.

Original comment by mb555...@gmail.com on 14 Aug 2009 at 12:23

[GoogleCodeExporter]

Hi,

I can reproduce this problem now. Test case:

Class.forName("org.h2.Driver");
String url = "jdbc:h2:data/test;MVCC=TRUE";
DeleteDbFiles.execute("data", "test", true);

Connection conn = DriverManager.getConnection(url);
Statement stat = conn.createStatement();
stat.execute("create table test(id int primary key) as select 0");
conn.setAutoCommit(false);
stat.executeUpdate("update test set id = 0");
stat.executeUpdate("delete from test");
conn.rollback();

Connection conn2 = DriverManager.getConnection(url);
ResultSet rs = conn2.createStatement().executeQuery(
        "select * from test");
while (rs.next()) {
    System.out.println(rs.getString(1));
}

Original comment by thomas.t...@gmail.com on 15 Aug 2009 at 3:52

• Changed state: Accepted

[GoogleCodeExporter]

I have committed a simplified test case and the fix.

Original comment by thomas.t...@gmail.com on 16 Aug 2009 at 10:19

• Changed title: Multi-version concurrency / duplicate primary key after rollback.

[GoogleCodeExporter]

This should be fixed in version 1.1.118 (2009-09-04)

Original comment by thomas.t...@gmail.com on 4 Sep 2009 at 9:04

• Changed state: Fixed