Mysql遇到锁表快速解决办法

  • 一次执行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 用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程(即与您正在使用的MySQL帐户关联的线程),看不到其它用户正在运行的线程。如果不使用FULL关键字,Info字段中只显示每条语句的前100个字符。

第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语句即可解决锁表。