Skip navigation links
6.3.3 INNODB_LOCK_WAITS »
Section Navigation      [Toggle]

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 is always held in a mode (read vs. write, shared vs. exclusive) incompatible with the mode of requested lock.

Table 6.4. 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 copied to 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 does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to NULL.

This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .