When a transaction updates a row in a table, or locks it with
SELECT FOR UPDATE, InnoDB establishes a list
or queue of locks on that row. Similarly, InnoDB maintains a
list of locks on a table for table-level locks transactions
hold. If a second transaction wants to update a row or lock a
table already locked by a prior transaction in an incompatible
mode, InnoDB adds a lock request for the row to the
corresponding queue. For a lock to be acquired by a transaction,
all incompatible lock requests previously entered into the lock
queue for that row or table must be removed (the transactions
holding or requesting those locks either commit or roll back).
A transaction may have any number of lock requests for different
rows or tables. At any given time, a transaction may be
requesting a lock that is held by another transaction, in which
case it is blocked by that other transaction. The requesting
transaction must wait for the transaction that holds the
blocking lock to commit or rollback. If a transaction is not
waiting for a lock, it is in the
state. If a transaction is waiting for a lock, it is in the
'LOCK WAIT' state.
INNODB_LOCKS table holds one or
more row for each
'LOCK WAIT' transaction,
indicating any lock requests that are preventing its progress.
This table also contains one row describing each lock in a queue
of locks pending for a given row or table. The
INNODB_LOCK_WAITS table shows which
locks already held by a transaction are blocking locks requested
by other transactions.