MySQL 锁表后快速解决方法 及 MySQL中的锁

(1) 遇到锁表快速解决办法

　　依次执行1-6步，运行第6步生成的语句即可。

　　如果特别着急，运行 1 2 6 步 以及第6步生成的kill语句 即可。 

第1步　查看表是否在使用。

show open tables where in_use > 0 ;

如果查询结果为空。则证明表没有在使用。结束。

mysql>  show open tables where in_use > 0 ;
Empty set (0.00 sec)

 

如果查询结果不为空，继续后续的步骤。

mysql>  show open tables where in_use > 0 ;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t     |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

 

第2步　查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程。

show processlist;

注意：show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。
SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field. 　

 

第3步　当前运行的所有事务

SELECT * FROM information_schema.INNODB_TRX;

 

第4步　当前出现的锁

SELECT * FROM information_schema.INNODB_LOCKs;

 

第5步　锁等待的对应关系

SELECT * FROM information_schema.INNODB_LOCK_waits;

看事务表INNODB_TRX，里面是否有正在锁定的事务线程，看看ID是否在show processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。
搜索的结果是在事务表发现了很多任务，这时候最好都kill掉。

 

第6步　批量删除事务表中的事务

这里用的方法是：通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。

 

SELECT concat('KILL ',id,';') 
FROM information_schema.processlist p 
INNER JOIN  information_schema.INNODB_TRX x 
ON p.id=x.trx_mysql_thread_id 
WHERE db='test';

记得修改对应的数据库名。

这个语句执行后结果如下：

 

mysql>  SELECT concat('KILL ',id,';')  FROM information_schema.processlist p  INNER JOIN  information_schema.INNODB_TRX x  ON p.id=x.trx_mysql_thread_id  WHERE db='test';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 42;               |
| KILL 40;               |
+------------------------+
2 rows in set (0.00 sec)

 

 

执行结果里的两个kill语句即可解决锁表。 

 

摘自：https://weikeqin.com/2019/09/05/mysql-lock-table-solution/