Movatterモバイル変換

MySQL 5.7 Reference Manual

24.4.14 The INFORMATION_SCHEMA INNODB_LOCKS Table

TheINNODB_LOCKS table provides information about each lock that anInnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.

Note

This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.

TheINNODB_LOCKS table has these columns:

LOCK_ID
A unique lock ID number, internal toInnoDB. Treat it as an opaque string. AlthoughLOCK_ID currently containsTRX_ID, the format of the data inLOCK_ID is subject to change at any time. Do not write applications that parse theLOCK_ID value.
LOCK_TRX_ID
The ID of the transaction holding the lock. To obtain details about the transaction, join this column with theTRX_ID column of theINNODB_TRX table.
LOCK_MODE
How the lock is requested. Permitted lock mode descriptors areS,X,IS,IX,GAP,AUTO_INC, andUNKNOWN. Lock mode descriptors may be used in combination to identify particular lock modes. For information aboutInnoDB lock modes, seeSection 14.7.1, “InnoDB Locking”.
LOCK_TYPE
The type of lock. Permitted values areRECORD for a row-level lock,TABLE for a table-level lock.
LOCK_TABLE
The name of the table that has been locked or contains locked records.
LOCK_INDEX
The name of the index, ifLOCK_TYPE isRECORD; otherwiseNULL.
LOCK_SPACE
The tablespace ID of the locked record, ifLOCK_TYPE isRECORD; otherwiseNULL.
LOCK_PAGE
The page number of the locked record, ifLOCK_TYPE isRECORD; otherwiseNULL.
LOCK_REC
The heap number of the locked record within the page, ifLOCK_TYPE isRECORD; otherwiseNULL.
LOCK_DATA
The data associated with the lock, if any. A value is shown if theLOCK_TYPE isRECORD, otherwise the value isNULL. Primary key values of the locked record are shown for a lock placed on the primary key index. Secondary index values of the locked record are shown for a lock placed on a unique secondary index. Secondary index values are shown with primary key values appended if the secondary index is not unique. If there is no primary key,LOCK_DATA shows either the key values of a selected unique index or the uniqueInnoDB internal row ID number, according to the rules governingInnoDB clustered index use (seeSection 14.6.2.1, “Clustered and Secondary Indexes”).LOCK_DATA reports“supremum pseudo-record” for a lock taken on a supremum pseudo-record. If the page containing the locked record is not in the buffer pool because it was written to disk while the lock was held,InnoDB does not fetch the page from disk. Instead,LOCK_DATA reportsNULL.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G*************************** 1. row ***************************    lock_id: 3723:72:3:2lock_trx_id: 3723  lock_mode: X  lock_type: RECORD lock_table: `mysql`.`t` lock_index: PRIMARY lock_space: 72  lock_page: 3   lock_rec: 2  lock_data: 1, 9*************************** 2. row ***************************    lock_id: 3722:72:3:2lock_trx_id: 3722  lock_mode: S  lock_type: RECORD lock_table: `mysql`.`t` lock_index: PRIMARY lock_space: 72  lock_page: 3   lock_rec: 2  lock_data: 1, 9

Notes

Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described inSection 14.16.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
You must have thePROCESS privilege to query this table.
Use theINFORMATION_SCHEMACOLUMNS table or theSHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.
For usage information, seeSection 14.16.2.1, “Using InnoDB Transaction and Locking Information”.

PREV HOME UP NEXT