MySQL 如何避免mysql'尝试获取锁时发现死锁；尝试重新启动事务'

How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'

提问by David

I have a innoDB table which records online users. It gets updated on every page refresh by a user to keep track of which pages they are on and their last access date to the site. I then have a cron that runs every 15 minutes to DELETE old records.

我有一个记录在线用户的 innoDB 表。它会在用户每次刷新页面时更新，以跟踪他们所在的页面以及他们上次访问该站点的日期。然后我有一个 cron，它每 15 分钟运行一次以删除旧记录。

I got a 'Deadlock found when trying to get lock; try restarting transaction' for about 5 minutes last night and it appears to be when running INSERTs into this table. Can someone suggest how to avoid this error?

我在尝试获取锁时发现了“死锁”；昨晚尝试重新启动事务大约 5 分钟，似乎是在将 INSERT 运行到此表中时。有人可以建议如何避免此错误吗？

=== EDIT ===

=== 编辑 ===

Here are the queries that are running:

以下是正在运行的查询：

First Visit to site:

首次访问网站：

INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3

On each page refresh:

在每个页面刷新时：

UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888

Cron every 15 minutes:

每 15 分钟执行一次 Cron：

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

It then does some counts to log some stats (ie: members online, visitors online).

然后它会记录一些统计数据（即：在线成员、在线访问者）。

回答by Omry Yadan

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

可以帮助解决大多数死锁的一个简单技巧是按特定顺序对操作进行排序。

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

当两个事务试图以相反的顺序锁定两个锁时，您会遇到死锁，即：

• connection 1: locks key(1), locks key(2);
• connection 2: locks key(2), locks key(1);

• 连接1：锁钥匙（1），锁钥匙（2）；
• 连接2：锁钥匙（2），锁钥匙（1）；

If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

如果两者同时运行，连接 1 将锁定 key(1)，连接 2 将锁定 key(2)，并且每个连接将等待另一个释放密钥 -> 死锁。

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

现在，如果您更改了查询，以便连接以相同的顺序锁定键，即：

• connection 1: locks key(1), locks key(2);
• connection 2: locks key(1), locks key(2);

• 连接1：锁钥匙（1），锁钥匙（2）；
• 连接2：锁钥匙（1），锁钥匙（2）；

it will be impossible to get a deadlock.

不可能陷入僵局。

So this is what I suggest:

所以这就是我的建议：

1. Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.

2. Fix your delete statement to work in ascending order:

1. 确保除了 delete 语句之外，没有其他查询一次锁定多个键的访问。如果您这样做（我怀疑您这样做），请按升序对 (k1,k2,..kn) 中的 WHERE 进行排序。

2. 修复您的删除语句以按升序工作：

Change

改变

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

To

到

DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;

Another thing to keep in mind is that mysql documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

要记住的另一件事是 mysql 文档建议，如果出现死锁，客户端应自动重试。您可以将此逻辑添加到您的客户端代码中。（例如，在放弃之前对这个特定错误重试 3 次）。

回答by ewernli

Deadlock happen when two transactions wait on each other to acquire a lock. Example:

当两个事务相互等待获取锁时，就会发生死锁。例子：

• Tx 1: lock A, then B
• Tx 2: lock B, then A

• Tx 1：锁定 A，然后锁定 B
• Tx 2：锁定 B，然后锁定 A

There are numerous questions and answers about deadlocks. Each time you insert/update/or delete a row, a lock is acquired. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Generally speaking, try to acquire lock always in the same ordereven in different transaction (e.g. always table A first, then table B).

关于死锁有很多问题和答案。每次插入/更新/或删除一行时，都会获得一个锁。为了避免死锁，您必须确保并发事务不会以可能导致死锁的顺序更新行。一般来说，即使在不同的事务中，也总是尝试以相同的顺序获取锁（例如，总是先表 A，然后是表 B）。

Another reason for deadlock in database can be missing indexes. When a row is inserted/update/delete, the database needs to check the relational constraints, that is, make sure the relations are consistent. To do so, the database needs to check the foreign keys in the related tables. It mightresult in other lock being acquired than the row that is modified. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lockinstead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.

数据库死锁的另一个原因可能是缺少索引。当插入/更新/删除一行时，数据库需要检查关系约束，即确保关系一致。为此，数据库需要检查相关表中的外键。它可能会导致获取其他锁而不是被修改的行。然后确保始终在外键（当然还有主键）上有索引，否则可能会导致table lock而不是row lock。如果发生表锁，锁争用会更高，死锁的可能性也会增加。

回答by Anders Abel

It is likely that the delete statement will affect a large fraction of the total rows in the table. Eventually this might lead to a table lock being acquired when deleting. Holding on to a lock (in this case row- or page locks) and acquiring more locks is always a deadlock risk. However I can't explain why the insert statement leads to a lock escalation - it might have to do with page splitting/adding, but someone knowing MySQL better will have to fill in there.

delete 语句很可能会影响表中总行数的很大一部分。最终这可能会导致在删除时获取表锁。持有锁（在这种情况下是行锁或页锁）并获取更多锁始终存在死锁风险。但是，我无法解释为什么插入语句会导致锁升级 - 它可能与页面拆分/添加有关，但是更了解 MySQL 的人必须在那里填写。

For a start it can be worth trying to explicitly acquire a table lock right away for the delete statement. See LOCK TABLESand Table locking issues.

首先，尝试立即为删除语句显式获取表锁是值得的。请参阅锁定表和表锁定问题。

回答by Brian Sandlin

You might try having that deletejob operate by first inserting the key of each row to be deleted into a temp table like this pseudocode

您可以尝试delete通过首先将要删除的每一行的键插入到临时表中来运行该作业，如此伪代码

create temporary table deletetemp (userid int);

insert into deletetemp (userid)
  select userid from onlineusers where datetime <= now - interval 900 second;

delete from onlineusers where userid in (select userid from deletetemp);

Breaking it up like this is less efficient but it avoids the need to hold a key-range lock during the delete.

像这样分解它的效率较低，但它避免了在delete.

Also, modify your selectqueries to add a whereclause excluding rows older than 900 seconds. This avoids the dependency on the cron job and allows you to reschedule it to run less often.

此外，修改您的select查询以添加一个where排除超过 900 秒的行的子句。这避免了对 cron 作业的依赖，并允许您重新安排它的运行频率。

Theory about the deadlocks: I don't have a lot of background in MySQL but here goes... The deleteis going to hold a key-range lock for datetime, to prevent rows matching its whereclause from being added in the middle of the transaction, and as it finds rows to delete it will attempt to acquire a lock on each page it is modifying. The insertis going to acquire a lock on the page it is inserting into, and thenattempt to acquire the key lock. Normally the insertwill wait patiently for that key lock to open up but this will deadlock if the deletetries to lock the same page the insertis using because thedeleteneeds that page lock and the insertneeds that key lock. This doesn't seem right for inserts though, the deleteand insertare using datetime ranges that don't overlap so maybe something else is going on.

关于死锁的理论：我在 MySQL 中没有很多背景知识，但这里是...delete将持有日期时间的键范围锁，以防止where在事务中间添加与其子句匹配的行，并且当它找到要删除的行时，它会尝试在它正在修改的每个页面上获取锁。将insert在它插入的页面上获取锁，然后尝试获取键锁。通常，它insert会耐心等待该键锁打开，但如果delete尝试锁定insert正在使用的同一页面，这将导致死锁，因为delete需要该页面锁和该insert键锁。但这似乎不适合插入，delete并且insert正在使用不重叠的日期时间范围，因此可能正在发生其他事情。

http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

回答by Archie

For Java programmers using Spring, I've avoided this problem using an AOP aspect that automatically retries transactions that run into transient deadlocks.

对于使用 Spring 的 Java 程序员，我使用 AOP 方面避免了这个问题，该方面会自动重试遇到暂时死锁的事务。

See @RetryTransactionJavadoc for more info.

有关更多信息，请参阅@RetryTransactionJavadoc。

回答by chatsap

In case someone is still struggling with this issue:

如果有人仍在为这个问题苦苦挣扎：

I faced similar issue where 2 requests were hitting the server at the same time. There was no situation like below:

我遇到了类似的问题，其中 2 个请求同时命中服务器。没有像下面这样的情况：

T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

So, I was puzzled why deadlock is happening.

所以，我很困惑为什么会发生死锁。

Then I found that there was parent child relation ship between 2 tables because of foreign key. When I was inserting a record in child table, the transaction was acquiring a lock on parent table's row. Immediately after that I was trying to update the parent row which was triggering elevation of lock to EXCLUSIVE one. As 2nd concurrent transaction was already holding a SHARED lock, it was causing deadlock.

然后我发现由于外键，两个表之间存在父子关系。当我在子表中插入一条记录时，事务正在获取父表行上的锁。在那之后，我立即尝试更新触发锁定提升到 EXCLUSIVE 的父行。由于第二个并发事务已经持有共享锁，因此导致死锁。

Refer to: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

参考：https: //blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

回答by Rick James

cronis dangerous. If one instance of cron fails to finish before the next is due, they are likely to fight each other.

cron是危险的。如果一个 cron 实例未能在下一个到期之前完成，它们很可能会互相争斗。

It would be better to have a continuously running job that would delete some rows, sleep some, then repeat.

最好有一个连续运行的作业，它会删除一些行，休眠一些，然后重复。

Also, INDEX(datetime)is very important for avoiding deadlocks.

此外，INDEX(datetime)对于避免死锁非常重要。

But, if the datetime test includes more than, say, 20% of the table, the DELETEwill do a table scan. Smaller chunks deleted more often is a workaround.

但是，如果日期时间测试包括超过表的 20%，DELETE则将执行表扫描。更频繁地删除较小的块是一种解决方法。

Another reason for going with smaller chunks is to lock fewer rows.

使用较小块的另一个原因是锁定较少的行。

Bottom line:

底线：

• INDEX(datetime)
• Continually running task -- delete, sleep a minute, repeat.
• To make sure that the above task has not died, have a cron job whose sole purpose is to restart it upon failure.

• INDEX(datetime)
• 持续运行的任务——删除，睡一分钟，重复。
• 为确保上述任务没有终止，有一个 cron 作业，其唯一目的是在失败时重新启动它。

Other deletion techniques: http://mysql.rjweb.org/doc.php/deletebig

其他删除技巧：http: //mysql.rjweb.org/doc.php/deletebig

回答by CINCHAPPS

I have a method, the internals of which are wrapped in a MySqlTransaction.

我有一个方法，其内部封装在 MySqlTransaction 中。

The deadlock issue showed up for me when I ran the same method in parallel with itself.

当我并行运行相同的方法时，死锁问题出现了。

There was not an issue running a single instance of the method.

运行该方法的单个实例没有问题。

When I removed MySqlTransaction, I was able to run the method in parallel with itself with no issues.

当我删除 MySqlTransaction 时，我能够并行运行该方法，没有任何问题。

Just sharing my experience, I'm not advocating anything.

只是分享我的经验，我不提倡任何东西。