如何调试MySQL上的锁定等待超时？

Question

如何调试MySQL上的锁定等待超时？在我的生产错误日志中，我偶尔会看到：SQLSTATE [HY000]：常规错误：1205超出锁定等待超时; 尝试重新启动事务我知道当时哪个查询正在尝试访问数据库，但有没有办法找出哪个查询在那个精确时刻有锁定？

Answer 1

正如在有关此问题的众多SO线程之一中提到的那样：有时锁定表的进程显示为在进程列表中睡眠！我正在撕扯我的头发，直到我杀死所有在相关数据库中打开的睡眠线程（当时没有一个是活跃的）。最终解锁了表并让更新查询运行。

这位评论者说了一句类似于“有时一个MySQL线程会锁定一个表，然后在等待与MySQL无关的事情发生时休眠。”

在重新审核show engine innodb status日志之后（一旦我跟踪了负责锁定的客户端），我发现有问题的卡住线程列在事务列表的最底部，位于即将发生错误的活动查询下方由于冻结锁定：

------------------

---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读取视图”消息是否与冻结锁相关，但与其他活动事务不同，此消息不会显示已发出的查询，而是声称事务处于“清理”状态，但具有多个行锁）

故事的寓意是，即使线程处于休眠状态，事务也可以处于活动状态。

Answer 2

由于MySQL的普及，难怪锁定等待超时; 尝试重启事务异常得到如此多的关注SO。

您拥有的争用越多，死锁的可能性就越大，数据库引擎将通过暂停其中一个死锁事务来解决这个问题。此外，已修改（例如UPDATE或DELETE）大量条目（如“ 高性能Java持久性”一书中所述，它采用锁以避免脏写异常）的长时间运行事务更有可能与其他事务产生冲突。

虽然InnoDB MVCC，您仍然可以使用该FOR UPDATE子句请求显式锁。但是，与其他流行的数据库（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_COMMITED，因为在使用跨越多个HTTP请求的逻辑事务时，您需要应用程序级丢失更新防护。在乐观锁定方法的目标丢失更新，如果你使用的是甚至可能发生SERIALIZABLE隔离级别，同时通过允许您使用减少了锁争用READ_COMMITED。