Bug #9276 LOCK TABLE ... READ LOCAL + Query Cache gives wrong result

• Submitted: 18 Mar 2005 12:14
• Modified: 18 Mar 2005 16:10
• Reporter: Tobias Asplund
• Status: Closed
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.1.10
• OS: Any (*)
• CPU Architecture: Any

[18 Mar 2005 12:14] Tobias Asplund

Description:
READ LOCAL locks on MyISAM tables can somehow poison the contents of the query cache with incorrect resultsets, see below:

How to repeat:
client1> SHOW TABLE STATUS LIKE 'City' \G
...
 Auto_increment: 4079
...
1 row in set (0.00 sec)

client1> SELECT * FROM City WHERE Id > 4078;
Empty set (0.00 sec)

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

client2> INSERT INTO City VALUES ();
Query OK, 1 row affected (0.00 sec)

client2> INSERT INTO City VALUES ();
Query OK, 1 row affected (0.00 sec)

client2> INSERT INTO City VALUES ();
Query OK, 1 row affected (0.00 sec)

client1> SELECT * FROM City WHERE Id > 4078;
Empty set (0.00 sec)

client1> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

client1> SELECT * FROM City WHERE Id > 4078;
Empty set (0.00 sec)

client1> SET GLOBAL query_cache_size = 0;
Query OK, 0 rows affected (0.00 sec)

client1> SELECT * FROM City WHERE Id > 4078;
+------+------+---------+----------+------------+------+
| Id   | Name | Country | District | Population | a    |
+------+------+---------+----------+------------+------+
| 4079 |      |         |          |          0 | NULL |
| 4080 |      |         |          |          0 | NULL |
| 4081 |      |         |          |          0 | NULL |
+------+------+---------+----------+------------+------+
3 rows in set (0.00 sec)

[18 Mar 2005 16:09] Jorge del Conde

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Tested it on 4.1.11 from bk:

mysql> SELECT * FROM City WHERE a > 20;
Empty set (0.00 sec)

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

mysql> SELECT * FROM City WHERE a > 20;
Empty set (0.01 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM City WHERE a > 20;
+----+
| a  |
+----+
| 21 |
| 22 |
| 23 |
+----+
3 rows in set (0.00 sec)

[18 Mar 2005 16:10] Jorge del Conde

The above is a console print of "Client 1"