How to get Active Transactions and 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;

    Comments

    Popular posts from this blog

    Check MySQL query history from command line

    Installing chocolatey on Windows