mysql查询锁定表 [英] mysql query locking tables

问题描述

有没有一种方法可以检查哪个查询在mysql中锁定了表?我们可以确定哪些表被锁定，但是很难找到锁定表的查询.我们尝试了显示进程列表，显示引擎的innodb状态，还查看了一些表，例如INNODB_TRX和INNODB_LOCKS.是否有一种特定的方法可以指出导致锁定的原因?

Hi Is there a way of checking which query is locking tables in mysql? We can Identify which tables are locked however Its difficult to find the query that is locking tables. We have tried show processlist, show engine innodb status, and also looked at some tables such INNODB_TRX and INNODB_LOCKS. Is there a specific method that can point out what is causing the locks?

推荐答案

如果您具有对mysql服务的root访问权限:

If you have root access to mysql service:

#mysql -uroot
#mysql> SET global log_output = 'table';
#mysql> SET global general_log = 1;

重新加载网站或执行锁定表的操作

reload site or do something that locks table

#mysql> SET global general_log = 0;

然后检查表mysql.general_log

如果您没有root权限访问在root/lib/Varien/Db/Adapter/Pdo/Mysql.php中设置的mysql服务:

If you haven't root access to mysql service set in root/lib/Varien/Db/Adapter/Pdo/Mysql.php:

protected $_debug = true;
protected $_logAllQueries = true;

重新加载页面后，您将在var/debug/pdo_mysql.log

After reload page you'll get query log in var/debug/pdo_mysql.log

这篇关于mysql查询锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！