mysql - 解决 MySQL 错误 "Deadlock found when trying to get lock; try restarting transaction"

Question

我有一个 MySQL 表，其中包含大约 5,000,000 行，这些行通过 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，那么即使在完全正常的情况下，任何 写入事务都可能导致死锁。较大的表、较大的写入和较长的事务 block 通常会增加发生死锁的可能性。在你的情况下，它可能是这些的组合。

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

我强烈建议您阅读 this page在 MySQL 手册中。它列出了一系列有助于应对死锁和减少死锁频率的事情。