Home 2016 February MySQL: Script to identify the locks and blocking transactions

MySQL: Script to identify the locks and blocking transactions

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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) :

Anvesh Patel
MySQL: Script to find Last AUTO_INCREMENT value for a TableSQL Server: TSQL Script to find Count the number of Occurrences of a String
Comments: 5
  1. vivek
    February 23, 2016 at 3:39 am

    Hi ,
    Do know how to use debugger in PL/SQL developer tool and what permission are required ?

    • Anvesh Patel
      Anvesh Patel
      February 23, 2016 at 6:45 pm

      If you are talking about Oracle, I need to check.
      For MySQL, you can visit this articles:
      http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet
      http://stackoverflow.com/questions/273437/how-do-you-debug-mysql-stored-procedures

  2. vayu nandan
    October 10, 2017 at 11:25 am

    YOUR SCRIPT IS WRONG . WE NEED TO USE information_schema.innodb_trx TABLE TWICE TO JOIN ON ilw.requesting_trx_id AND ilw.blocking_trx_id TO GET BLOCKED AND BLOCKING TRANSACTION DETAILS SEPERATELY

    • Anvesh Patel
      October 10, 2017 at 8:36 pm

      thanks for update, i will check this.

  3. Doug Mewhort
    November 20, 2019 at 9:42 pm

    I think the query should be:
    SELECT pl.id,pl.user,pl.state
    ,it.trx_id,it.trx_mysql_thread_id,it.trx_query AS blocked_query
    ,it2.trx_id AS blocking_trx_id,it2.trx_mysql_thread_id AS blocking_thread,it2.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_trx AS it2 ON pl.id = it2.trx_mysql_thread_id
    INNER JOIN information_schema.innodb_lock_waits AS ilw ON it.trx_id=ilw.requesting_trx_id AND it2.trx_id = ilw.blocking_trx_id;

Anvesh Patel
Anvesh Patel

Database Engineer

February 22, 2016 MySQL, MySQL DBA Script, , , , , , , , , , , , , , , ,