解决 MySQL 错误“尝试获取锁时发现死锁；尝试重新启动事务”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2596005/
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
Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction"
提问by Anon Gordon
I have a MySQL table with about 5,000,000 rows that are being constantly updated in small ways by parallel Perl processes connecting via DBI. The table has about 10 columns and several indexes.
我有一个包含大约 5,000,000 行的 MySQL 表，这些行通过 DBI 连接的并行 Perl 进程以小方式不断更新。该表有大约 10 列和几个索引。
One fairly common operation gives rise to the following error sometimes:
一种相当常见的操作有时会引起以下错误：
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.
The SQL statement that triggers the error is something like this:
触发错误的 SQL 语句是这样的：
UPDATE file_table SET a_lock = 'process-1234' WHERE param1 = 'X' AND param2 = 'Y' AND param3 = 'Z' LIMIT 47
The error is triggered only sometimes. I'd estimate in 1% of calls or less. However, it never happened with a small table and has become more common as the database has grown.
该错误仅在某些时候触发。我估计只有 1% 或更少的电话。但是，它从未发生在小表上，并且随着数据库的增长而变得更加普遍。
Note that I am using the a_lock field in file_table to ensure that the four near-identical processes I am running do not try and work on the same row. The limit is designed to break their work into small chunks.
请注意，我在 file_table 中使用 a_lock 字段来确保我正在运行的四个几乎相同的进程不会尝试在同一行上工作。该限制旨在将他们的工作分成小块。
I haven't done much tuning on MySQL or DBD::mysql. MySQL is a standard Solaris deployment, and the database connection is set up as follows:
我没有对 MySQL 或 DBD::mysql 做太多调整。MySQL是标准的Solaris部署，数据库连接设置如下：
my $dsn = "DBI:mysql:database=" . $DbConfig::database . ";host=${DbConfig::hostname};port=${DbConfig::port}";
my $dbh = DBI->connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;
I have seen online that several other people have reported similar errors and that this may be a genuine deadlock situation.
我在网上看到其他几个人报告了类似的错误，这可能是真正的死锁情况。
I have two questions:
我有两个问题：
What exactly about my situation is causing the error above?
Is there a simple way to work around it or lessen its frequency? For example, how exactly do I go about "restarting transaction at Db.pm line 276"?
我的情况究竟是什么导致了上述错误？
有没有一种简单的方法可以解决它或降低它的频率？例如，“在 Db.pm 第 276 行重新启动事务”我究竟该怎么做？
Thanks in advance.
提前致谢。
回答by zombat
If you are using InnoDB or any row-level transactional RDBMS, then it is possible that anywrite transaction can cause a deadlock, even in perfectly normal situations. Larger tables, larger writes, and long transaction blocks will often increase the likelihood of deadlocks occurring. In your situation, it's probably a combination of these.
如果您使用 InnoDB 或任何行级事务 RDBMS，那么任何写事务都可能导致死锁，即使在完全正常的情况下也是如此。更大的表、更大的写入和长事务块通常会增加发生死锁的可能性。在你的情况下，它可能是这些的组合。
The only way to truly handle deadlocks is to write your code to expect them. This generally isn't very difficult if your database code is well written. Often you can just put a try/catcharound the query execution logic and look for a deadlock when errors occur. If you catch one, the normal thing to do is just attempt to execute the failed query again.
真正处理死锁的唯一方法是编写您的代码以期待它们。如果您的数据库代码编写得很好，这通常不是很困难。通常，您可以try/catch在查询执行逻辑周围放置一个，并在发生错误时查找死锁。如果您捕获到一个，通常要做的就是尝试再次执行失败的查询。
I highly recommend you read this pagein the MySQL manual. It has a list of things to do to help cope with deadlocks and reduce their frequency.
我强烈建议您阅读MySQL 手册中的此页面。它有一个要做的事情列表，以帮助处理死锁并减少它们的频率。
回答by Ross
The answer is correct, however the perl documentation on how to handle deadlocks is a bit sparse and perhaps confusing with PrintError, RaiseError and HandleError options. It seems that rather than going with HandleError, use on Print and Raise and then use something like Try:Tiny to wrap your code and check for errors. The below code gives an example where the db code is inside a while loop that will re-execute an errored sql statement every 3 seconds. The catch block gets $_ which is the specific err message. I pass this to a handler function "dbi_err_handler" which checks $_ against a host of errors and returns 1 if the code should continue (thereby breaking the loop) or 0 if its a deadlock and should be retried...
答案是正确的，但是关于如何处理死锁的 perl 文档有点稀疏，可能会与 PrintError、RaiseError 和 HandleError 选项混淆。似乎与其使用 HandleError，不如在 Print and Raise 上使用，然后使用 Try:Tiny 之类的东西来包装您的代码并检查错误。下面的代码给出了一个示例，其中 db 代码位于 while 循环中，该循环将每 3 秒重新执行一次出错的 sql 语句。catch 块获得 $_ 这是特定的错误消息。我将它传递给一个处理函数“dbi_err_handler”，它检查 $_ 是否有一系列错误，如果代码应该继续（从而打破循环），则返回 1，如果它是死锁并且应该重试，则返回 0...
$sth = $dbh->prepare($strsql);
my $db_res=0;
while($db_res==0)
{
$db_res=1;
try{$sth->execute($param1,$param2);}
catch
{
print "caught $_ in insertion to hd_item_upc for upc $upc\n";
$db_res=dbi_err_handler($_);
if($db_res==0){sleep 3;}
}
}
dbi_err_handler should have at least the following:
dbi_err_handler 至少应具有以下内容：
sub dbi_err_handler
{
my($message) = @_;
if($message=~ m/DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction/)
{
$caught=1;
$retval=0; # we'll check this value and sleep/re-execute if necessary
}
return $retval;
}
You should include other errors you wish to handle and set $retval depending on whether you'd like to re-execute or continue..
您应该包括您希望处理的其他错误并根据您是要重新执行还是继续设置 $retval ..
Hope this helps someone -
希望这对某人有所帮助-
回答by Web Server Expert
Note that if you use SELECT FOR UPDATEto perform a uniqueness check before an insert, you will get a deadlock for every race condition unless you enable the innodb_locks_unsafe_for_binlogoption. A deadlock-free method to check uniqueness is to blindly insert a row into a table with a unique index using INSERT IGNORE, then to check the affected row count.
请注意，如果您SELECT FOR UPDATE习惯于在插入之前执行唯一性检查，除非您启用该innodb_locks_unsafe_for_binlog选项，否则每个竞争条件都会导致死锁。检查唯一性的无死锁方法是使用 盲目地将行插入具有唯一索引的表中INSERT IGNORE，然后检查受影响的行数。
add below line to my.cnffile
将下面的行添加到my.cnf文件中
innodb_locks_unsafe_for_binlog = 1
innodb_locks_unsafe_for_binlog = 1
#1 - ON
0 - OFF
1 - 开
0 - 关
回答by Web Server Expert
The idea of retrying the query in case of Deadlock exception is good, but it can be terribly slow, since mysql query will keep waiting for locks to be released. And incase of deadlock mysql is trying to find if there is any deadlock, and even after finding out that there is a deadlock, it waits a while before kicking out a thread in order to get out from deadlock situation.
在死锁异常的情况下重试查询的想法是好的，但它可能会非常慢，因为 mysql 查询将一直等待锁被释放。并且在死锁的情况下，mysql 试图查找是否存在任何死锁，即使在发现存在死锁之后，它也会在踢出线程之前等待一段时间以摆脱死锁情况。
What I did when I faced this situation is to implement locking in your own code, since it is the locking mechanism of mysql is failing due to a bug. So I implemented my own row level locking in my java code:
当我遇到这种情况时，我所做的是在自己的代码中实现锁定，因为它是由于错误导致mysql的锁定机制失败。所以我在我的 java 代码中实现了我自己的行级锁定：
private HashMap<String, Object> rowIdToRowLockMap = new HashMap<String, Object>();
private final Object hashmapLock = new Object();
public void handleShortCode(Integer rowId)
{
Object lock = null;
synchronized(hashmapLock)
{
lock = rowIdToRowLockMap.get(rowId);
if (lock == null)
{
rowIdToRowLockMap.put(rowId, lock = new Object());
}
}
synchronized (lock)
{
// Execute your queries on row by row id
}
}
