首页 > 代码库 > MySQL定位锁争用比较严重的表

MySQL定位锁争用比较严重的表

1:查看当前的事务

mysql> SELECT * FROM information_schema.innodb_trx \G

2:查看当前锁定的事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3:查看当前等锁的事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 

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;

  

 



MySQL定位锁争用比较严重的表