InnoDB什么时候超时而不是报告死锁？ [英] When does InnoDB time out instead of reporting deadlock?

问题描述

我有一个无法复制或诊断的MySQL错误消息，提示超出了锁定等待超时。我确定它是僵局的（相对于事务先抓住一个锁然后再拨动它的拇指），因为我的日志显示另一个进程同时开始，也挂起，然后在第一次超时时继续。但是通常，InnoDB可以检测死锁而不会超时。因此，我试图了解为什么未检测到此死锁。

I have a "Lock wait timeout exceeded" error from MySQL that I can't reproduce or diagnose. I'm sure it's deadlock (as opposed to a transaction grabbing a lock then twiddling its thumbs), because my logs show that another process started at the same time, also hung, then continued when the first timed out. But normally, InnoDB detects deadlocks without timing out. So I am trying to understand why this deadlock was not detected.

两个事务都使用隔离级别可序列化。 （我对这种隔离级别的InnoDB锁定有一定的了解。）事务中使用了一个非InnoDB（MyISAM）表，我将其插入并更新。但是，我不了解死锁可能如何涉及到它，因为我相信MyISAM只是在插入和更新过程中使用了表锁（由于MyISAM不是事务性的，因此会立即释放它），因此在此期间不会再使用其他锁。表锁被保持。

Both transactions are using isolation level serializable. (I have a fair understanding of InnoDB locking in this isolation level.) There is one non-InnoDB (MyISAM) table used in the transaction, which I insert into and update. However, I don't understand how it could be involved in the deadlock, because I believe MyISAM just takes a table lock during the inserts and updates (then immediately releases it since MyISAM is not transactional), so no other lock is taken while this table lock is held.

因此，我确信死锁仅涉及InnoDB表，这使我回到为什么未检测到它的问题。 MySQL文档（http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html）暗示，死锁检测几乎总是可以工作的。我在搜索时发现的问题案例涉及显式的锁定表，变更表和插入延迟。我没有做任何这些事情，只是插入，更新和选择（我的某些选择是用于更新）。

So I'm convinced that the deadlock involves only InnoDB tables, which brings me back to the question of why it was not detected. The MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html) implies that deadlock detection pretty much always works. The problem cases I found while searching involve things like explicit "lock table", "alter table", and "insert delayed". I'm not doing any of these things, just inserts, updates, and selects (some of my selects are "for update").

我试图通过创建来复制一个MyISAM表和几个InnoDB表，并在MyISAM中进行各种插入和更新序列，并在InnoDB中选择更新。但是每次我产生一个死锁时，InnoDB都会立即报告它。我无法重现超时。

I tried to reproduce by creating one MyISAM table and a couple InnoDB tables and doing various sequences of insert and update into MyISAM, and "select for update"s in InnoDB. But every time I produced a deadlock, InnoDB reported it immediately. I could not reproduce a timeout.

还有其他诊断方法吗？我正在使用mysql 5.1.49。

Any other tips for diagnosing this? I am using mysql 5.1.49.

推荐答案

一个技巧是可以使用 SHOW INNODB STATUS 可以显示InnoDB引擎的状态。

One tip is that you can use SHOW INNODB STATUS to, you guessed it, show the status of the InnoDB engine.

返回的信息（大量文本）包括当前信息表锁和最后一个检测到的死锁（在标题 LATEST DETECTED DEADLOCK 下），因此此技巧在事实发生后并没有太大用处，但可以为您提供帮助

The information it returns (a big hunk of text) includes info on current table locks, and the last detected deadlock (under the heading "LATEST DETECTED DEADLOCK"), so this trick isn't that useful well after the fact, but it can help you track down a hung query while it's happening.

mysqladmin debug 还可以打印有用的锁调试信息。

mysqladmin debug can also print useful lock-debugging information.

第三个技巧是创建一个名为 innodb_lock_monitor 的魔术表，如 http://dev.mysql.com/doc/refman/5.1/en/innodb-monitors.html 提供更详细的锁定调试。

A third trick is to create a magically-named table called innodb_lock_monitor as described at http://dev.mysql.com/doc/refman/5.1/en/innodb-monitors.html which gives more detailed lock debugging.

HTH！

UPDATE >：

可能因为实际上不是死锁，所以可以检测到死锁，但是一个进程可能正在等待另一进程锁定的行上的行锁。从 innodb_lock_wait_timeout 变量：

It may not be detecting a deadlock becuase it isn't actually a deadlock, but more likely that one process is waiting for a row lock on a row that is locked by another process. From the manual for the innodb_lock_wait_timeout variable:

InnoDB
事务可能等待一行的超时时间（以秒为单位）在放弃之前锁定
。默认值为
50秒。尝试
访问被
锁定的行的另一个InnoDB事务将在
发出以下错误之前，将
挂起最多这么多秒：

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

错误1205（HY000）：超出了锁定等待超时
;尝试重新启动事务
发生锁定等待超时时，不会执行
当前语句。
当前交易不会回滚
。 （直到MySQL 5.0.13，如果
a锁定等待超时发生，InnoDB
会回滚整个事务。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened.

A例如，当两个进程各自需要锁定被另一个进程锁定的行，而没有大量等待将解决冲突时，就会发生死锁。

A deadlock occurs, for example, when two processes each need to lock rows that are locked by the other process, and no amount of waiting will resolve the conflict.

这篇关于InnoDB什么时候超时而不是报告死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！