MySQL Bugs: #22151: ROLLBACK fails with two interleaving transactions

Bug #22151	ROLLBACK fails with two interleaving transactions
Submitted:	9 Sep 2006 5:39	Modified:	24 Jan 2007 19:13
Reporter:	Georg Richter	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	SERIALIZABLE ISOLATION

Description:
see how to repeat

How to repeat:
Run the "T1:" statements on one connection.
Run the "T2:" statements on another connection.
Don't worry about any of the error messages.
The point is: the ROLLBACK doesn't cause an error.

T1:
set @@autocommit=0;
create database db62;
use db62;
drop table if exists x1;
create table x1 (x1 int primary key, x2 int) engine=jstar;
commit;
insert into x1 values (0,0),(1,1);
commit;
drop table if exists x1;
create table x1 (x1 int primary key, x2 int) engine=jstar;
start transaction;
set transaction isolation level serializable;
select * from x1;
insert into x1 values (0,0);

T2:
set @@autocommit=1;
use db62;
insert into x1 values (1,1);

T1:
insert into x1 values (1,2);

T2:
insert into x1 values (0,3);

T1:
update x1 set x1 = 1, x2 = 4;

T2:
update x1 set x1 = 0, x2 = 5;
insert into x1 values (0,6);

T1:
rollback;

T2:
insert into x1 values (0,6);
insert into x1 values (0,6);

T1:
select * from x1;

The result of the final SELECT, alas, is:

mysql> select * from x1;
+----+------+
| x1 | x2 |
+----+------+
| 0 | 0 |
| 1 | 1 |
+----+------+
2 rows in set (0.00 sec)

was mantis bug #148

Comment from JimStarkey:

 This can't be executed. The problem is that the T2 statement

    insert into x1 values (0,3);

blocks pending a commit or rollback of T1, hanging the test.

I simplified the test for this bug a bit. Test case is falcon_bug_148.test.

Looks like ROLLBACK does not work:
TEST                            RESULT
-------------------------------------------------------
falcon_bug_148                 [ fail ]

Errors are (from /home/hakan/work/mysql/mysql-5.1-falcon/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 40: query 'INSERT INTO t1 VALUES (1,2)' failed: 1062: Duplicate entry '1' for key 'PRIMARY'
(the last lines may be the most important ones)

Regards, Hakan

Added ORDER BY to falcon_bug_148.test.

Test passes most times. Some times (10%) it fails with

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_148                 [ fail ]

Errors are (from /home/hakan/work/mysql/mysql-5.1-falcon/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 40: query 'INSERT INTO t1 VALUES (1,2)' failed: 1062: Duplicate entry '1' for key 'PRIMARY'

Regards, Hakan

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

falcon_bug_148.test is still failing on Linux 32-bit:

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_148                 [ fail ]

Errors are (from /media/usbdisk/mysql/var/log/mysqltest-time) :
mysqltest: At line 40: query 'INSERT INTO t1 VALUES (1,2)' failed: 1062: Duplicate entry '1' for key 'PRIMARY'

Regards, Hakan

This bug tests the serializable isolatin level which Falcon does not currently support.  The only reason it fails is that Falcon allows a concurrent insert by connection 1 when the test expects it to wait for the default connection to rollback.  Support for Serializable transactions is planned for after the alpha release.

Added a warning to MySQL whenever a falcon table is accessed inside a Serializable or Read-Uncommitted transaction.  It looks like this;
mysql> show warnings;
+---------+------+---------------
| Level   | Code | Message                                                                        
+---------+------+---------------
| Warning | 1556 | Falcon does not support SERIALIZABLE ISOLATION, using REPEATABLE READ instead.
+---------+------+---------------
1 row in set (0.00 sec)

and;

| Warning | 1556 | Falcon does not support READ UNCOMMITTED ISOLATION, using REPEATABLE READ instead.

When re-running, the T2 statement:

      insert into x1 values (0,3);

correctly hangs pending commit or rollback of T1.  It is then impossible to continue with the script.

I think the problem here is that InnoDB automatically rolls
back after a deadlock.  That behavior differs from Falcon and
the ISO SQL standard.

The expected result depends on the behavior of InnoDB when
running in serializable mode.  Falcon doesn't have a serializable
mode at this point (first beta), but when it does, there's no
guarantee that it will serialize transactions the same way that
InnoDB does.  So, in the end the correct result may not be the
same as the result from InnoDB.  

Without serializable transactions, the sequence of actions
with Falcon is this:

T1:
set @@autocommit=0;
create database db62;
use db62;
drop table if exists x1;
create table x1 (x1 int primary key, x2 int) engine=jstar;
commit;
insert into x1 values (0,0),(1,1);
commit;
drop table if exists x1;
create table x1 (x1 int primary key, x2 int) engine=jstar;
start transaction;
set transaction isolation level serializable;
select * from x1;
insert into x1 values (0,0);

T2:
set @@autocommit=1;
use db62;
insert into x1 values (1,1);

/* the insert from transaction T2 succeeds in Falcon
   but fails in InnoDB in serializable mode.  InnoDB's
   transaction T1 holds a next key lock from the previous
   insert of (0,0).  Falcon does not hold next key locks. */

T1:
insert into x1 values (1,2);

/* In Falcon, this operation fails because transaction T2
   has inserted (1,1) in auto-commit mode.  In InnoDB in
   serializable mode, T2's insert failed, so this one 
   succeeds.  */

T2:
insert into x1 values (0,3);

/* In Falcon, this operation stalls, waiting for transaction
   T1 to commit or rollback.  Other operations sent to T2
   backup behind this one.  */

T1:
update x1 set x1 = 1, x2 = 4;

/* In both systems, this operation fails because there are
   two records in the table and the update tries to give both
   the same primary key.  In InnoDB, the records are (0,0) and
   (1,2), both uncommitted and created by transaction T1.  In 
   Falcon they are (0,0)uncommitted from T1 and (1,1) commited
   from T2.  */

T2:
update x1 set x1 = 0, x2 = 5;
insert into x1 values (0,6);

/* In Falcon, these operations stall behind the previous
   update which is waiting for t1. */

T1:
rollback;

/* At this point the sequence hits another bug, 34174... 
   When Jim fixes that one, we can determine exactly what
   Falcon should do on this. */

T2:
insert into x1 values (0,6);
insert into x1 values (0,6);

T1:
select * from x1;

It's reasonably important to change the engine=jstar to engine=falcon
when trying to reproduce this problem.  MySQL happily and silently
creates a MyISAM database if you ask for jstar.

The expected result depends on the behavior of InnoDB when
running in serializable mode.  Falcon doesn't have a serializable
mode at this point (first beta), but when it does, there's no
guarantee that it will serialize transactions the same way that
InnoDB does.  So, in the end the correct result may not be the
same as the result from InnoDB. 

At the moment, Falcon appears to run this test correctly and the
results are serializable.  The result of T1 is nothing - it rolls
back.  The result of T2 is the same as if it were the only transaction
running. 

The sequence of events with Falcon is this:

T1:
set @@autocommit=0;
create database db62;
use db62;
drop table if exists x1;
create table x1 (x1 int primary key, x2 int) engine=falcon;
commit;
insert into x1 values (0,0),(1,1);
commit;
drop table if exists x1;
create table x1 (x1 int primary key, x2 int) engine=falcon;
start transaction;
set transaction isolation level serializable;

/* The set transaction statement produces an error:
mysql> set transaction isolation level serializable;
ERROR 1568 (25001): Transaction isolation level can't be changed while a 
transaction is in progress
*/
select * from x1;
insert into x1 values (0,0);

T2:
set @@autocommit=1;
use db62;
insert into x1 values (1,1);

/* the insert from transaction T2 succeeds in Falcon
   but fails in InnoDB in serializable mode.  InnoDB's
   transaction T1 holds a next key lock from the previous
   insert of (0,0).  Falcon does not hold next key locks. */

T1:
insert into x1 values (1,2);

/* In Falcon, this operation fails because transaction T2
   has inserted (1,1) in auto-commit mode.  In InnoDB in
   serializable mode, T2's insert failed, so this one 
   succeeds.  */

T2:
insert into x1 values (0,3);

/* In Falcon, this insert stalls, waiting for transaction
   T1 to commit or rollback.  Other operations sent to T2
   backup behind this one.  */

T1:
update x1 set x1 = 1, x2 = 4;

/* In both systems, this operation fails because there are
   two records in the table and the update tries to give both
   the same primary key.  In InnoDB, the records are (0,0) and
   (1,2), both uncommitted and created by transaction T1.  In 
   Falcon they are (0,0)uncommitted from T1 and (1,1) commited
   from T2.  */

T2:
update x1 set x1 = 0, x2 = 5;
insert into x1 values (0,6);

/* In Falcon, these operations stall behind the previous
   insert which is waiting for T1 to complete. */

T1:
rollback;

/* At this point T1 rolls back, removing the record (0,0) and
   unblocking T2.  T2 inserts (0,3) successfully.  It then tries
   this statement: update x1 set x1 = 0, x2 = 5; which fails 
   because there are two records in x1 and they can't both have
   a primary key of zero.  The next statement: insert into x1 values (0,6);
   also fails with a primary key error.
*/

T2:
insert into x1 values (0,6);
insert into x1 values (0,6);

/* Both these statements fail because there is already a record (0,3)
   with 0 as a primary. */

T1:
select * from x1;

+----+------+
| x1 | x2   |
+----+------+
|  1 |    1 |
|  0 |    3 |
+----+------+
2 rows in set (0.00 sec)

/* both those rows were created by T1. The appear in creation
   order, which is storage order for Falcon.
*/