MySql锁表解决(死)

1. show open tables where In_use>0;  查看被锁的表
In_use show how many threads are currently using this table, meaning have it locked or waiting on the table lock for this table to lock it.
2.show processlist;  查看所有执行线程

如果在线上不慎执行了一个复杂sql，ctrl + c不一定会终止sql在服务端的执行，可以通过以上两个sql，检查表是否被锁，以及执行线程是否终结。如果还在执行，手动kill一下线程

演示:(id 7是我用python 来连过来的一个会话,虽然是状态是sleep,为了演示，干掉他)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  7 | root | localhost | yy   | Sleep   |  154 |       | NULL             | 
|  8 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql> kill 7;

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  8 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)