How to get Active Transactions and Locks from MySQL https://jobs.goyun.info/2020/07/how-to-get-active-transactions-and.html

locate MySQL dead lock.sql

SELECT 
    r.trx_wait_started AS wait_started,
    TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
    rl.lock_table AS locked_table,
    rl.lock_index AS locked_index,
    rl.lock_type AS locked_type,
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_pid,
    r.trx_query AS waiting_query,
    rl.lock_id AS waiting_lock_id,
    rl.lock_mode AS waiting_lock_mode,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_pid,
    b.trx_query AS blocking_query,
    bl.lock_id AS blocking_lock_id,
    bl.lock_mode AS blocking_lock_mode
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.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id
        INNER JOIN
    information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;