解决MySQL错误"Deadlock found when trying to get lock; try restarting transaction"的方法

Question

我有一个MySQL表格，其中包含约500万行数据，这些数据通过使用DBI连接的并行Perl进程进行小规模更新。该表格有大约10个列和多个索引。

有一个相当常见的操作有时会出现以下错误：

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.

导致错误的 SQL 语句大致如下：

UPDATE file_table SET a_lock = 'process-1234' WHERE param1 = 'X' AND param2 = 'Y' AND param3 = 'Z' LIMIT 47

错误只有在某些情况下才会被触发。我估计只有1%或更少的调用会出现这种情况。然而，在使用小表时从未发生过，随着数据库的增长，这种情况变得越来越普遍。

请注意，我在file_table中使用a_lock字段确保我运行的四个几乎相同的进程不会尝试处理同一行。这个限制被设计成将它们的工作分成小块。

我没有对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;

我在网上看到很多人报告了类似的错误，可能是真正的死锁情况。

我的问题有两个：

1. 我的情况具体导致了上述错误？

2. 有没有简单的方法来解决它或减少它的频率？例如，如何精确地执行“在Db.pm的第276行重启事务”？

提前感谢。

- Anon Gordon

---

要查看哪个表正在发生死锁以及导致死锁的一些数据，请运行 SHOW ENGINE INNODB STATUS。 - Ryan Shillington

Answer 1

如果您正在使用InnoDB或任何行级事务性关系型数据库管理系统，则即使在完全正常的情况下任何写事务都可能导致死锁。较大的表、较大的写入和长时间的事务块通常会增加死锁发生的可能性。在您的情况下，这可能是这些因素的结合。

真正处理死锁的唯一方法是编写代码以预期它们。如果您的数据库代码编写得很好，通常并不难实现。通常可以将try/catch放在查询执行逻辑周围，在出现错误时检查死锁。如果捕获到死锁，则通常需要尝试重新执行失败的查询。

我强烈建议您阅读MySQL手册中的此页面。它列出了一些可帮助应对死锁并减少其频率的措施。

- zombat

---

需要捕捉哪些错误代码呢？仅捕捉1205就足够了吗？在http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html中有超过900个错误代码。您如何知道所有需要捕捉的代码，以便为您的try/catch建议实现适当的解决方案？ - Pacerier

这是否意味着除了 InnoDB 或任何行级事务关系型数据库管理系统，其他数据库都没有这些问题？ - Vojtěch

@Pacerier，我认为1213也是必须的 - 它比1205更频繁地发生在我身上。请参阅https://mariadb.com/kb/en/mariadb-error-codes/。也许3058也应该重试。 - Serge Rogatch

Answer 2

答案：答案是正确的，但是关于如何处理死锁的Perl文档有点简略，也许与PrintError、RaiseError和HandleError选项有些混淆。似乎不应该使用HandleError，而是使用Print和Raise，然后使用类似Try:Tiny的东西来包装代码并检查错误。下面的代码给出了一个示例，在其中，数据库代码位于一个while循环内，该循环将重新执行每3秒钟的出错SQL语句。捕获块获取$_，它是特定的错误消息。我将其传递给一个处理程序函数"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 应至少具备以下内容：

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;
}

你应该包括你想要处理的其他错误，并根据你想要重新执行还是继续执行设置$retval。

希望这能对某人有所帮助 -

- Ross

Answer 3

请注意，如果您在插入之前使用SELECT FOR UPDATE进行唯一性检查，则会出现死锁情况，除非您启用了innodb_locks_unsafe_for_binlog选项。一个无死锁的检查唯一性的方法是使用INSERT IGNORE将一行数据盲目地插入到具有唯一索引的表中，然后检查受影响的行数。

请将以下行添加到my.cnf文件中： innodb_locks_unsafe_for_binlog = 1 1 - 启用
0 - 禁用

- Web Server Expert

---

这解决了我在多线程环境下保存ActiveRecord关联的所有问题。 - lightyrs

启用innodb_locks_unsafe_for_binlog可能会导致幻象问题，因为当禁用间隙锁定时，其他会话可以在间隙中插入新行。 - shivam

Answer 4

在发生死锁异常时重试查询的想法是好的，但这可能会非常缓慢，因为mysql查询将不断等待锁被释放。并且，在死锁情况下，mysql会尝试查找是否有任何死锁，并且即使在找到死锁后，它也会等待一段时间才能踢出线程以从死锁情况中退出。

当我面对这种情况时，我在自己的代码中实现了锁定，因为mysql的锁定机制由于一个bug而失败。所以我在我的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
    }
}

- user517491

---

很不幸，遇到这种情况的大多数用户都会涉及到多台机器或进程将数据导入单个MySQL实例中。对于大多数用户而言，在应用程序中进行行级锁定根本不是一种选择。 - dgtized