Table of Contents
The seven
INFORMATION_SCHEMA
tables INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM,
INNODB_CMPMEM_RESET, INNODB_TRX, INNODB_LOCKS and
INNODB_LOCK_WAITS contain live information
about 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 8, Installing the InnoDB Plugin. If they are installed, but the InnoDB storage engine plugin is not installed, these tables will 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 will reset 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.
Reading from the table
INNODB_CMP_RESET will reset this
count. |
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. Reading from the table
INNODB_CMP_RESET will reset this
count. |
COMPRESS_TIME | Total time in seconds spent in attempts to
compress B-tree pages of the size
PAGE_SIZE. Reading from the table
INNODB_CMP_RESET will reset this
count. |
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. Reading from the table
INNODB_CMP_RESET will reset this
count. |
UNCOMPRESS_TIME | Total time in seconds spent in uncompressing
B-tree pages of the size PAGE_SIZE.
Reading from the table
INNODB_CMP_RESET will reset this
count. |
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. The tables INNODB_CMP and
INNODB_CMP_RESET should provide more useful statistics on
compression.
The column PAGE_SIZE contains the
allocation size of the memory allocator, a so-called binary buddy
system. You may think of it as the compressed page size. Compressed
pages are between 1024 and 16384 bytes. The rows with
PAGE_SIZE<1024 are implementation
artifacts. The smallest blocks (PAGE_SIZE=64
or PAGE_SIZE=128, depending on the server
platform) are used for keeping track of compressed pages for
which no uncompressed page has been allocated in the buffer
pool. Other blocks of PAGE_SIZE<1024
should never be allocated (PAGES_USED=0).
They exist because the memory allocator allocates smaller blocks by
splitting bigger ones into halves.
These two tables have identical contents, but reading from
INNODB_CMPMEM_RESET will reset 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 will reset this
count. |
RELOCATION_TIME | Total time in microseconds spent in relocating
blocks of the size PAGE_SIZE. Reading
from the table INNODB_CMPMEM_RESET will
reset 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, Section 6.2.1, “INNODB_CMP and INNODB_CMP_RESET ”, and Section 6.2.2, “INNODB_CMPMEM and INNODB_CMPMEM_RESET ”).
As can be seen in the following table,
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.
Table 6.3. The contents of
INFORMATION_SCHEMA.INNODB_CMP under light load
| 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 the following table there are 6169 eight-kilobyte
compressed pages in the buffer pool. The only other allocated
block size is 64 bytes. The smallest PAGE_SIZE
in INNODB_CMPMEM is used for block descriptors of those
compressed pages for which no uncompressed page exists in the
buffer pool. We see that there are 5910 such pages.
Indirectly, we see that 259 (6169-5910) compressed pages also
exist in the buffer pool in uncompressed form.
In the following table
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 a so-called 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).
Table 6.4. The contents of
INFORMATION_SCHEMA.INNODB_CMPMEM under light load
| page size | pages used | pages free | relocation ops | relocation time |
|---|---|---|---|---|
| 64 | 5910 | 0 | 2436 | 0 |
| 128 | 0 | 1 | 0 | 0 |
| 256 | 0 | 0 | 0 | 0 |
| 512 | 0 | 1 | 0 | 0 |
| 1024 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 1 | 0 | 0 |
| 4096 | 0 | 1 | 0 | 0 |
| 8192 | 6169 | 0 | 5 | 0 |
| 16384 | 0 | 0 | 0 | 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.5. 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',
'BLOCKED', '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='BLOCKED',
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='BLOCKED', otherwise
NULL).
|
TRX_MYSQL_THREAD_ID
|
MySQL thread ID. Can be used for joining with
PROCESSLIST on ID. See Notes.
|
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='BLOCKED') 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', 'BLOCKED',
'ROLLING BACK' or 'COMMITTING'). The lock that is blocking a
transaction will always be held in a mode (read vs. write,
shared vs. exclusive) incompatible with the mode of requested
lock.
Table 6.6. 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 intension 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 pasted in 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 will not fetch the
page from disk to avoid unnecessary disk operations.
Instead, LOCK_DATA will be 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.7. 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.
In the following scenario:User A:Given this scenario, the following queries show the contents of the threeBEGIN;User A:SELECT a FROM t FOR UPDATE;User A:SELECT SLEEP(100);User B:SELECT b FROM t FOR UPDATE;User C:SELECT c FROM t FOR UPDATE;
INFORMATION_SCHEMA tables related to transactions
and locking.
mysql> SELECT * FROM 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 |
mysql> SELECT * FROM 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 |
mysql> SELECT * FROM information_schema.innodb_lock_waits;| requesting trx id | requested lock id | blocking trx id | blocking lock id |
|---|---|---|---|
A4 | A4:1:3:2 | A3 | A3:1:3:2 |
A5 | A5:1:3:2 | A3 | A3:1:3:2 |
A5 | A5:1:3:2 | A4 | A4:1:3:2 |
Use this query to see who is waiting for who:
mysql> SELECT
trx_blocking.trx_mysql_thread_id AS blocking_thread,
trx_blocking.trx_query AS blocking_query,
trx_requesting.trx_mysql_thread_id AS requesting_thread,
trx_requesting.trx_query AS requesting_query
FROM
innodb_lock_waits
INNER JOIN innodb_trx AS trx_blocking
ON innodb_lock_waits.blocking_trx_id = trx_blocking.trx_id
INNER JOIN innodb_trx AS trx_requesting
ON innodb_lock_waits.requesting_trx_id = trx_requesting.trx_id;
blocking thread | blocking query | requesting thread | requesting query |
|---|---|---|---|
5 | SELECT SLEEP(100) | 6 | SELECT b FROM t FOR UPDATE |
5 | SELECT SLEEP(100) | 7 | SELECT c FROM t FOR UPDATE |
6 | SELECT b FROM t FOR UPDATE | 7 | SELECT c FROM t FOR UPDATE |
User B (trx_id='A4',thread=6) is waiting for User A
(trx_id='A3',thread=5).
User C (trx_id='A5',thread=7) is waiting for both
User A (trx_id='A3',thread=5) and User B
(trx_id='A4',thread=6).
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
show 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 the inconsistency between queries shown for transaction
E15 (mysql thread 61) in
INNODB_TRX.TRX_QUERY and
PROCESSLIST.INFO. See
Notes
for an explanation.
Table 6.8. The contents of
INFORMATION_SCHEMA.PROCESSLIST in a loaded
system
| 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 |
Table 6.9. The contents of
INFORMATION_SCHEMA.INNODB_TRX in a loaded
system
| trx id | trx state | trx started | trx requested lock id | trx waitstarted | trx weight | trx mysqlthread 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 … |
Table 6.10. The contents of
INFORMATION_SCHEMA.INNODB_LOCKS in a loaded
system
| 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 |
Table 6.11. 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 |
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. In order 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 'BLOCKED' state.
The table INNODB_LOCKS holds one or more row for each
'BLOCKED' 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 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 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
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 will be 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 of course see different results, since the
data will 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, will not
necessarily be in sync. This means that 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
(JOINing INNODB_TRX and PROCESSLIST, for example),
the content of those tables will generally not be 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 will always be consistent with the rest of
INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS when the
data comes from the same snapshot.