October 1, 2014

How MySQL Query Cache works with Transactions

As MySQL Manual Says Query Cache works with transactions with Innodb tables but it does not tell you how and with which restrictions.

According to my tests it works but it is very restricted and one could expect it to work much better:

The result set can be retrieved from query cache (for statements both inside and outside of transactions) until there is a statement inside transactions which modifies the table. As soon as table is modified in transaction it becomes uncachable by query cache until that transaction is committed. Not only query cache can’t be used inside the same transaction which modified data but also in other concurrent transactions which do not even see the changes done yet

Of course such implementation is rather restricted. Queries outside of transactions well could use query cache until it is invalidated by committed transaction, however it was probably too hard to implement using current query cache infrastructure. With current approach Innodb can probably do something as simple as marking table “uncachable” if it has any uncommitted changed which would take care about all complicated aspects of change visibility in different transaction modes.

In most cases this limitation should not cause many problems (compared to general coarse table base invalidation it does) – only in case of long uncommitted transactions you will get data being uncachable for concurrent workload for a long time.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Pete Harlan says:

    I filed a bug report about this in Sept 2006 if you’re interested in reading Heikki’s thoughts on the matter:

    http://bugs.mysql.com/bug.php?id=22618

  2. peter says:

    Pete,

    Thank you for good illustration of this behavior.
    I see for you this behavior is quite and inconvenience. Good to know.

    I tend not to relay on Query Cache a lot in most large scale applications. There are just too many limits.

  3. Shelon Padmore says:

    I tend to agree that the benefits to be gained are most times VERY marginal when looked at in the context of the bigger app performance picture.
    Most times just not worth the effort.

    – Shelon Padmore

  4. kousi says:

    what is query cache? how it works and where is works well? please reply soon

  5. Meadows says:

    I am looking to tune a highly transactional messaging database and on most of my servers I turn on query cache and tune it…so that I know how to do. My question is I can’t have people viewing old data, I need them to view the current data at all times, does the query cache recognize when the result set is a different size and not use the cached version?

Speak Your Mind

*