Chapter 6. InnoDB INFORMATION

Chapter 6. InnoDB INFORMATION_SCHEMA tables
Prev		Next

Chapter 6. InnoDB `INFORMATION_SCHEMA` tables

Table of Contents

6.1. Overview

6.2. Information Schema Tables about Compression

6.2.1. INNODB_CMP and INNODB_CMP_RESET
6.2.2. INNODB_CMPMEM and INNODB_CMPMEM_RESET
6.2.3. Using the Compression Information Schema Tables

6.3. Information Schema Tables about Transactions.

6.3.1. INNODB_TRX
6.3.2. INNODB_LOCKS
6.3.3. INNODB_LOCK_WAITS
6.3.4. Using the Transaction InfoSchema Tables

6.4. Notes on Locking in InnoDB

6.1. Overview

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.

6.2. Information Schema Tables about Compression

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.

6.2.1. `INNODB_CMP` and `INNODB_CMP_RESET`

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.

6.2.2. `INNODB_CMPMEM` and `INNODB_CMPMEM_RESET`

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.

6.2.3. Using the Compression Information Schema Tables

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	uncompress ops
1024	0	0	0
2048	0	0	0
4096	0	0	0
8192	1048	921	61
16384	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
64	5910	0	2436
128	0	1	0
256	0	0	0
512	0	1	0
1024	0	0	0
2048	0	1	0
4096	0	1	0
8192	6169	0	5
16384	0	0	0

6.3. Information Schema Tables about Transactions.

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.

6.3.1. `INNODB_TRX`

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.

6.3.2. `INNODB_LOCKS`

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`.

6.3.3. `INNODB_LOCK_WAITS`

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`.

6.3.4. Using the Transaction InfoSchema Tables

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: BEGIN;
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;

Given this scenario, the following queries show the contents of the three 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`

6.4. Notes on Locking in InnoDB

Understanding InnoDB Locking

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.

Rapidly changing internal data

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.

Possible inconsistency with PROCESSLIST

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.

Prev		Next
Chapter 5. Storage of Variable-Length Columns	Home	Chapter 7. Changes for Flexibility, Ease of Use and Reliability