5.5以上版本查询锁信息

select
r.trx_id waiting_trx_id,r.trx_mysql_thread_idwaiting_thread,r.trx_query waiting_query,lr.lock_modewaiting_lock_mode,
b.trx_id running_trx_id,b.trx_mysql_thread_idrunning_thread,b.trx_query running_query,lb.lock_moderunning_lock_mode
from INNODB_LOCK_WAITS w inner join INNODB_TRX b
on (b.trx_id=w.blocking_trx_id) inner join INNODB_LOCKS lb on(b.trx_id=lb.lock_trx_id)
inner join INNODB_TRX r
on(r.trx_id=w.requesting_trx_id) inner join INNODB_LOCKS lr on(r.trx_id=lr.lock_trx_id) \G
*************************** 1. row***************************
 

waiting_trx_id: 548500
  waiting_thread: 111
   waiting_query: select * fromcly where id=1 lock in share mode
waiting_lock_mode: S
  running_trx_id: 548499
  running_thread: 110
   running_query: NULL
running_lock_mode: X