PT导致元数据锁/锁设计

 

pager less ;

show processlist;

找出时间最长的业务线程id或者个人账号连接线程id
截图保留信息

 

kill掉占用锁的线程id
nopager;

#####################

第二种方法：

select * from INNODB_TRX\G

找到trx_mysql_thread_id  kill掉。

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  能看到blocking_trx_id，非线程id.

show processlist;判断占用锁的线程id.

#########################

5.6版本

行锁使用：

  SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, TIMESTAMPDIFF ( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ) AS wait_time, r.trx_query AS waiting_query, l.lock_table AS waiting_table_lock, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_HOST, SUBSTRING(p.host, INSTR(p.host, ':') + 1) AS blocking_port, IF(p.command = "Sleep", p.time, 0) AS idle_in_trx, b.trx_query AS blocking_query 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 r.trx_id = w.requesting_trx_id INNER JOIN information_schema.innodb_locks AS l ON w.requested_lock_id = l.lock_id LEFT JOIN information_schema.processlist AS p ON p.id = b.trx_mysql_thread_id ORDER BY wait_time\G

#########################

select * from information_schema.processlist where time > 1800 and STATE not like "Master%";