在我的生产错误日志中,我偶尔会看到:

SQLSTATE[HY000]:一般错误:1205 超过锁等待超时;试一试 重新启动事务

我知道哪个查询在那个时刻试图访问数据库,但是是否有一种方法可以找出哪个查询在那个精确的时刻拥有锁?


当前回答

你可以使用:

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读视图”消息是否与冻结锁相关,但与其他活动事务不同的是,这个事务不显示与发出的查询一起,而是声称事务正在“清理”,但有多个行锁)

这个故事的寓意是,即使线程处于睡眠状态,事务也可以是活动的。

如果您正在使用JDBC,那么您可以选择 includeInnodbStatusInDeadlockExceptions = true

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

争用越多,出现死锁的可能性就越大,DB引擎将通过对其中一个死锁事务进行超时处理来解决这个问题。

此外,已修改(例如UPDATE或DELETE)大量条目的长时间运行的事务更有可能与其他事务产生冲突。

虽然InnoDB MVCC,你仍然可以使用FOR UPDATE子句请求显式锁。然而,与其他流行的db (Oracle, MSSQL, PostgreSQL, DB2)不同,MySQL使用REPEATABLE_READ作为默认隔离级别。

现在,您获得的锁(通过修改行或使用显式锁定)将在当前运行的事务期间保持。如果你想了解REPEATABLE_READ和READ COMMITTED在锁方面的区别,请阅读这篇Percona文章。

在REPEATABLE READ中,事务期间获得的每个锁都被持有 在事务持续时间内。 在READ COMMITTED中,不匹配扫描的锁在STATEMENT完成后被释放。 ... 这意味着在READ COMMITTED中,其他事务可以自由地更新它们在update语句完成后无法更新的行(在REPEATABLE READ中)。

因此:隔离级别(REPEATABLE_READ, SERIALIZABLE)越严格,死锁的可能性就越大。这不是“本质上”的问题,而是一种权衡。

使用READ_COMMITTED可以获得非常好的结果,因为在使用跨越多个HTTP请求的逻辑事务时,需要防止应用程序级的丢失更新。乐观锁定方法针对即使使用SERIALIZABLE隔离级别也可能发生的丢失更新,同时通过允许使用READ_COMMITTED来减少锁争用。

从上面Rolando的回答推断,是这些阻碍了你的查询:

---TRANSACTION 0 620783788, not started, process no 29956, OS thread id 1196472640
MySQL thread id 5341773, query id 189708353 10.64.89.143 viget

如果你需要执行你的查询,不能等待其他人运行,使用MySQL线程id杀死他们:

kill 5341773 <replace with your thread id>

(从mysql内部,而不是shell,显然)

你必须找到线程id从:

show engine innodb status\G

命令,并找出哪个是阻塞数据库的程序。

看看pt-deadlock-logger实用程序的手册页:

brew install percona-toolkit
pt-deadlock-logger --ask-pass server_name

它从上面提到的引擎innodb状态中提取信息 它可以用来创建一个守护进程,每30秒运行一次。