mysql锁相关查询

 以下查询来自于网络，收藏太久，源链接未能保存下来！

 

查询方法1：

SELECT 
  r.`trx_id` waiting_trx_id,
  r.`trx_mysql_thread_id` waiting_thread,
  r.`trx_query` waiting_query,
  b.`trx_id` bolcking_trx_id,
  b.`trx_mysql_thread_id` blocking_thread,
  b.`trx_query` block_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` ;

　　

查询方法2：

select  IFNULL(wt.trx_mysql_thread_id,1) BLOCKING_THREAD_ID,                           
t.trx_mysql_thread_id THREAD_ID,                                                       
CONCAT(p.user,'@',p.host) USER,                                                        
l.lock_table LOCK_TABLE,                                                               
l.lock_index LOCKED_INDEX,                                                             
l.lock_type LOCK_TYPE,                                                                 
l.lock_mode LOCK_MODE,                                                                 
CONCAT(                                                                                
FLOOR(HOUR(TIMEDIFF(now(), t.trx_wait_started)) / 24), 'day ',                         
MOD(HOUR(TIMEDIFF(now(), t.trx_wait_started)), 24), ':',                               
MINUTE(TIMEDIFF(now(), t.trx_wait_started)), ':',                                      
second(TIMEDIFF(now(), t.trx_wait_started))) AS WAIT_TIME,                             
t.trx_started TRX_STARTED,                                                             
t.trx_isolation_level TRX_ISOLATION_LEVEL,                                             
t.trx_rows_locked TRX_ROWS_LOCKED,                                                     
t.trx_rows_modified TRX_ROWS_MODIFIED,                                                 
p.info SQL_TEXT                                                                        
from   INFORMATION_SCHEMA.INNODB_TRX t                                                 
LEFT  JOIN information_schema.innodb_lock_waits w on t.trx_id = w.requesting_trx_id    
LEFT  JOIN information_schema.innodb_trx wt       on wt.trx_id = w.blocking_trx_id     
INNER JOIN information_schema.innodb_locks l      on l.lock_trx_id = t.trx_id          
INNER JOIN information_schema.processlist p       on t.trx_mysql_thread_id = p.id      
ORDER BY 1;

 

查询方法3：

select
 'Blocker' role,
    p.id,
    p.user,
    left(p.host, locate(':', p.host) - 1) host,
    tx.trx_id,
    tx.trx_state,
    tx.trx_started,
 timestampdiff(second, tx.trx_started, now()) duration,
 lo.lock_mode,
 lo.lock_type,
 lo.lock_table,
 lo.lock_index,
    tx.trx_query,
    tx.trx_tables_in_use,
    tx.trx_tables_locked,
    tx.trx_rows_locked
from
    information_schema.innodb_trx tx,
    information_schema.innodb_lock_waits lw,
 information_schema.innodb_locks lo,
    information_schema.processlist p
where
    lw.blocking_trx_id = tx.trx_id
 and p.id = tx.trx_mysql_thread_id
 and lo.lock_trx_id = tx.trx_id
union all
select
    'Blockee' role,
    p.id,
    p.user,
    left(p.host, locate(':', p.host) - 1) host,
    tx.trx_id,
    tx.trx_state,
    tx.trx_started,
 timestampdiff(second, tx.trx_started, now()) duration,
 lo.lock_mode,
 lo.lock_type,
 lo.lock_table,
 lo.lock_index,
    tx.trx_query,
    tx.trx_tables_in_use,
    tx.trx_tables_locked,
    tx.trx_rows_locked
from
    information_schema.innodb_trx tx,
    information_schema.innodb_lock_waits lw,
 information_schema.innodb_locks lo,
    information_schema.processlist p
where
    lw.requesting_trx_id = tx.trx_id
 and p.id = tx.trx_mysql_thread_id
 and lo.lock_trx_id = tx.trx_id \G