How to get Active Transactions and Dead Locks from MySQL

To get a list of active transactions and locks that are currently executing against our target database

Simply use:

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

If a NULL value is reported for the blocking query, see Identifying a Blocking Query After the Issuing Session Becomes Idle. 


For MySQL 8, use performance_schema

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

MySQL captures transaction and lock information in the INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS INFORMATION_SCHEMA tables.

• INNODB_TRX provides us with information on the currently executing transactions.
• INNODB_LOCKS gives us information on current lock.
• INNODB_LOCK_WAITS provides information on who is actually waiting for locks. 

The query below enables one to see active locks filtered by a particular database:

SELECT  
    tw_ps.DB waiting_trx_db,  
    r.trx_id waiting_trx_id,  
    r.trx_mysql_thread_id waiting_thread,  
    r.trx_query waiting_query,  
    bt_ps.DB blocking_trx_db,  
    b.trx_id blocking_trx_id,  
    b.trx_mysql_thread_id blocking_thread,  
    b.trx_query blocking_query  
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.processlist tw_ps ON tw_ps.ID = r.trx_id  
                INNER JOIN information_schema.processlist bt_ps ON bt_ps.ID = b.trx_id  
WHERE  r.trx_id in (Select ID FROM information_schema.processlist where DB='YOUR_DB_NAME') ; 

SELECT 
    'requested query',
    ilw.*,
    pcr.*,
    ilr.*,
    'block query',
    itb.trx_query blk_qry,
    itb.trx_rows_modified,
    itb.trx_rows_locked,
    pcb.*,
    ilb.*
FROM
    information_schema.innodb_lock_waits ilw,
    information_schema.innodb_locks ilr,
    information_schema.innodb_locks ilb,
    information_schema.innodb_trx itr,
    information_schema.innodb_trx itb,
    information_schema.processlist pcr,
    information_schema.processlist pcb
WHERE
    ilw.requested_lock_id = ilr.lock_id
        AND ilw.blocking_lock_id = ilb.lock_id
        AND ilr.lock_trx_id = itr.trx_id
        AND ilb.lock_trx_id = itb.trx_id
        AND pcb.ID = itb.trx_mysql_thread_id
        AND pcr.ID = itr.trx_mysql_thread_id;

SELECT 
    COUNT(*)
FROM
    information_schema.innodb_lock_waits ilw;

To quickly kill multiple processes:

select GROUP_CONCAT(stat SEPARATOR ' ') from (select concat('KILL ',id,';') as stat from information_schema.processlist) as stats;