Bug #2693 Query Cache ignores WRITE lock
Submitted: 9 Feb 2004 15:55 Modified: 4 Mar 2004 10:20
Reporter: Adam Erickson
Status: Closed
Category:Server Severity:S2 (Serious)
Version:4.0.18 OS:Linux (Linux / RedHat ES)
Assigned to: Bugs System Target Version:

[9 Feb 2004 15:55] Adam Erickson 
Description:
When the query cache is enabled "LOCK TABLES a WRITE" does not prevent other threads from
reading the table.  This only happens if the query has already been cached.

How to repeat:
Enable query cache on the MySQL server.

CREATE TABLE test ( dummy char(1); );
INSERT INTO test ('a')('b')('c')('d')('e')('f')('g')('h');
(The data in the table obviously does not matter.)

Start two MySQL clients ("a" and "b")

Client A: use test; select * from test;
(Re-issue "SELECT" a couple times, to make sure it is cached.)

Client B: LOCK TABLES test WRITE;

Client A: select * from test;
(It should block here, but it will not.  Instead pulling from the query cache.)

Suggested fix:
Before checking the query cache for a resultset, the server should check the requrested
tables for any outstanding WRITE locks and honor them if needed.
[9 Feb 2004 16:22] Dean Ellis 
Verified against 4.0.18 and 4.1.2.  Thank you for the report!
[13 Feb 2004 1:58] Oleksandr Byelkin 
ChangeSet 
  1.1735 04/02/13 10:54:15 bell@sanja.is.com.ua +6 -0 
  invalidation of locking for write tables
[4 Mar 2004 10:20] Oleksandr Byelkin 
Thank you for bugreport. 
 
patch for this bug is pushed into our local source repository and will be 
present in next release. 
 
To get behaviour what you want, you will need to set variable 
query_cache_wlock_invalidate to 1(TRUE)