mysql 定位问题集锦(持续更新...)

事务

// 查询当前数据库执行时间在10秒以上的事务
SELECT t.trx_mysql_thread_id
             ,t.trx_state
             ,t.trx_tables_in_use
             ,t.trx_tables_locked
             ,t.trx_query
             ,t.trx_rows_locked 
             ,t.trx_rows_modified
             ,t.trx_lock_structs
             ,t.trx_started
             ,t.trx_isolation_level
             ,p.time 
             ,p.user
             ,p.host
             ,p.db
             ,p.command
       FROM   information_schema.INNODB_TRX t 
              INNER JOIN information_schema.PROCESSLIST p 
                      ON t.trx_mysql_thread_id = p.id 
       WHERE  t.trx_state = 'RUNNING'
              AND p.time >= 10
              AND p.command = 'Sleep' limit 100

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