解决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屋！