MySQL Bugs: #22181: Query cache becomes obsolete with two interleaving transactions

Bug #22181	Query cache becomes obsolete with two interleaving transactions
Submitted:	9 Sep 2006 10:36	Modified:	26 Apr 2007 10:58
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Any (all)
Assigned to:	Jim Starkey	CPU Architecture:	Any

Description:
I have a transaction which SELECTs using MySQL's query cache.
On another connection, I add a row which would change the SELECT's result set.
I SELECT again, hoping for a different result set.
I get the same result set set as before.
With InnoDB, the result set would be different.

How to repeat:
/*
  I am running two instances of mysql client.
  I'll call the first one T1.
  I'll call the second one T2.
*/

On T1, do:
show variables like 'have_query_cache'; /* This should be 'YES'. */
set global query_cache_size = 200000; /* There should be no warnings. */
set @@autocommit=0;
use test;
drop database falcon4; /* No problem if not exists. */
create database falcon4;
use falcon4;
create table t2 (s1 int, s2 varchar(1000), key(s1)) engine=falcon;
insert into t2 values (1,repeat('a',1000)),(2,repeat('a',1000));
commit;
start transaction;
update t2 set s2 = 'w' where s1 = 10;
select sql_cache count(*) from t2 where s2 = 'w';

On T2, do:
commit;
use falcon4;
start transaction;
insert into t2 values (-1,'w'); /* Matches T1's SELECT condition. */
commit;

On T1, do:
select sql_cache count(*) from t2 where s2 = 'w';
commit;
select sql_cache count(*) from t2 where s2 = 'w';
/* The result is 0. With engine=innodb, the result would be 1. */

On T2, do:
select sql_cache count(*) from t2 where s1 = 'w';
/* The result is 0. With engine=innodb, the result would be 1. */

Have given Jim a fix for the bug that he will push soon.
I will add a full test case for this into 5.1 (include/query_cache.inc)

Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_22181               [ pass ]           1142
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers where restarted 1 times
Spent 1.142 seconds actually executing testcases

I confirm that I can no longer see a problem here.

A note has been added to the 6.0.0 changelog.