本文详细介绍了在使用MySQL MyISAM引擎时遇到锁表和死锁问题的解决方法，通过展示如何使用`SHOW PROCESSLIST`命令检查当前进程，并利用`KILL`命令终止锁定进程来释放资源，避免服务因长时间等待而出现停顿。同时，通过`SHOW INNODB STATUS`检查InnoDB引擎状态，进一步诊断并定位问题。
mysql使用myisam的时候锁表比较多，尤其有慢查询的时候，造成死锁.这时需要手动kill掉locked的process.使他释放.
我们使用Show innodb status检查引擎状态，以发现了死锁问题。
(以前我都是重起服务)..惭愧啊
演示:(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)
我们使用Show innodb status检查引擎状态，以发现了死锁问题。
(以前我都是重起服务)..惭愧啊
演示:(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)
扫一扫
548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
