How MySQL Query Cache works with Transactions

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Comments

  1. 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.

  2. 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

  3. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *