| Bug #35812 | Falcon does not handle check clause well in repeatable read | ||
|---|---|---|---|
| Submitted: | 3 Apr 2008 17:45 | Modified: | 23 Apr 2008 21:45 |
| Reporter: | Ann Harrison | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Apr 2008 23:21]
MySQL Verification Team
Thank you for the bug report.
[23 Apr 2008 21:45]
Ann Harrison
This is a difference in behavior between InnoDB and Falcon. Both behaviors are correct according to the standard, which says that concurrent transactions should be serializable but does not define the order in which they should appear to have run. Falcon orders the transactions as if the view update happened before the table update, regardless of the order of commits. InnoDB handles the transactions as if the first to commit ran first, which is required to support statement-based replication. If at some point Falcon tries to support statement-based replication, this is one more issue we'll need to address. At the moment, Falcon's behavior gives better concurrency and is correct. (And yes, I did check with Peter Gulutzan about my interpretation of the rules).
Description: If a view of one table has a condition that excludes values from another table and a check clause, before new values can be added to the view, MySQL must verify that the new values are not present in the second table. In repeatable read mode, Falcon does not see rows in the second table that are inserted but not committed, so it does not detect conflicting changes by concurrent transactions. The example below demonstrates the problem. InnoDB apparently does a select for update when evaluating the check clause (? just a guess) so it does detect the conflict and rejects the change to the view. Note that if the transaction that adds the value to the view ran and committed before the transaction that adds the value to the second table, both would succeed. From that perspective, I'm not sure this is even a bug - just a difference in behavior. How to repeat: T1: set global falcon_consistent_read = 1; set @@autocommit=0; use test drop table if exists t1,t2; drop view if exists v; create table t1 (s1 int) engine=falcon; create table t2 (s1 int) engine=falcon; create view v as select * from t1 where s1 not in (select s1 from t2) with check option; commit; T2: set @@autocommit=0; use test insert into t2 values (1); T1: insert into v values (1); commit; T2: commit; InnoDB does not allow this -- "with check option" error. Falcon allows this -- no error. InnoDB is right; Falcon is wrong; this is a bug. The question is: is it a "Falcon consistent read" bug? Suggested fix: Figure out how InnoDB blocks the conflicting insert and decide whether to emulate that behavior when consistent read is off.