How to find out / locate MySQL dead lock

See also:

• How to get Active Transactions and Locks from MySQL

SELECT * FROM information_schema.innodb_locks;

(run it as root)

	SELECT
	r.trx_wait_started AS wait_started,
	TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
	rl.lock_table AS locked_table,
	rl.lock_index AS locked_index,
	rl.lock_type AS locked_type,
	r.trx_id AS waiting_trx_id,
	r.trx_mysql_thread_id AS waiting_pid,
	r.trx_query AS waiting_query,
	rl.lock_id AS waiting_lock_id,
	rl.lock_mode AS waiting_lock_mode,
	b.trx_id AS blocking_trx_id,
	b.trx_mysql_thread_id AS blocking_pid,
	b.trx_query AS blocking_query,
	bl.lock_id AS blocking_lock_id,
	bl.lock_mode AS blocking_lock_mode
	FROM
	information_schema.INNODB_LOCK_WAITS w
	INNER JOIN
	information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
	INNER JOIN
	information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
	INNER JOIN
	information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id
	INNER JOIN
	information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id
	ORDER BY r.trx_wait_started;

view raw locate MySQL dead lock.sql hosted with ❤ by GitHub

| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release. |

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.

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 intention exclusive gap locks, and auto-increment table level lock, respectively. 

See also:
InnoDB Lock Modes