常用SQL

根据 INNODB_LOCK_WAITS 找到被锁的线程以及加锁的线程

SELECT
t1.requesting_thread_id,
t1.requesting_query,
trx2.trx_mysql_thread_id AS block_thread_id,
proc2.INFO AS block_query 
FROM
(
  SELECT
  trx1.trx_mysql_thread_id AS requesting_thread_id,
  proc1.INFO AS requesting_query,
  lock1.blocking_trx_id 
  FROM
  information_schema.INNODB_TRX AS trx1
  INNER JOIN information_schema.INNODB_LOCK_WAITS AS lock1 ON trx1.trx_id = lock1.requesting_trx_id
  INNER JOIN information_schema.`PROCESSLIST` AS proc1 ON trx1.trx_mysql_thread_id = proc1.ID 
) AS t1
INNER JOIN information_schema.INNODB_TRX AS trx2 ON trx2.trx_id = t1.blocking_trx_id
    INNER JOIN information_schema.`PROCESSLIST` AS proc2 ON trx2.trx_mysql_thread_id = proc2.ID