通过information_schema查询MySQL的锁定状态

注意：MySQL版本必须>=5.1

如果看不到INNODB_LOCKS表，请用SHOW VARIABLES检查innodb_version变量。如果没有看到这个变量，说明你还没有使用InnoDB插件，你需要他。如果你看到了这个变量但没有那些表，那么你需要确保服务器配置文件的plugin_load设置中明确包括了那些表，详情请查阅MySQL用户手册。

下面的查询显示了谁阻塞和谁在等待，以及等待了多久：
    SELECT    
        r.trx_id waiting_trx_id,    
        r.trx_mysql_thread_id waiting_thread,    
        TIMESTAMPDIFF(    
            SECOND,    
            r.trx_wait_started,    
            CURRENT_TIMESTAMP    
        ) wait_time,    
        r.trx_query waiting_query,    
        l.lock_table waiting_table_lock,    
        b.trx_id blocking_trx_id,    
        b.trx_mysql_thread_id blocking_thread,    
        SUBSTRING(    
            p. HOST,    
            1,    
            INSTR(p. HOST, ':') - 1    
        ) blocking_host,    
        SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) blocking_port,    
        
    IF (p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx,    
     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    
    INNER JOIN information_schema.INNODB_LOCKS l ON w.requested_lock_id = l.lock_id   
    LEFT JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id    
    ORDER BY    
        wait_time DESC\G  

 下面查询显示了多少查询被哪些线程阻塞：

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