查询锁信息 定位阻塞线程

查询锁信息

1、select trx_id, trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

2、select * from information_schema.innodb_lock_waits;

3、select * from information_schema.innodb_locks;

most important 直接找出阻塞的查询线程并kill

4、select waiting_trx_id,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query,sql_kill_blocking_query,sql_kill_blocking_connection from sys.innodb_lock_waits;

查杀慢SQL

select concat('kill ',id,';') from information_schema.processlist where state <> '' and user = '***' and time > 10;