解决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手册中的此页面。它提供了一系列可帮助处理死锁并减少死锁频率的事情。