Bug #2693 Query Cache ignores WRITE lock
Submitted: 9 Feb 2004 14:55 Modified: 4 Mar 2004 9:20
Reporter: Adam Erickson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18 OS:Linux (Linux / RedHat ES)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[9 Feb 2004 14: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 15:22] Dean Ellis 
Verified against 4.0.18 and 4.1.2.  Thank you for the report!
[13 Feb 2004 0: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 9: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)