Table of Contents
The INFORMATION_SCHEMA
tables
INNODB_BUFFER_PAGE
,
INNODB_BUFFER_PAGE_LRU
,
INNODB_BUFFER_POOL_STATS
,
INNODB_CMP
, INNODB_CMP_RESET
, INNODB_CMPMEM
,
INNODB_CMPMEM_RESET
, INNODB_TRX
, INNODB_LOCKS
and
INNODB_LOCK_WAITS
contain live information about the InnoDB
buffer pool, compressed InnoDB tables, the compressed InnoDB
buffer pool, all transactions currently executing inside InnoDB,
the locks that transactions hold and those that are blocking
transactions waiting for access to a resource (a table or row).
Note that the Information Schema tables are themselves plugins to the MySQL server. As such they need to be INSTALLed as described in Chapter 9, Installing the InnoDB Plugin. If they are installed, but the InnoDB storage engine plugin is not installed, these tables appear to be empty.
Following is a description of the new Information Schema tables introduced in the InnoDB Plugin, and some examples of their use.
Two new pairs of Information Schema tables provided by the InnoDB Plugin can give you some insight into how well compression is working overall. One pair of tables contains information about the number of compression operations and the amount of time spent performing compression. Another pair of tables contains information on the way memory is allocated for compression.
The tables INNODB_CMP
and INNODB_CMP_RESET
contain status
information on the operations related to compressed tables,
which are covered in Chapter 3, InnoDB Data Compression. The
compressed page size is in the column
PAGE_SIZE
.
These two tables have identical contents, but reading from
INNODB_CMP_RESET
resets the statistics on compression and
uncompression operations. For example, if you archived the
output of INNODB_CMP_RESET
every 60 minutes, it would show the
hourly statistics. If you never read
INNODB_CMP_RESET
and monitored the output of
INNODB_CMP
instead, it would show the cumulated statistics
since InnoDB was started.
Table 6.1. Columns of INNODB_CMP
and
INNODB_CMP_RESET
Column name | Description |
---|---|
PAGE_SIZE | Compressed page size in bytes. |
COMPRESS_OPS | Number of times a B-tree page of the size PAGE_SIZE
has been compressed. Pages are compressed whenever an
empty page is created or the space for the uncompressed
modification log runs out. |
COMPRESS_OPS_OK | Number of times a B-tree page of the size PAGE_SIZE
has been successfully compressed. This count should
never exceed COMPRESS_OPS . |
COMPRESS_TIME | Total time in seconds spent in attempts to compress B-tree pages of the
size PAGE_SIZE . |
UNCOMPRESS_OPS | Number of times a B-tree page of the size PAGE_SIZE
has been uncompressed. B-tree pages are uncompressed
whenever compression fails or at first access when the
uncompressed page does not exist in the buffer pool. |
UNCOMPRESS_TIME | Total time in seconds spent in uncompressing B-tree pages of the size
PAGE_SIZE . |
You may consider the tables INNODB_CMPMEM
and
INNODB_CMPMEM_RESET
as the status information on the
compressed pages that reside in the buffer pool. Please consult
Chapter 3, InnoDB Data Compression for further information on
compressed tables and the use of the buffer pool. The tables
INNODB_CMP
and INNODB_CMP_RESET
should provide more useful
statistics on compression.
The InnoDB Plugin uses a so-called “buddy allocator” system to manage memory allocated to pages of various sizes, from 1KB to 16KB. Each row of the two tables described here corresponds to a single page size.
These two tables have identical contents, but reading from
INNODB_CMPMEM_RESET
resets the statistics on relocation
operations. For example, if every 60 minutes you archived the
output of INNODB_CMPMEM_RESET
, it would show the hourly
statistics. If you never read INNODB_CMPMEM_RESET
and
monitored the output of INNODB_CMPMEM
instead, it would show
the cumulated statistics since InnoDB was started.
Table 6.2. Columns of INNODB_CMPMEM
and INNODB_CMPMEM_RESET
Column name | Description |
---|---|
PAGE_SIZE | Block size in bytes. Each record of this table describes blocks of this size. |
PAGES_USED | Number of blocks of the size PAGE_SIZE that are
currently in use. |
PAGES_FREE | Number of blocks of the size PAGE_SIZE that are
currently available for allocation. This column shows
the external fragmentation in the memory pool. Ideally,
these numbers should be at most 1. |
RELOCATION_OPS | Number of times a block of the size PAGE_SIZE has
been relocated. The buddy system can relocate the
allocated “buddy neighbor” of a freed block
when it tries to form a bigger freed block. Reading from
the table INNODB_CMPMEM_RESET resets this count. |
RELOCATION_TIME | Total time in microseconds spent in relocating blocks of the size
PAGE_SIZE . Reading from the table
INNODB_CMPMEM_RESET resets this count. |
Example 6.1. Using the Compression Information Schema Tables
The following is sample output from a database that contains
compressed tables (see Chapter 3, InnoDB Data Compression,
INNODB_CMP
, and INNODB_CMPMEM
).
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_CMP
under a light
workload. The only
compressed page size that the buffer pool contains is 8K.
Compressing or uncompressing pages has consumed less than a
second since the time the statistics were reset, because the
columns COMPRESS_TIME
and
UNCOMPRESS_TIME
are zero.
page size | compress ops | compress ops ok | compress time | uncompress ops | uncompress time |
---|---|---|---|---|---|
1024 | 0 | 0 | 0 | 0 | 0 |
2048 | 0 | 0 | 0 | 0 | 0 |
4096 | 0 | 0 | 0 | 0 | 0 |
8192 | 1048 | 921 | 0 | 61 | 0 |
16384 | 0 | 0 | 0 | 0 | 0 |
According to INNODB_CMPMEM
, there are 6169 compressed 8KB
pages in the buffer pool.
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_CMPMEM
under
light load. We can see that some memory is unusable due to
fragmentation of the InnoDB memory allocator for compressed
pages: SUM(PAGE_SIZE*PAGES_FREE)=6784
. This
is because small memory allocation requests are fulfilled by
splitting bigger blocks, starting from the 16K blocks that are
allocated from the main buffer pool, using the buddy
allocation system. The fragmentation is this low, because some
allocated blocks have been relocated (copied) to form bigger
adjacent free blocks. This copying of
SUM(PAGE_SIZE*RELOCATION_OPS)
bytes has
consumed less than a second
(SUM(RELOCATION_TIME)=0)
.
Three new Information Schema tables introduced in the
InnoDB Plugin make it much easier to monitor transactions and
diagnose possible locking problems. The three tables are
INNODB_TRX
, INNODB_LOCKS
and INNODB_LOCK_WAITS
.
Contains information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the particular SQL statement the transaction is executing.
Table 6.3. INNODB_TRX
Columns
Column name | Description |
---|---|
TRX_ID | Unique transaction ID number, internal to InnoDB. |
TRX_WEIGHT | The weight of a transaction, reflecting (but not necessarily the exact count of) the number of rows altered and the number of rows locked by the transaction. To resolve a deadlock, InnoDB selects the transaction with the smallest weight as the “victim” to rollback. Transactions that have changed non-transactional tables are considered heavier than others, regardless of the number of altered and locked rows. |
TRX_STATE | Transaction execution state. One of 'RUNNING' , 'LOCK WAIT' , 'ROLLING BACK'
or 'COMMITTING' . |
TRX_STARTED | Transaction start time; the transaction is created by executing a transactional query. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if TRX_STATE
is 'LOCK WAIT' , otherwise NULL ). Details about the lock
can be found by joining with INNODB_LOCKS on
LOCK_ID . |
TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if TRX_STATE is
'LOCK WAIT' , otherwise NULL ). |
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with PROCESSLIST on ID .
See
Section 6.4.3, “Possible Inconsistency with PROCESSLIST ”. |
TRX_QUERY | The SQL query that is being executed by the transaction. |
Each transaction in InnoDB that is waiting for another
transaction to release a lock
(INNODB_TRX.TRX_STATE='LOCK WAIT'
) is blocked
by exactly one “blocking lock request”. That
blocking lock request is for a row or table lock held by another
transaction in an incompatible mode. The waiting or blocked
transaction cannot proceed until the other transaction commits
or rolls back, thereby releasing the requested lock. For every
blocked transaction, INNODB_LOCKS
contains one row that
describes each lock the transaction has requested, and for which
it is waiting. INNODB_LOCKS
also contains one row for each
lock that is blocking another transaction, whatever the state of
the transaction that holds the lock ('RUNNING'
, 'LOCK WAIT'
,
'ROLLING BACK'
or 'COMMITTING'
). The lock that is blocking a
transaction is always held in a mode (read vs. write, shared vs.
exclusive) incompatible with the mode of requested lock.
Table 6.4. INNODB_LOCKS
Columns
Column name | Description |
---|---|
LOCK_ID | Unique lock ID number, internal to InnoDB. Should be treated as an
opaque string. Although LOCK_ID currently contains
TRX_ID , the format of the data in LOCK_ID is not
guaranteed to remain the same in future releases. You
should not write programs that parse the LOCK_ID
value. |
LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction
can be found by joining with INNODB_TRX on TRX_ID . |
LOCK_MODE | Mode of the lock. One of 'S' , 'X' , 'IS' , 'IX' , 'S,GAP' , 'X,GAP' ,
'IS,GAP' , 'IX,GAP' , or 'AUTO_INC' for shared, exclusive,
intention shared, intention exclusive row locks, shared
and exclusive gap locks, intention shared and intention
exclusive gap locks, and auto-increment table level
lock, respectively. Refer to the sections
InnoDB Lock Modes and
InnoDB and TRANSACTION ISOLATION
LEVEL of the MySQL Manual for
information on InnoDB locking. |
LOCK_TYPE | Type of the lock. One of 'RECORD' or 'TABLE' for record (row) level or
table level locks, respectively. |
LOCK_TABLE | Name of the table that has been locked or contains locked records. |
LOCK_INDEX | Name of the index if LOCK_TYPE='RECORD' , otherwise
NULL . |
LOCK_SPACE | Tablespace ID of the locked record if
LOCK_TYPE='RECORD' , otherwise NULL . |
LOCK_PAGE | Page number of the locked record if
LOCK_TYPE='RECORD' , otherwise NULL . |
LOCK_REC | Heap number of the locked record within the page if
LOCK_TYPE='RECORD' , otherwise NULL . |
LOCK_DATA | Primary key of the locked record if
LOCK_TYPE='RECORD' , otherwise NULL .
This column contains the value(s) of the primary key
column(s) in the locked row, formatted as a valid SQL
string (ready to be copied to SQL commands). If there is
no primary key then the InnoDB internal unique row ID
number is used. When the page containing the locked
record is not in the buffer pool (in the case that it
was paged out to disk while the lock was held), InnoDB
does not fetch the page from disk, to avoid unnecessary
disk operations. Instead, LOCK_DATA is set to NULL . |
Using this table, you can tell which transactions are waiting
for a given lock, or for which lock a given transaction is
waiting. This table contains one or more rows for each
blocked transaction, indicating the lock it
has requested and the lock(s) that is (are) blocking that
request. The REQUESTED_LOCK_ID
refers to the lock that a
transaction is requesting, and the BLOCKING_LOCK_ID
refers to
the lock (held by another transaction) that is preventing the
first transaction from proceeding. For any given blocked
transaction, all rows in INNODB_LOCK_WAITS
have the same value
for REQUESTED_LOCK_ID
and different values for
BLOCKING_LOCK_ID
.
Table 6.5. INNODB_LOCK_WAITS
Columns
Column name | Description |
---|---|
REQUESTING_TRX_ID | ID of the requesting transaction. |
REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the
lock can be found by joining with INNODB_LOCKS on
LOCK_ID . |
BLOCKING_TRX_ID | ID of the blocking transaction. |
BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from
proceeding. Details about the lock can be found by
joining with INNODB_LOCKS on LOCK_ID . |
Example 6.2. Identifying Blocking Transactions
It is sometimes helpful to be able to identify which transaction is blocking another. You can use the Information Schema tables to find out which transaction is waiting for another, and which resource is being requested.
Suppose you have the following scenario, with three users running concurrently. Each user (or session) corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these users have issued the following commands, but none has yet committed its transaction:
BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);
SELECT b FROM t FOR UPDATE;
SELECT c FROM t FOR UPDATE;
In this scenario, you may use this query to see who is waiting for whom:
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
waiting trx id | waiting thread | waiting query | blocking trx id | blocking thread | blocking query |
---|---|---|---|---|---|
A4 | 6 | SELECT b FROM t FOR UPDATE | A3 | 5 | SELECT SLEEP(100) |
A5 | 7 | SELECT c FROM t FOR UPDATE | A3 | 5 | SELECT SLEEP(100) |
A5 | 7 | SELECT c FROM t FOR UPDATE | A4 | 6 | SELECT b FROM t FOR UPDATE |
In the above result, you can identify users by the “waiting query” or “blocking query”. As you can see:
User B (trx id 'A4'
, thread
6
) and User C (trx id
'A5'
, thread 7
) are
both waiting for User A (trx id 'A3'
,
thread 5
).
User C is waiting for User B as well as User A.
You can see the underlying data in the tables
INNODB_TRX
,
INNODB_LOCKS
,
and
INNODB_LOCK_WAITS
.
The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_TRX.
trx id | trx state | trx started | trx requested lock id | trx wait started | trx weight | trx mysql thread id | trx query |
---|---|---|---|---|---|---|---|
A3 | RUNNING | 2008-01-15 16:44:54 | NULL | NULL | 2 | 5 | SELECT SLEEP(100) |
A4 | LOCK WAIT | 2008-01-15 16:45:09 | A4:1:3:2 | 2008-01-15 16:45:09 | 2 | 6 | SELECT b FROM t FOR UPDATE |
A5 | LOCK WAIT | 2008-01-15 16:45:14 | A5:1:3:2 | 2008-01-15 16:45:14 | 2 | 7 | SELECT c FROM t FOR UPDATE |
The following table shows some sample contents of
INFORMATION_SCHEMA.INNODB_LOCKS
.
lock id | lock trx id | lock mode | lock type | lock table | lock index | lock space | lock page | lock rec | lock data |
---|---|---|---|---|---|---|---|---|---|
A3:1:3:2 | A3 | X | RECORD | `test`.`t` | `PRIMARY` | 1 | 3 | 2 | 0x0200 |
A4:1:3:2 | A4 | X | RECORD | `test`.`t` | `PRIMARY` | 1 | 3 | 2 | 0x0200 |
A5:1:3:2 | A5 | X | RECORD | `test`.`t` | `PRIMARY` | 1 | 3 | 2 | 0x0200 |
The following table shows some sample contents of
INFORMATION_SCHEMA.INNODB_LOCK_WAITS
.
Example 6.3. More Complex Example of Transaction Data in Information Schema Tables
Sometimes you would like to correlate the internal InnoDB locking information with session-level information maintained by MySQL. For example, you might like to know, for a given InnoDB transaction ID, the corresponding MySQL session ID and name of the user that may be holding a lock, and thus blocking another transaction.
The following output from the
INFORMATION_SCHEMA
tables is taken from a
somewhat loaded system.
As can be seen in the following tables, there are several transactions running.
The following INNODB_LOCKS
and
INNODB_LOCK_WAITS
tables shows that:
Transaction 77F
(executing an
INSERT
) is waiting for transactions
77E
, 77D
and 77B
to commit.
Transaction 77E
(executing an INSERT) is waiting for
transactions 77D
and 77B
to commit.
Transaction 77D
(executing an INSERT) is waiting for
transaction 77B
to commit.
Transaction 77B
(executing an INSERT) is waiting for
transaction 77A
to commit.
Transaction 77A
is running, currently executing
SELECT
.
Transaction E56
(executing an
INSERT
) is waiting for transaction
E55
to commit.
Transaction E55
(executing an
INSERT
) is waiting for transaction
19C
to commit.
Transaction 19C
is running, currently executing an
INSERT
.
Note that there may be an inconsistency between queries shown
in the two tables INNODB_TRX.TRX_QUERY
and
PROCESSLIST.INFO
. The current transaction
ID for a thread, and the query being executed in that
transaction, may be different in these two tables for any
given thread. See
Section 6.4.3, “Possible Inconsistency with PROCESSLIST
”
for an explanation.
The following table shows the contents of
INFORMATION_SCHEMA.PROCESSLIST
in a system
running a heavy workload.
ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
---|---|---|---|---|---|---|---|
384 | root | localhost | test | Query | 10 | update | insert into t2 values … |
257 | root | localhost | test | Query | 3 | update | insert into t2 values … |
130 | root | localhost | test | Query | 0 | update | insert into t2 values … |
61 | root | localhost | test | Query | 1 | update | insert into t2 values … |
8 | root | localhost | test | Query | 1 | update | insert into t2 values … |
4 | root | localhost | test | Query | 0 | preparing | SELECT * FROM processlist |
2 | root | localhost | test | Sleep | 566 |
| NULL |
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_TRX
in a system
running a heavy workload.
trx id | trx state | trx started | trx requested lock id | trx wait started | trx weight | trx mysql thread id | trx query |
---|---|---|---|---|---|---|---|
77F | LOCK WAIT | 2008-01-15 13:10:16 | 77F :806 | 2008-01-15 13:10:16 | 1 | 876 | insert into t09 (D, B, C) values … |
77E | LOCK WAIT | 2008-01-15 13:10:16 | 77E :806 | 2008-01-15 13:10:16 | 1 | 875 | insert into t09 (D, B, C) values … |
77D | LOCK WAIT | 2008-01-15 13:10:16 | 77D :806 | 2008-01-15 13:10:16 | 1 | 874 | insert into t09 (D, B, C) values … |
77B | LOCK WAIT | 2008-01-15 13:10:16 | 77B :733:12:1 | 2008-01-15 13:10:16 | 4 | 873 | insert into t09 (D, B, C) values … |
77A | RUNNING | 2008-01-15 13:10:16 | NULL | NULL | 4 | 872 | select b, c from t09 where … |
E56 | LOCK WAIT | 2008-01-15 13:10:06 | E56 :743:6:2 | 2008-01-15 13:10:06 | 5 | 384 | insert into t2 values … |
E55 | LOCK WAIT | 2008-01-15 13:10:06 | E55 :743:38:2 | 2008-01-15 13:10:13 | 965 | 257 | insert into t2 values … |
19C | RUNNING | 2008-01-15 13:09:10 | NULL | NULL | 2900 | 130 | insert into t2 values … |
E15 | RUNNING | 2008-01-15 13:08:59 | NULL | NULL | 5395 | 61 | insert into t2 values … |
51D | RUNNING | 2008-01-15 13:08:47 | NULL | NULL | 9807 | 8 | insert into t2 values … |
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_LOCK_WAITS
in a
loaded system
requesting trx id | requested lock id | blocking trx id | blocking lock id |
---|---|---|---|
77F | 77F :806 | 77E | 77E :806 |
77F | 77F :806 | 77D | 77D :806 |
77F | 77F :806 | 77B | 77B :806 |
77E | 77E :806 | 77D | 77D :806 |
77E | 77E :806 | 77B | 77B :806 |
77D | 77D :806 | 77B | 77B :806 |
77B | 77B :733:12:1 | 77A | 77A :733:12:1 |
E56 | E56 :743:6:2 | E55 | E55 :743:6:2 |
E55 | E55 :743:38:2 | 19C | 19C :743:38:2 |
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_LOCKS
in a system
running a heavy workload.
lock id | lock trx id | lock mode | lock type | lock table | lock index | lock space | lock page | lock rec | lock data |
---|---|---|---|---|---|---|---|---|---|
77F :806 | 77F | AUTO_INC | TABLE | `test`.`t09` | NULL | NULL | NULL | NULL | NULL |
77E :806 | 77E | AUTO_INC | TABLE | `test`.`t09` | NULL | NULL | NULL | NULL | NULL |
77D :806 | 77D | AUTO_INC | TABLE | `test`.`t09` | NULL | NULL | NULL | NULL | NULL |
77B :806 | 77B | AUTO_INC | TABLE | `test`.`t09` | NULL | NULL | NULL | NULL | NULL |
77B :733:12:1 | 77B | X | RECORD | `test`.`t09` | `PRIMARY` | 733 | 12 | 1 | supremum pseudo-record |
77A :733:12:1 | 77A | X | RECORD | `test`.`t09` | `PRIMARY` | 733 | 12 | 1 | supremum pseudo-record |
E56 :743:6:2 | E56 | S | RECORD | `test`.`t2` | `PRIMARY` | 743 | 6 | 2 | 0, 0 |
E55 :743:6:2 | E55 | X | RECORD | `test`.`t2` | `PRIMARY` | 743 | 6 | 2 | 0, 0 |
E55 :743:38:2 | E55 | S | RECORD | `test`.`t2` | `PRIMARY` | 743 | 38 | 2 | 1922, 1922 |
19C :743:38:2 | 19C | X | RECORD | `test`.`t2` | `PRIMARY` | 743 | 38 | 2 | 1922, 1922 |
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
rollback).
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 'RUNNING'
state. If a
transaction is waiting for a lock, it is in the 'LOCK WAIT'
state.
The table INNODB_LOCKS
holds one or more row for each
'LOCK WAIT'
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 INNODB_LOCK_WAITS
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
misleading JOIN
s 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 JOIN
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_LOCKS.LOCK_DATA
and
INNODB_TRX.TRX_QUERY
), for security reasons,
only the users with the PROCESS
privilege are
allowed to 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
MySQL table 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
SELECT
(JOIN
ing
INNODB_TRX
and PROCESSLIST
, for example), the content of
those tables is generally not consistent. INNODB_TRX
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
INNODB_TRX
is always consistent with the rest of INNODB_TRX
,
INNODB_LOCKS
and INNODB_LOCK_WAITS
when the data comes from
the same snapshot.