MySQL Bugs: #41391: falcon.falcon_bug_22211 fails due to seeing duplicate entry

Bug #41391	falcon.falcon_bug_22211 fails due to seeing duplicate entry
Submitted:	11 Dec 2008 10:57	Modified:	15 May 2009 13:39
Reporter:	Olav Sandstå	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.9-alpha,6.0.10-alpha	OS:	Any
Assigned to:	Olav Sandstå	CPU Architecture:	Any
Tags:	F_ISOLATION

Description:
falcon.falcon_bug_22211 fails with the following error:

@@ -59,6 +59,7 @@
 SELECT * FROM t1;
 a
 10
+10
 # Switch to connection 1 (REPEATABLE READ)
 # reap INSERT (10) - This should get an update conflict.
 ERROR 23000: Duplicate entry '10' for key 'PRIMARY'

This seems like an invalid result from a select query, possibly that some data that should not be visible has been included in the result.

How to repeat:
Can probably be repeated by running falcon.falcon_bug_22211 on a falcon tree that includes the code for the new dependency manager.

Suggested fix:
Test should not produce invalid results like this.

While re-running the test in order to try to reproduce the failure, after about 35000 runs, the test failed with the same symptom but on a different place in the test.

Output from test failure:

falcon.falcon_bug_22211                  [ fail ]
        Test ended at 2009-02-27 11:11:19

CURRENT_TEST: falcon.falcon_bug_22211
mysqltest: At line 102: query 'reap' failed: 1062: Duplicate entry '100' for key
 'PRIMARY'

The result from queries just before the failure was:
< snip >
SELECT * FROM t1;
a
100
# Switch to connection 1 (REPEATABLE READ)
START TRANSACTION;
SELECT * FROM t1;
a
0
# send INSERT (100) - This should SUCCEED after 100 is rolled back.
INSERT INTO t1 VALUES(100);;
# Switch to connection default (REPEATABLE READ)
# Rollback the value 100
ROLLBACK TO SAVEPOINT sp1;
# Commit the value 10
COMMIT;
SELECT * FROM t1;
a
10
# Switch to connection 1 (REPEATABLE READ)
# reap - INSERT (100)

Possibly related to Bug#42189.  We need to look closely at the waitForTransaction cycle to see if there are any holes.

In an attempt to reproduce this bug a different error on the same place in the test occured:

CURRENT_TEST: falcon.falcon_bug_22211
mysqltest: At line 67: query 'reap' succeeded - should have failed with sqlstate 23000...

The result from queries just before the failure was:
< snip >
SELECT * FROM t1;
a
100
# Switch to connection 1 (REPEATABLE READ)
START TRANSACTION;
SELECT * FROM t1;
a
0
# send INSERT (10) - This should block unitl 10 is committed.
INSERT INTO t1 VALUES(10);;
# Switch to connection default (REPEATABLE READ)
# Rollback the value 100
ROLLBACK TO SAVEPOINT sp1;
# Commit the value 10
COMMIT;
SELECT * FROM t1;
a
10
# Switch to connection 1 (REPEATABLE READ)
# reap INSERT (10) - This should get an update conflict.

This time it seems like we are actually able to successfully insert "a duplicate value into a primary key".

Not reproducible on 6.0-falcon-team after 6900 runs on a Xeon 2.5 Ghz Linux 64bit.

Philip reported that this problem cannot be repeated after the changes to create a TransactionState object and the addition of the CycleManager to protect doomed records until after all temporary stack pointers to them have gone away.

The new transaction state object and the cycle manager are both pushed into mysql-6.0-falcon-team some weeks ago.

Fix merged into mysql-6.0 today.

Fix is in 6.0.11

A note have been added to the 6.0.11 changelog: 

The Falcon CycleManager has been updated, which addresses a number of issues when examining records in various transaction states and their visisbility/isolation in relation to other threads.