Rollback not working correctly when using MVCC and page store disabled

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

Create a master table with two detail tables (child1 and child2). child1
references master with ON DELETE CASCADE, whereas child2 references master
without ON DELETE CASCADE. Try to delete a master row when rows in child2
exist, catch the Referential integrity constraint violation and perform a
ROLLBACK on this connection. Open another connection and select the child
rows in child1 for this master row you tried to delete ("SELECT * FROM
child1 WHERE parent=<master-row-id>").

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

You'll see that you don't get all children for this master row but only a
few rows. The reason is that the index which is being used for the foreign
key relation in child1 is corrupt. I guess that the index became corrupted
during ROLLBACK.

If you perform a full select on child1 without using the index ("SELECT *
FROM child1") you'll get all child1 rows, so the table data itself is not
corrupted. If you close and reopen the database, the index gets
reconstructed and you'll see all children again.

See the attached test case for details.

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

H2 1.1.118 (stable)
Windows XP SP 2 with NTFS
Sun JRE 1.6.0-16

Do you know a workaround?

No, not during runtime. Closing an reopening the database helps, but we
cannot do that on every ROLLBACK.

How important/urgent is the problem for you?

Very important. Our application is in production.
We cannot update to 1.2.124 because it is a beta and you said that it is
not recommended for production use because of the new page store (true?!?).
We need a fix for the stable release.

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

Defect.

Please provide any additional information below.

This happens only when using MVCC=TRUE.

This issue might be related to issue 150 ("MVCC: RuntimeException:
Unexpected code path in simple SELECT"). Before the RuntimeException
occurred, our (Hibernate) application tried to delete a master row in the
same fashion and performed a ROLLBACK. The application got a new connection
(C3P0 connection pool) and worked with the corrupted index data.

Original issue reported on code.google.com by dmoeb...@gmx.net on 14 Jan 2010 at 10:21

Attachments:

• MVCCIndexCorruptedTest.java

[GoogleCodeExporter]

This error does not occur in 1.2.126, if the database is created from scratch. 
If we
create the database with 1.1.118, then update to 1.2.126, the error still shows.

Original comment by dmoeb...@gmx.net on 14 Jan 2010 at 10:38

[GoogleCodeExporter]

This error also occurs in 1.2.126 when using MVCC=TRUE;PAGE_STORE=FALSE with a 
newly
created database.

Original comment by dmoeb...@gmx.net on 14 Jan 2010 at 12:13

[GoogleCodeExporter]

I will try to fix it (I didn't have time yet to reproduce it).
It's strange that it's not reproducible without the page store.

Original comment by thomas.t...@gmail.com on 15 Jan 2010 at 8:19

• Changed state: Accepted

[GoogleCodeExporter]

Hi,

I found the problem, it is fixed in the trunk.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 23 Jan 2010 at 6:20

[GoogleCodeExporter]

Great! Thanks a lot.

To backport this fix to 1.1.118, I guess I have to add this line

  row.setVersion(row.getVersion() - 1);

to UndoLogRecord.java somewhere. I'll try this for myself. It doesn't seem hard 
to do.

Btw.: I think your new testcase TestTransaction.testRollback() is missing a
rollback() in the second part, where you delete from master:

  try {
    stat.execute("delete from master");
    fail();
  } catch (SQLException ex) {
    // ok
    rollback(); // <-- this one is missing here
  }

Otherwise you'll don't test the original error.

Original comment by dmoeb...@gmx.net on 25 Jan 2010 at 9:07

[GoogleCodeExporter]

Sorry, Thomas, this bug is not yet fixed. :-(

If you change your testcase TestTransaction.testRollback() so that it inserts 3 
rows
instead of 2, you'll see that you don't get the expected result. I attached a 
patch
file to show you how I modified your testcase.

Note that I added conn.rollback() in the catch clause, but this is not 
required: the
bug shows up with or without rollback.

Original comment by dmoeb...@gmx.net on 29 Jan 2010 at 4:54

Attachments:

• issue158-testcase.patch

[GoogleCodeExporter]

I tried to work around the problem, but I'm afraid I will not be able to fix 
this
problem, I'm sorry. When using MVCC, it looks like the page store is the only
possible solution.

Original comment by thomas.t...@gmail.com on 30 Jan 2010 at 11:01

• Changed title: Rollback not working correctly when using MVCC and page store disabled

[GoogleCodeExporter]

Could you please elaborate on this? What's the difference between page store 
and old
file store so that the problem is solved in the first case and cannot be solved 
in
the latter case?

Original comment by dmoeb...@gmx.net on 1 Feb 2010 at 8:33

[GoogleCodeExporter]

> Could you please elaborate on this?

I'm sorry it's a bit complicated. I would have to explain a lot; I'm afraid I 
don't
have the time to do that.

Original comment by thomas.t...@gmail.com on 6 Feb 2010 at 4:53

[GoogleCodeExporter]

I'm afraid I will not be able to fix it with version 1.1.x (page store 
disabled). I
think it doesn't make sense to keep the issue open because it works with the 
current
release (page store enabled).

Original comment by thomas.t...@gmail.com on 20 Feb 2010 at 9:49

• Changed state: Fixed