Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 285.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

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

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

The INNODB_LOCKS table has these columns:

  • LOCK_ID

    A 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

    The 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

    The name of the table that has been locked or contains locked records.

  • LOCK_INDEX

    The name of the index, if LOCK_TYPE is RECORD; otherwise NULL.

  • LOCK_SPACE

    The tablespace ID of the locked record, if LOCK_TYPE is RECORD; otherwise NULL.

  • LOCK_PAGE

    The page number of the locked record, if LOCK_TYPE is RECORD; otherwise NULL.

  • LOCK_REC

    The 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
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.