Queries to use and diagnose blocking and locking threads 

... only works in MySQL version 5.5.x

This is the Query that is blocking :

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

Empty set (0.00 sec)

=======================================================================

Tell you how long it has waited and from which host and port

... only works in MySQL version 5.5.x

--------------
select r.trx_id as waiting_trx_id, r.trx_mysql_thread_id as waiting_thread,
 timestampdiff(second, r.trx_wait_started, current_timestamp) as wait_time,
 r.trx_query as waiting_query,
 l.lock_table as waiting_table_lock,
 b.trx_id as blocking_trx_id, b.trx_mysql_thread_id as blocking_thread,
 substring(p.host, 1, instr(p.host, ':') - 1) as blocking_host,
 substring(p.host,    instr(p.host, ':') + 1) as blocking_port,
 if(p.command = "Sleep" , p.time, 0) as idle_in_trx,
 b.trx_query as blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS r ON b.trx_id = w.requesting_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
LEFT  JOIN INFORMATION_SCHEMA.PROCESSLIST  AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC
--------------

Empty set (0.00 sec)

========================================================================

... show how many queries are blocked on which threads...

--------------
SELECT CONCAT('thread ' , b.trx_mysql_thread_id , ' from ' , p.host) as who_blocks,
       IF(p.command = "Sleep", p.time, 0 ) as idle_in_trx,
       MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW() )) as max_wait_time,
       COUNT(*) as num_waiters
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS r ON b.trx_id = w.requesting_trx_id
LEFT  JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks ORDER BY num_waiters DESC
--------------

Empty set (0.00 sec)