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.
