MySQL Bugs: #12385: Query Cache and READ LOCAL locks can cause bad results from query

Bug #12385	Query Cache and READ LOCAL locks can cause bad results from query
Submitted:	4 Aug 2005 14:27	Modified:	16 Aug 2005 3:53
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0	OS:	Any (*)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
An insert to a READ LOCAL locked MyISAM table does not invalidate the query cache's results from that table.
See below:

(conn1 and conn2 are two different connections to the MySQL server).

How to repeat:
conn1> CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.12 sec)

conn1> LOCK TABLE t1 READ LOCAL;
Query OK, 0 rows affected (0.00 sec)

conn2> INSERT INTO t1 VALUES (), (), ();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

conn1> SELECT * FROM t1;
Empty set (0.00 sec)

conn2> SELECT * FROM t1;
Empty set (0.00 sec)

conn2> SELECT * FROM  t1 /* extra stuff to fool Qcache */;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

Problem is not in invalidation, cache is filled by first select from other connect, here you can see how it looks like without query cache, if you run following script in mysql_test
connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock);
connection root;
CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE =
MyISAM;
LOCK TABLE t1 READ LOCAL;
connect (root2,localhost,root,,test,$MASTER_MYPORT,master.sock);
connection root2;
INSERT INTO t1 VALUES (), (), ();
connection root;
SELECT * FROM t1;
connection root2;
SELECT * FROM t1;
connection root;
SELECT * FROM t1;
drop table t1;

then you'll get following results:
+ CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE =
+ MyISAM;
+ LOCK TABLE t1 READ LOCAL;
+ INSERT INTO t1 VALUES (), (), ();
+ SELECT * FROM t1;
+ a
+ SELECT * FROM t1;
+ a
+ 1
+ 2
+ 3
+ SELECT * FROM t1;
+ a
+ drop table t1;

With query ccahe all selects return the same results as first one. Of course it is not right. I thionk best way is switch off QC for tables locked as READ LOCAL.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28116

Thank you for bugreport!
Now Query cache is switched off if thread (connection) have tables locked with LOCK statement (any kind of LOCK).
changes pushed to 4.0.26, 4.1.14, 5.0.12

Documented as a functionality change for the changelogs of the versions listed:

<listitem><para>
Query cache is switched off if a thread (connection) has tables locked. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385)
</para></listitem>

Developer: please review this entry and reset status to documenting if this entry is inaccurate.