解决 MySQL 错误“尝试获取锁定时发现死锁”尝试重新启动事务”

Question

我有一个包含大约 5,000,000 行的 MySQL 表，这些表通过通过 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 行重新启动事务”？

提前致谢。

Answer 1

如果您使用 InnoDB 或任何行级事务 RDBMS，那么任何写入事务都可能导致死锁，即使在完全正常的情况下也是如此。较大的表、较大的写入和较长的事务块通常会增加发生死锁的可能性。在你的情况下，它可能是这些的组合。

真正处理死锁的唯一方法是编写代码以应对死锁。如果您的数据库代码编写得好，这通常不是很困难。通常，您可以在查询执行逻辑周围放置一个try/catch，并在发生错误时查找死锁。如果你发现了一个，正常的做法就是尝试再次执行失败的查询。

我强烈建议您阅读 MySQL 手册中的此页面 。它列出了一系列有助于应对僵局并减少僵局发生频率的事项。

Answer 2

答案是正确的，但是关于如何处理死锁的 perl 文档有点稀疏，并且可能与 PrintError、RaiseError 和 HandleError 选项混淆。看起来，与其使用 HandleError，不如使用 Print 和 Raise，然后使用 Try:Tiny 之类的东西来包装代码并检查错误。下面的代码给出了一个示例，其中数据库代码位于 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 应该在至少包括以下内容：

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 。

希望这对某人有帮助 -

Answer 3

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

将以下行添加到 my.cnf 文件

innodb_locks_unsafe_for_binlog = 1

#

1 - ON
0 - 关闭

#

Answer 4

在死锁异常的情况下重试查询的想法很好，但它可能会非常慢，因为 mysql 查询将一直等待锁被释放。如果出现死锁，mysql会尝试查找是否存在死锁，即使发现存在死锁，它也会等待一段时间，然后再踢出线程，以便摆脱死锁情况。

当我遇到这种情况时，我所做的就是在自己的代码中实现锁定，因为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
    }
}