Each transaction in InnoDB that is waiting for another transaction to release a lock (INNODB_TRX.TRX_STATE='LOCK WAIT') 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', 'LOCK WAIT', '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 nameDescription
LOCK_IDUnique 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_IDID of the transaction holding this lock. Details about the transaction can be found by joining with INNODB_TRX on TRX_ID.
LOCK_MODEMode 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 intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections InnoDB Lock Modes and The InnoDB Transaction Model and Locking of the MySQL Manual for information on InnoDB locking.
LOCK_TYPEType of the lock. One of 'RECORD' or 'TABLE' for record (row) level or table level locks, respectively.
LOCK_TABLEName of the table that has been locked or contains locked records.
LOCK_INDEXName of the index if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_SPACETablespace ID of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_PAGEPage number of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_RECHeap number of the locked record within the page if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_DATAPrimary 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.

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