Databases Reference
In-Depth Information
The factors that determine access are the transaction ID and whether there are any
locks on the table. Each table in InnoDB's in-memory data dictionary has an associated
transaction ID counter. Transactions whose IDs are less than the counter value are
forbidden to read from or write to the query cache for queries that involve that table.
Any locks on a table also make queries that access it uncacheable. For example, if a
transaction performs a
SELECT FOR UPDATE
query on a table, no other transactions will
be able to read from or write to the query cache for queries involving that table until
the locks are released.
When a transaction commits, InnoDB updates the counters for the tables upon which
the transaction has locks. A lock is a rough heuristic for determining whether the
transaction has modified a table; it is possible for a transaction to lock rows in a table
and not update them, but it is not possible for it to modify the table's contents without
acquiring any locks. InnoDB sets each table's counter to the system's transaction ID,
which is the maximum transaction ID in existence.
This has the following consequences:
• The table's counter is an absolute lower bound on which transactions can use the
query cache. If the system's transaction ID is 5 and a transaction acquires locks on
rows in a table and then commits, transactions 1 through 4 can never read from or
write to the query cache for queries involving that table again.
• The table's counter is updated not to the transaction ID of the transaction that
locked rows in it, but to the system's transaction ID. As a result, transactions that
lock rows in tables might find themselves blocked from reading from or writing to
the query cache for queries involving that table in the future.
Query cache storage, retrieval, and invalidation are handled at the server level, and
InnoDB cannot bypass or delay this. However, InnoDB can tell the server explicitly to
invalidate queries that involve specific tables. This is necessary when a foreign key
constraint, such as
ON DELETE CASCADE
, alters the contents of a table that isn't mentioned
in a query.
In principle, InnoDB's MVCC architecture could let queries be served from the cache
when modifications to a table don't affect the consistent read view other transactions
see. However, implementing this would be complex. InnoDB's algorithm takes some
shortcuts for simplicity, at the cost of locking transactions out of the query cache when
this might not really be necessary.
General Query Cache Optimizations
Many schema, query, and application design decisions affect the query cache. In ad-
dition to what we discussed in the previous sections, here are some points to keep in
mind:
