MySQL Bugs: #22175: Mixing with InnoDB table leads to an anomaly

Bug #22175	Mixing with InnoDB table leads to an anomaly
Submitted:	9 Sep 2006 10:27	Modified:	26 May 2010 17:48
Reporter:	Georg Richter	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
  	 I create a Falcon table. I create an InnoDB table.
I create a trigger on the Falcon table which causes insert into the InnoDB table.
With transaction T1, I 'start transaction' and do an insert.
With transaction T2, I 'start transaction' and delete from both tables.
(Transaction T2 hangs on the second DELETE, the one on the InnoDB table.)
With transaction T1, I 'commit' and the transaction ends succesfully.
(Transaction T2 stops hanging and the second DELETE succeeds.)
With transaction T2, I 'commit' and the transaction ends successfully.
With transaction T1, I 'select' from the Falcon table ... I see the new row.
With transaction T1, I 'select' from the InnoDB table ... I see no rows.
Both tables should have the same row count.

How to repeat:
  	 /*
   Assume that mysqld has been started in the ordinary way.
   I start with ----transaction-isolation=read-uncommitted
   but that won't matter.

   Start mysql client twice.
   I'll call the first instance T1.
   I'll call the second instance T2.

*/

To start, on T1, say:
create table t1 (s1 int) engine=falcon;
create table t2 (s1 int) engine=innodb;
set @@autocommit = 0;
create trigger t1_bi before insert on t1 for each row insert into t2 values (new.s1);
insert into t1 values (5);
commit;
set transaction isolation level serializable;
start transaction;
insert into t1 values (6);

Then, on T2, say:
set @@autocommit = 0;
set transaction isolation level serializable;
start transaction;
delete from t1;
delete from t2;

Then, on T1, say:
commit;

Then, on T2, say:
commit;

Then, on T1, say:
select * from t1;
select * from t2;

Verified on Linux 32-bit change set 1.2295, 2006-09-15.
Test case is falcon_bug_220.test.

Regards, Hakan

This bug is also dependent on the implementation of Serializable transactions.
See 22151 & 22189.

The testcase inserts into both Falcon and Innodb table in a transaction, then concurrently deletes those records.  With a serializable transaction the deletes will wait on the inserts to be committed.  But Falcon does not wait.  So the current result is that there a re no records in the innodb table one record in the Falcon table.

Incidently, This kind of testcase will never work as expected using read-committed or repeatable-read isolation since InnoDB always serializes the updates, while Falcon will allow Concurrency.

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.

and;

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

I think we should close this bug since Falcon returns an error for Serializable transaction and the testcase, as currently written, has been working for some time.  Either that, or we rename it to "Falcon needs Serializable Transactions"

Needs a Serializable transaction level.