如何检测 Mysql/innodb 中的死锁？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/643122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to detect deadlocks in Mysql / innodb?
提问by boz
I know that deadlocks occur inevitably when using transactions in Innodb and that they are harmless if they are treated properly by the application code - "just try it again", as the manual says.
我知道在 Innodb 中使用事务时不可避免地会发生死锁，并且如果应用程序代码正确处理它们，它们是无害的 - 正如手册所说，“再试一次”。
So I was wondering - how do you detect deadlocks? Does a deadlock issue some special mysql error number? I am using PHP's mysqli extension if that matters.
所以我想知道 - 你如何检测死锁？死锁是否会发出一些特殊的 mysql 错误号？如果重要的话，我正在使用 PHP 的 mysqli 扩展。
Thank you.
谢谢你。
Edit: solution found, see comments
编辑：找到解决方案，见评论
采纳答案by S.Lott
http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html
1213 (ER_LOCK_DEADLOCK)
Transaction deadlock. You should rerun the transaction.
1213 (ER_LOCK_DEADLOCK)
事务僵局。您应该重新运行事务。
回答by boz
"SHOW ENGINE INNODB STATUS" from the MySQL Command line client (not a query browser) will give you info on deadlocks.
来自 MySQL 命令行客户端（不是查询浏览器）的“SHOW ENGINE INNODB STATUS”将为您提供有关死锁的信息。
Deadlocks can also be caused by uncommitted transactions (usually program bugs) and the person who is running the uncommitted transaction will not see the problem as they will be working fine (through their data will not be committed).
死锁也可能是由未提交的事务（通常是程序错误）引起的，运行未提交事务的人不会看到问题，因为他们会正常工作（因为他们的数据不会被提交）。
回答by Sandy
回答by Andrei Sura
If you are on a mac:
如果您使用的是 mac：
$ brew install percona-toolkit
$ brew install percona-toolkit
$ pt-deadlock-logger -uroot --ask-pass localhost
$ pt-deadlock-logger -uroot --ask-pass 本地主机
回答by Mark Baaijens
I recently created a very simple check for deadlocks for the implementation of a smoke test of a web applciation. Code can be improved a lot, but it's working for now. See https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.htmlfor more info on the output of the used query below.
我最近创建了一个非常简单的死锁检查，以实现 Web 应用程序的冒烟测试。代码可以改进很多，但它现在正在工作。有关下面使用的查询输出的更多信息，请参阅https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.html。
$status = DB::select("SHOW ENGINE INNODB STATUS")["Status"]??null;
if(strpos($status,"LATEST DETECTED DEADLOCK") !== false)
{
trigger_error("LATEST DETECTED DEADLOCK section present in output of SHOW ENGINE INNODB STATUS");
}
if(strpos($status,"LATEST FOREIGN KEY ERROR") !== false)
{
trigger_error("LATEST FOREIGN KEY ERROR section present in output of SHOW ENGINE INNODB STATUS");
}
回答by Mark Baaijens
Try innotop, will detect the deadlock for you.
试试 innotop，会为你检测死锁。
