MySQL: Script to identify the locks and blocking transactions

In the series of DBA Scripts, I am sharing a script to find locks and blocking transaction of the MySQL Server.

The table locks and blocking transaction are a very common thing in any database system.
As a Database Administrator, this is our responsibility to find all locked and blocked transactions of MySQL Server.

Below is a script (work for MySQL 5.5 and above versions) :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT     pl.id     ,pl.user     ,pl.state     ,it.trx_id     ,it.trx_mysql_thread_id     ,it.trx_query AS query     ,it.trx_id AS blocking_trx_id     ,it.trx_mysql_thread_id AS blocking_thread     ,it.trx_query AS blocking_query FROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS it ON pl.id = it.trx_mysql_thread_id INNER JOIN information_schema.innodb_lock_waits AS ilw ON it.trx_id = ilw.requesting_trx_id         AND it.trx_id = ilw.blocking_trx_id