Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.8Mb
PDF (A4) - 37.8Mb
PDF (RPM) - 36.4Mb
HTML Download (TGZ) - 9.8Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.6Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_LOCKS Table

24.31.13 The INFORMATION_SCHEMA INNODB_LOCKS Table

The INNODB_LOCKS table contains information about each lock that an InnoDB 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.

Table 24.13 INNODB_LOCKS Columns

Column name Description
LOCK_ID Unique lock ID number, internal to InnoDB. Treat it as an opaque string. Although LOCK_ID currently contains TRX_ID, the format of the data in LOCK_ID is subject to change at any time. Do not write applications that parse the LOCK_ID value.
LOCK_TRX_ID ID of the transaction holding the lock. To obtain details about the transaction, join this column with the TRX_ID column of the INNODB_TRX table.
LOCK_MODE How the lock is requested. Permitted lock mode descriptors are S, X, IS, IX, GAP, AUTO_INC, and UNKNOWN. Lock mode descriptors may be used in combination to identify particular lock modes. For information about InnoDB lock modes, see Section 14.5.1, “InnoDB Locking”.
LOCK_TYPE The type of lock. Permitted values are RECORD for a row-level lock, TABLE for a table-level lock.
LOCK_TABLE Name of the table that has been locked or contains locked records.
LOCK_INDEX Name of the index, if LOCK_TYPE is RECORD; otherwise NULL.
LOCK_SPACE Tablespace ID of the locked record, if LOCK_TYPE is RECORD; otherwise NULL.
LOCK_PAGE Page number of the locked record, if LOCK_TYPE is RECORD; otherwise NULL.
LOCK_REC Heap number of the locked record within the page, if LOCK_TYPE is RECORD; otherwise NULL.
LOCK_DATA The data associated with the lock, if any. Values are primary key values of the locked record if LOCK_TYPE is RECORD, otherwise NULL. This column contains the values of the primary key columns in the locked row, formatted as a valid SQL string (ready to be copied to SQL statements). If there is no primary key, LOCK_DATA is the unique InnoDB internal row ID number. If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports supremum pseudo-record. 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.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 3723:72:3:2
lock_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:2
lock_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:


User Comments
Sign Up Login You must be logged in to post a comment.