Bug #22618 Query cache invalidated throughout open transaction
Submitted: 22 Sep 2006 23:15 Modified: 13 Jun 2008 15:28
Reporter: Pete Harlan (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S5 (Performance)
Version:5.0.32-BK, 5.0.24a, 4.1.21 OS:Linux (Debian Linux)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[22 Sep 2006 23:15] Pete Harlan
Description:
Hi,

Section 5.14.1 of the manual states:

=====================
Cache entries for transactional InnoDB tables that have been changed are invalidated when a COMMIT is performed.
=====================

From this I would assume that the cache isn't invalidated _until_ the commit is performed.  This is not the case; the cache is invalidated the moment the transaction (at any isolation level) modifies a table, and remains invalidated, from the transaction's viewpoint or from other clients' viewpoints, until the transaction is committed.  (When I say "the cache is invalidated", I mean any queries involving a table affected by the transaction will not be satisfied by, or stored in (apparently), the cache.)

The manual then states:

=====================
The query cache also works within transactions when using InnoDB tables, making use of the table version number to detect whether its contents are still current.
=====================

The query cache works within a transaction for any tables the transaction hasn't modified, but not for any tables it has.  In other words, the behavior is the same within the transaction as it is from other clients: Once the transaction has updated a table, no queries involving that table will come from the cache until the transaction is committed or rolled back.

This is highly inconvenient---the query cache is such a valuable feature that we have come to depend on it.  It would be very nice if the query cache for clients who cannot yet see changes to a table would remain valid for them.

How to repeat:
create table qc_test (val int);

[insert 100,000 consecutive integer values in qc_test, however you want]

client A: select count(*) from qc_test where val regexp '2.*1';
+----------+
| count(*) |
+----------+
|     8146 |
+----------+
1 row in set (0.10 sec)

Repeat client A query, and it takes 0.00 seconds, indicating that the answer was satisfied by the cache.

client B: start transaction;

client A: select count(*) from qc_test where val regexp '2.*1';
[Result comes from cache]

client B: update qc_test set val = 2 where val = 1;

client A: select count(*) from qc_test where val regexp '2.*1';
[Result does not come from cache]

At this point, the query will not be satisfied by the cache, whether issued from client A or client B, until client B commits or rolls back the transaction.  Once the transaction is committed, the next select count(*) query will also not be satisfied by the cache, but it will populate it, so the one after that will come from the cache.
[25 Nov 2006 10:19] Valeriy Kravchuk
Thank you for a bug report. Verified with 5.0.32-BK on Linux.

Session A:

mysql> set global query_cache_size=1000000;
Query OK, 0 rows affected (0.03 sec)

mysql> create table qc_test (val int auto_increment primary key) engine=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into qc_test () values();
Query OK, 1 row affected (0.02 sec)

mysql> alter table qc_test add val2 int;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into qc_test (val2) select val2 from qc_test;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into qc_test (val2) select val2 from qc_test;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

...

mysql> insert into qc_test (val2) select val2 from qc_test;
Query OK, 65536 rows affected (3.25 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> show status like 'qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 990600 |
| Qcache_hits             | 0      |
| Qcache_inserts          | 0      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 2      |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 1      |
+-------------------------+--------+
8 rows in set (0.00 sec)

mysql> select count(*) from qc_test where val like '_2%';
+----------+
| count(*) |
+----------+
|    19999 |
+----------+
1 row in set (0.27 sec)

mysql> show status like 'qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 989064 |
| Qcache_hits             | 0      |
| Qcache_inserts          | 1      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 3      |
| Qcache_queries_in_cache | 1      |
| Qcache_total_blocks     | 4      |
+-------------------------+--------+
8 rows in set (0.00 sec)

mysql> select count(*) from qc_test where val like '_2%';
+----------+
| count(*) |
+----------+
|    19999 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 989064 |
| Qcache_hits             | 1      |
| Qcache_inserts          | 1      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 4      |
| Qcache_queries_in_cache | 1      |
| Qcache_total_blocks     | 4      |
+-------------------------+--------+
8 rows in set (0.01 sec)

Session B:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

Session A:

mysql> select count(*) from qc_test where val like '_2%';
+----------+
| count(*) |
+----------+
|    19999 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 990600 |
| Qcache_hits             | 2      |
| Qcache_inserts          | 1      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 11     |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 1      |
+-------------------------+--------+
8 rows in set (0.00 sec)

Session B (note that different column is changed, and transaction is NOT comitted):

mysql> update qc_test set val2 = 2 where val = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session A:

mysql> select count(*) from qc_test where val like '_2%';
+----------+
| count(*) |
+----------+
|    19999 |
+----------+
1 row in set (0.21 sec)

mysql> show status like 'qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 990600 |
| Qcache_hits             | 2      |
| Qcache_inserts          | 1      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 13     |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 1      |
+-------------------------+--------+
8 rows in set (0.00 sec)
[4 Jan 2007 15:50] Heikki Tuuri
Pete,

for InnoDB tables, the query cache of a table is invalidated immediately in an INSERT, DELETE, UPDATE.

The manual section is wrong. I am changing this as a MySQL Docs bug.

Your feature request is to let a transaction to use the query cache also when that same transaction has modified the table? But is that quite rare? Why would you issue the same SELECT query several times to a table that you have just modified?

Regards,

Heikki
[4 Jan 2007 16:56] Pete Harlan
Hi, thanks for taking a look at this.

> Your feature request is to let a transaction
> to use the query cache also when
> that same transaction has modified the table?

No, my feature request is:

It would be very nice if the query cache for clients who cannot yet see changes to a table would remain valid for them.

It's not the transaction that's modifying the table that I'm particularly worried about.  I only mentioned that to point out that the query cache appears disabled (not just cleared upon data modification) for that client also, even if it repeats the same query.  I mentioned this only because I noticed it, and it may be relevant to the bug report, and at the very least seemed to disagree with the docs.

It's the clients who aren't modifying the table, and who can't yet see the changes made to the table by the other client (who hasn't yet committed its transaction), that I would like to see use the query cache until the change happens.  As I interpret the docs, they say that it will work this way.

Ideally it would work that way.  Less ideally, the docs would reflect the current behavior.
[4 Jan 2007 17:07] Heikki Tuuri
Pete,

but that would not help much: if the client commits his transaction (like he should do very often), then the query cache would be invalidated for him. Furthermore, if the query cache can only hold one result per query (not multiple versions from different snapshots), then the change would prevent other, later transactions from benefitting from the query cache.

Regards,

Heikki
[4 Jan 2007 17:19] Pete Harlan
I'm thinking of the following scenario:

A bunch of clients/connections use a large table ("T") in a read-only fashion.  Most of the queries are satisfied from the query cache, because it's a website and unless you poke around in the corners of things the queries will tend to come from a small pool.

Another client ("client X") periodically updates T.  This process takes a relatively long time.  I'd like to have client X perform its work in a transaction, but as soon as it makes its first uncommitted change to T in its transaction, the many other clients stop having their queries satisfied by the cache until client X commits (or rolls back).

One workaround we have used is to have client X create a wholly new table, T_tmp, and then rename the table to T when it is finished.  This is not a nice solution for us, because T is large and typically the changes to T are few.  Rebuilding the table puts needless strain on replication, whereas the transaction method would require replicating only the few updates that were actually performed.
[11 Jan 2007 14:31] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated the query cache section per the clarification from Heikki.
[15 Aug 2007 14:04] Alice Dijkstra
Does this mean this ticket is closed after modifying the documentation?

We also expected a behaviour like Pete: query cache gets invalidated only after a commit!

But, now we learn that query cache gets invalidated troughout an open transaction. This is a big performance issue for us. 

We have the isolation level set at read-committed.

Will this behaviour be corrected and from which version?
[21 Aug 2007 19:03] Victor Welling
Is there any chance this issue might be related to this bug fix: http://bugs.mysql.com/bug.php?id=4213
[13 Jun 2008 9:56] rei rei
it is working fine in my case.

(1) client1:select name from tb1; <-not using cache
(2) client1:select name from tb1; <-using cache
(3) 
client2: 
start transaction;
select name from tb1; <- NOT USING CACHE
commit

(4) 
client 2:
start transaction;
select name from tb1; <- NOT USING CACHE
update tb1 set name ="newname";

client 1:
select name from tb1; <- USING CACHE

client 2:
commit;

client 1:
select name from tb1; <- NOT USING CACHE

Even when an update was issuied in a transaction, the other transaction can still use the cache before the transaction was commited.

However!
In my case, cache could not be used inside a transaction...
[13 Jun 2008 15:28] Pete Harlan
Thanks for testing this; which version of MySQL are you using?  Which isolation level?