在我的生产错误日志中,我偶尔会看到:
SQLSTATE[HY000]:一般错误:1205 超过锁等待超时;试一试 重新启动事务
我知道哪个查询在那个时刻试图访问数据库,但是是否有一种方法可以找出哪个查询在那个精确的时刻拥有锁?
在我的生产错误日志中,我偶尔会看到:
SQLSTATE[HY000]:一般错误:1205 超过锁等待超时;试一试 重新启动事务
我知道哪个查询在那个时刻试图访问数据库,但是是否有一种方法可以找出哪个查询在那个精确的时刻拥有锁?
当前回答
下面是我最终不得不做的事情,以找出是什么“其他查询”导致了锁定超时问题。在应用程序代码中,我们在专用于此任务的单独线程上跟踪所有挂起的数据库调用。如果任何DB调用的时间超过n秒(对我们来说是30秒),我们记录:
-- Pending InnoDB transactions
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;
-- Optionally, log what transaction holds what locks
SELECT * FROM information_schema.innodb_locks;
通过上述方法,我们能够精确定位锁定导致死锁的行的并发查询。在我的例子中,它们是像INSERT…与普通SELECT不同,SELECT锁定底层行。然后可以重新组织代码或使用不同的事务隔离(如read uncommitted)。
好运!
其他回答
你可以使用:
show full processlist
它将列出MySQL中的所有连接和连接的当前状态以及正在执行的查询。还有一个更短的变种show processlist;它显示截断的查询以及连接统计信息。
正如有人在关于这个问题的众多SO线程中的一个线程中提到的:有时已经锁定表的进程在进程列表中显示为休眠!我非常着急,直到我杀死了数据库中打开的所有睡眠线程(当时没有一个是活动的)。这最终解锁了表并让更新查询运行。
评论者说了类似于“有时MySQL线程锁定了一个表,然后在等待与MySQL无关的事情发生时进入睡眠状态。”
在重新检查了show engine innodb状态日志后(一旦我找到了负责锁的客户端),我注意到被卡住的线程被列在事务列表的最底部,在由于锁冻结而即将出错的活动查询下面:
------------------
---TRANSACTION 2744943820, ACTIVE 1154 sec(!!)
2 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 276558, OS thread handle 0x7f93762e7710, query id 59264109 [ip] [database] cleaning up
Trx read view will not see trx with id >= 2744943821, sees < 2744943821
(不确定“Trx读视图”消息是否与冻结锁相关,但与其他活动事务不同的是,这个事务不显示与发出的查询一起,而是声称事务正在“清理”,但有多个行锁)
这个故事的寓意是,即使线程处于睡眠状态,事务也可以是活动的。
在我的例子中,我只能使用SELECT命令查询数据库,而不能使用UPDATE和DELETE命令。
这个问题是由于一根线卡住了。它没有响应,状态为“sleep”。所以,我必须杀了它。
1-查找线程ID:
select * FROM information_schemaprocesslist ORDER BY id
2-对我来说,它返回了两个线程。现在的那个和卡住的那个。我使用“kill THREAD_ID”来杀死它。
更多信息请看这里 https://oracle-base.com/articles/mysql/mysql-killing-threads
激活MySQL general.log(磁盘密集型)并使用mysql_analyse_general_log.pl来提取长时间运行的事务,例如:
——min-duration=你的innodb_lock_wait_timeout值
然后禁用general.log。
这个异常的最大问题是,它通常在测试环境中不可重现,当它发生在prod上时,我们无法运行innodb引擎状态。所以在其中一个项目中,我把下面的代码放入了这个异常的catch块中。这帮助我在异常发生时捕捉引擎状态。这帮了大忙。
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SHOW ENGINE INNODB STATUS");
while(rs.next()){
log.info(rs.getString(1));
log.info(rs.getString(2));
log.info(rs.getString(3));
}