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å
Tags: F_ISOLATION
Triage: Triaged: D2 (Serious)

[11 Dec 2008 10:57] Olav Sandstå
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.
[1 Mar 2009 18:04] Olav Sandstå
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)
[14 Mar 2009 11:43] Kevin Lewis
Possibly related to Bug#42189.  We need to look closely at the waitForTransaction cycle to see if there are any holes.
[17 Mar 2009 10:40] Olav Sandstå
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".
[28 Mar 2009 10:44] Philip Stoev
Not reproducible on 6.0-falcon-team after 6900 runs on a Xeon 2.5 Ghz Linux 64bit.
[30 Mar 2009 21:37] Kevin Lewis
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.
[15 Apr 2009 17:18] Olav Sandstå
The new transaction state object and the cycle manager are both pushed into mysql-6.0-falcon-team some weeks ago.
[15 Apr 2009 17:20] Olav Sandstå
Fix merged into mysql-6.0 today.
[15 Apr 2009 17:43] Hakan Küçükyılmaz
Fix is in 6.0.11
[15 May 2009 13:39] MC Brown
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.