如何避免MySQL '死锁时发现试图获得锁;尝试重新启动事务'

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

我在尝试锁定时发现了一个“僵局”;try restart transaction'昨晚大约5分钟，它似乎是在运行insert到这个表时。有人能建议如何避免这个错误吗?

=== edit ===

下面是正在运行的查询:

第一次实地考察:

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

在每个页面刷新:

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

每15分钟Cron一次:

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

然后，它会进行一些计数来记录一些统计数据(例如:在线成员，在线访客)。

513949 次浏览
小开

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

  • Tx 1:锁定A，然后锁定B
  • Tx 2:锁定B，然后是A

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

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

小开

delete语句可能会影响表中全部行的很大一部分。最终，这可能导致在删除时获得表锁。持有一个锁(在本例中是行锁或页锁)并获得更多锁始终存在死锁风险。然而，我不能解释为什么插入语句会导致锁升级——它可能与页面分割/添加有关，但更了解MySQL的人必须在那里填写。

首先，值得尝试立即为delete语句显式地获取一个表锁。参见锁表和表锁定问题。

小开
最佳答案

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

当两个事务试图以相反的顺序锁定两个锁时，就会出现死锁，例如:

  • 连接1:锁键(1)、锁键(2);
  • 连接2:锁键(2)、锁键(1);

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

现在，如果你改变了你的查询，这样连接就会以相同的顺序锁定键，即:

  • 连接1:锁键(1)、锁键(2);
  • 连接2:锁键(1)，锁键(2);

这样就不可能出现僵局。

这就是我的建议:

  1. 确保除了delete语句外，没有其他查询一次锁定多个键的访问权限。如果你这样做了(我怀疑你这样做了)，将它们在(k1,k2，..kn)中的WHERE按升序排列。

  2. 修正你的delete语句以升序工作:

改变

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

来

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

另一件需要记住的事情是，MySQL文档建议在出现死锁的情况下，客户端应该自动重试。您可以将此逻辑添加到客户端代码中。(比如，在放弃之前，在这个特定的错误上重试3次)。

小开

您可以尝试让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);

像这样分解它的效率较低，但它避免了在delete期间持有key-range锁的需要。

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

关于死锁的理论:我在MySQL方面没有很多的背景知识，但是这里…delete将为datetime保存一个键范围锁，以防止匹配它的where子句的行在事务中间被添加，并且当它发现要删除的行时，它将尝试在它正在修改的每一页上获得一个锁。insert将获取它正在插入的页面上的锁，而where0将尝试获取密钥锁。通常情况下，insert会耐心地等待钥匙锁打开，但如果delete试图锁定insert正在使用的同一页，这将导致死锁，因为delete需要页面锁，而insert需要钥匙锁。这似乎不适合插入，delete和insert使用的日期时间范围不重叠，所以可能发生了其他事情。

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

小开

对于使用Spring的Java程序员，我使用一个AOP方面避免了这个问题，该方面可以自动重试陷入瞬时死锁的事务。

更多信息请参见@RetryTransaction Javadoc。

小开

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

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

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

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

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

小开

如果有人还在纠结这个问题:

我也遇到过类似的问题，2个请求同时到达服务器。没有像下面这样的情况:

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


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

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

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

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

小开

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

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

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

但是，如果datetime测试包含超过20%的表，DELETE将执行表扫描。更频繁地删除较小的数据块是一种变通办法。

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

底线:

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

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

小开

@Omry Yadan的答案(https://stackoverflow.com/a/2423921/1810962)可以用ORDER by简化。

改变

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

来

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

以保持您删除项目的顺序一致。此外，如果在单个事务中执行多个插入，请确保它们也始终按id排序。

根据mysql delete文档:

如果指定了ORDER BY子句，则按指定的顺序删除行。

你可以在这里找到一个引用:https://dev.mysql.com/doc/refman/8.0/en/delete.html


提交答案