Find InnoDB lock waits in MySQL and MariaDB

MySQL 8.0 and later, from the bundled sys schema:

SELECT wait_age_secs, locked_table,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query
FROM sys.innodb_lock_waits;

MariaDB, and MySQL 5.7, from the information schema:

SELECT r.trx_mysql_thread_id AS waiting_pid,
       r.trx_query           AS waiting_query,
       b.trx_mysql_thread_id AS blocking_pid,
       b.trx_started         AS blocker_started
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

MySQL 8.0 removed the information schema table; its replacement is performance_schema.data_lock_waits, which the sys view reads. A blocking query that is empty or NULL usually means the blocker is idle inside its transaction: it holds its locks and runs nothing. See its SHOW PROCESSLIST row, where Command is Sleep.

SHOW ENGINE INNODB STATUS\G

Look for the section LATEST DETECTED DEADLOCK. It names both transactions, the statement each was running, the locks each held and the one each waited for, and says which transaction was rolled back. It keeps only the latest one. To keep every deadlock, set innodb_print_all_deadlocks, which writes each to the error log.