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 16:27	Modified:	16 Aug 2005 5:53
Reporter:	Tobias Asplund
Status:	Closed
Category:	Server: MyISAM	Severity:	S2 (Serious)
Version:	4.0	OS:	Any (*)
Assigned to:	Bugs System	Target Version:

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.