MySQL Bugs: #43032: The query cache can appear to no longer function in certain circumstances

Bug #43032	The query cache can appear to no longer function in certain circumstances
Submitted:	19 Feb 2009 19:38	Modified:	13 Mar 2009 19:55
Reporter:	Von Chance	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S5 (Performance)
Version:	5.0.67, 5.0.76, any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I wanted to document the following scenario since it caused us some grief.  I believe that this issue should be addressed even if it is only noted in the documentation.  This sequence of events will look like the query cache is no longer functioning.

How to repeat:
** A test schema is provided **

CREATE DATABASE IF NOT EXISTS testschema;
USE testschema;
DROP TABLE IF EXISTS `node`;
CREATE TABLE `node` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `node`
--

/*!40000 ALTER TABLE `node` DISABLE KEYS */;
INSERT INTO `node` (`id`) VALUES 
 (1),
 (2),
 (3),
 (4),
 (5);
/*!40000 ALTER TABLE `node` ENABLE KEYS */;

-----------------------------------------------------------------------------

1. Use the innodb engine and set the transaction isolation level to READ-COMMITTED mode.

2. Create 2 connections to the database and turn AUTOCOMMIT off for both.

3. On the first connection issue the query (select * from node;) several times.

4. Looking at the query cache statistics, you will notice that the Qcache_queries_in_cache would have increased by 1 and Qcache_hits would have increased every time the query is re-issued.

5. On the second connection issue the following:
begin transaction;
insert into node (id) values (null);
commit no release;

6. You will notice that the Qcache_queries_in_cache would have decreased by 1 since the write would have removed all cached queries using that table.

7. On the first connection, issue the same select query as before.  You will notice that Qcache_queries_in_cache does not increase and Qcache_not_cached does increase.  Repeated issuance of the query will produce the same results.

8. On the forst connection, issue the 'commit' command.

9. On the first connection, issue the query again.  This time it is correctly cached as before.

** If the transaction isolation level is READ-COMMITTED, why would I need to commit a transaction that hasn't written anything in order for it to cache it again?

Suggested fix:
At least document this as one of the reasons a query would not get cached if changing the implementation is too risky/time consuming.

Thank you for the problem report. Verified just as described. May be related to bug #42197.

This is not a bug. 

The query cache itself is a global resource. It is not aware of any transactional states nor does it keep multiple versions of any one query based on their transaction number. 

This is exactly why we implemented the Qcache_not_cached statistic: There is no way for us to cache the results of a query that must be repeatable from within a transaction because the Query Cache is a global, non-transactional resource. 

Unless the core implementation of the Query Cache changes considerably, this must be considered appropriate behavior. Marking this as "Not a Bug"

I agree with some of the statements above, however I don't think it is appropriate to indicate that this is "not a bug" because the implementation does not account for it.  That could apply to just about every bug out there.

The query cache does have knowledge of the transactional state, albeit indirectly.  This is evidenced by the behavior of the test scenario above and reviewing the source code of mysql itself.

The query cache makes a call to ask_handler_allowance which asks the engine if any of the tables included in the current query are not cacheable.  In the innodb case, it eventually flows down to the row_search_check_if_query_cache_permitted function.  In this function, the following check is performed:

...
if (UT_LIST_GET_LEN(table->locks) == 0
	    && ut_dulint_cmp(trx->id, table->query_cache_inv_trx_id) >= 0) {
...

This seems to invalidate any open transaction that was created before a transaction causing the query cache to be invalidated.  This would be true for a repeatable read isolation level, but is not true for a read committed isolation level.

** This could be changed to allow this or simply document that this is one of the conditions that would prevent a query to be cacheable.  The ability to fetch the reason why a particular query was not cacheable would also be beneficial.