MySQL Bugs: #28142: Falcon: repeatable read transaction can see another's committed result

Bug #28142	Falcon: repeatable read transaction can see another's committed result
Submitted:	27 Apr 2007 18:06	Modified:	3 Nov 2008 13:53
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.0-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	John Embretsen	CPU Architecture:	Any
Tags:	F_ISOLATION

Description:
I create a Falcon table.
I start two mysql clients.
I start REPEATABLE READ transactions on both clients.
On the first client, I insert a value, without committing.
On the second client, I select.
On the first client, I commit.
On the second client, I update and then select again.
The select results includes the row that the first client inserted.
That's not appropriate for this isolation level.

Compare:
Bug #17228 REPEATABLE READ doesn't work correctly in InnoDB tables?

How to repeat:
Start two mysql clients. Call them T1 and T2.

On T1, say:
create table t (s1 int, s2 varchar(50)) engine=falcon;
set @@autocommit=0;
set transaction isolation level repeatable read;
start transaction;
insert into t values (1,'inserted by T1');

On T2, say:
set @@autocommit=0;
set transaction isolation level repeatable read;
start transaction;
select * from t;

On T1, say:
commit;

On T2, say:
update t set s1 = 2;
select * from t;

The result of the final select looks like this:

mysql> select * from t;
+------+----------------+
| s1   | s2             |
+------+----------------+
|    2 | inserted by T1 |
+------+----------------+
1 row in set (0.01 sec)

This bug was opened to track differences between the two different ways of doing repeatable read.  In Falcon, since this bug was opened, we have added a FALCON_CONSISTENT_READ parameter to change the way repeatable-read is done.  Please verify that the behavior of  SET FALCON_CONSISTENT_READ ON is what this bug requests.  Then this can be closed.

Verified with revision klewis@mysql.com-20081103003405-j9qqx5p3e0ptgl3d (6.0.8-alpha_bzr) of the mysql-6.0-falcon-team branch that current default behavior in Falcon is that the result of the final select in the above example is empty, which is what the bug reporter seems to expect.

Default is FALCON_CONSISTENT_READ = ON.
If I set FALCON_CONSISTENT_READ = OFF, I see the old (InnoDB-like) behavior as described by the bug reporter.

Related tests covering this configuration parameter:
  falcon.falcon_bug_29151
  falcon.falcon_bug_29151_A
  falcon.falcon_bug_29151_B
  falcon.falcon_bug_29151_C

For further details about this setting, see e.g. WorkLog#3523, WorkLog#3715, Bug#34092, Bug#29151.