解决 MySQL 错误“尝试获取锁时发现死锁；尝试重新启动事务" [英] Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction"

问题描述

我有一个包含大约 5,000,000 行的 MySQL 表，这些行通过通过 DBI 连接的并行 Perl 进程以小方式不断更新.该表有大约 10 列和多个索引.

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.

一个相当常见的操作有时会导致以下错误:

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.

触发错误的SQL语句是这样的:

The SQL statement that triggers the error is something like this:

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

该错误仅在某些时候触发.我估计只有 1% 或更少的电话.然而，这种情况从未发生在小表上，而且随着数据库的增长而变得越来越普遍.

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.

请注意，我在 file_table 中使用 a_lock 字段来确保我正在运行的四个几乎相同的进程不会尝试在同一行上工作.该限制旨在将他们的工作分成小块.

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.

我没有对 MySQL 或 DBD::mysql 进行太多调整.MySQL是标准的Solaris部署，数据库连接设置如下:

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:

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.

我有两个问题:

1. 我的情况究竟是什么导致了上述错误?

1. What exactly about my situation is causing the error above?

有没有一种简单的方法可以解决它或降低它的频率?例如，在 Db.pm 的第 276 行重新启动事务"究竟如何处理?

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"?

提前致谢.

推荐答案

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

If you are using InnoDB or any row-level transactional RDBMS, then it is possible that any write 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.

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

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/catch around 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.

我强烈建议您阅读本页MySQL 手册.它列出了一些要做的事情来帮助处理死锁并减少死锁发生的频率.

I highly recommend you read this page in the MySQL manual. It has a list of things to do to help cope with deadlocks and reduce their frequency.

这篇关于解决 MySQL 错误“尝试获取锁时发现死锁；尝试重新启动事务"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！