When a transaction updates a row in a table, or locks it with
SELECT FOR UPDATE, InnoDB establishes a list
or queue of locks on that row. Similarly, InnoDB maintains a
list of locks on a table for table-level locks transactions
hold. If a second transaction wants to update a row or lock a
table already locked by a prior transaction in an incompatible
mode, InnoDB adds a lock request for the row to the
corresponding queue. For a lock to be acquired by a transaction,
all incompatible lock requests previously entered into the lock
queue for that row or table must be removed (the transactions
holding or requesting those locks either commit or roll back).
A transaction may have any number of lock requests for different
rows or tables. At any given time, a transaction may be
requesting a lock that is held by another transaction, in which
case it is blocked by that other transaction. The requesting
transaction must wait for the transaction that holds the
blocking lock to commit or rollback. If a transaction is not
waiting for a a lock, it is in the
state. If a transaction is waiting for a lock, it is in the
'LOCK WAIT' state.
holds one or more row for each
transaction, indicating the lock request(s) that is (are)
preventing its progress. This table also contains one row
describing each lock in a queue of locks pending for a given row
or table. The table
shows which locks already held by a transaction are blocking
locks requested by other transactions.
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 whenever a
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
read. 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.
As just described, while the transaction and locking data is
correct and consistent when these
INFORMATION_SCHEMA tables are populated, the
underlying data changes so fast that similar glimpses at other,
similarly fast-changing data, may not be in sync. Thus, you
should be careful in comparing the data in the InnoDB
transaction and locking tables with that in the
PROCESSLIST. The data from the
PROCESSLIST table does not come from the same
snapshot as the data about locking and transactions. Even if you
issue a single
PROCESSLIST, for example), the content of
those tables is generally not consistent.
may reference rows that are not present in
PROCESSLIST or the currently executing SQL
query of a transaction, shown in
INNODB_TRX.TRX_QUERY may be different from
the one in
PROCESSLIST.INFO. The query in
is always consistent with the rest of
when the data comes from the same snapshot.