MySQL Bugs: #35390: Access to Queries in cache is blocked even if invalidation is set to off

Bug #35390	Access to Queries in cache is blocked even if invalidation is set to off
Submitted:	18 Mar 2008 11:30	Modified:	6 May 2008 12:27
Reporter:	Rizwan Maredia	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S2 (Serious)
Version:	5.1.22, 5.0, 5.1, 6.0	OS:	Any (MS Windows, Linux)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	query cache, query_cache_wlock_invalidate

Description:
If a query exists in query cache and query_cache_wlock_invalidate is set to OFF. A client trying to access the table (being locked) through cached query gets blocked instead of being allowed access to cached query.

How to repeat:
Using Mysql test environment

CREATE TABLE t1(id int, value varchar(10));
INSERT INTO t1 VALUES(1, 'val1');

# Clearing the query cache and setting up cache size
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_size = 131072;
SET GLOBAL query_cache_type = ON;
SET SESSION query_cache_wlock_invalidate = OFF;
SELECT * FROM t1;
+id	value
+1	val1
connect (con1, localhost, root,,);
connection con1;
SET SESSION query_cache_wlock_invalidate = OFF;
SELECT * FROM t1;
+id	value
+1	val1
connection default;
LOCK TABLE t1 WRITE;

connection con1;
delimiter |;
send
SET @startTime = NOW();
SELECT * FROM t1;
SET @endTime = NOW();
SET @TimeDifference = TIME_TO_SEC(TIMEDIFF(@endTime, @startTime));
SELECT @TimeDifference;|
delimiter ;|

connection default;
--sleep 2
UNLOCK TABLES;

connection con1;
reap;

# The reap outputs the following result
id	value
1	val1
@TimeDifference
2

The time difference should not be 2 seconds in this case, it should be less than a second;

Suggested fix:
The access to clients should not be blocked in case of a write lock if query_cache_wlock_invalidate = OFF and query results are in the query cache.

Thank you for the report.

Verified as described.

This is probably more of a feature request. Query Cache doesn't cache 'statements' but rather text strings representing a expression. When multiple statements are sent to the server it process each statement by cutting up the input character string on each delimiter ';'. However the QC doesn't recognize ;-characters (multiple statements) and stores each new sub-string as a key for the result set.

If the above example is changed to only call "SELECT * FROM t1;" without changing delimiter to include more statements, the table is not blocked.

The effect of query_cache_wlock_invalidate is indirect by simply invalidating the cache on a WRITE lock:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_query_ca...