This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this DBA Scripts category, I am sharing one 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) :
,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