The data exposed by the transaction and locking tables represent a glimpse into fast-changing data. This is not like other (user) tables, where the data only changes when application-initiated updates occur. The underlying data is internal system-managed data, and can change very quickly.
For performance reasons, and to minimize the chance of
JOINs between the
INFORMATION_SCHEMA tables, InnoDB collects the required
transaction and locking information into an intermediate buffer
SELECT on any of the tables is
issued. This buffer is refreshed only if more than 0.1 seconds
has elapsed since the last time the buffer was used. The data
needed to fill the three tables is fetched atomically and
consistently and is saved in this global internal buffer,
forming a point-in-time “snapshot”. If multiple
table accesses occur within 0.1 seconds (as they almost
certainly do when MySQL processes a join among these tables),
then the same snapshot is used to satisfy the query.
A correct result is returned when you
any of these tables together in a single query, because the data
for the three tables comes from the same snapshot. Because the
buffer is not refreshed with every query of any of these tables,
if you issue separate queries against these tables within a
tenth of a second, the results are the same from query to query.
On the other hand, two separate queries of the same or different
tables issued more than a tenth of a second apart may see
different results, since the data come from different snapshots.
Because InnoDB must temporarily stall while the transaction and locking data is collected, too frequent queries of these tables can negatively impact performance as seen by other users.
As these tables contain sensitive information (at least
INNODB_TRX.TRX_QUERY), for security reasons,
only the users with the
PROCESS privilege are
SELECT from them.
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .