如何避免MySQL出现“Deadlock found when trying to get lock; try restarting transaction”错误

Question

我有一个记录在线用户的InnoDB表格。每次用户刷新页面时，它都会更新以跟踪他们所在的页面和最后访问站点的日期。然后，我有一个cron定时器，每15分钟运行一次，用于删除旧记录。

昨晚在运行插入此表时，出现了“Deadlock found when trying to get lock; try restarting transaction”错误，持续了约5分钟。有人能提供避免此错误的建议吗？

=== 编辑 ===

下面是正在运行的查询：

首次访问网站：

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

然后它执行一些统计以记录一些统计数据（例如：在线成员，在线访客）。

- David

---

你能提供一些关于表结构的更多细节吗？是否有任何聚集或非聚集索引？ - Anders Abel

执行 "show engine innodb status" 命令可以提供有用的死锁诊断信息。 - Martin

插入一行数据如何导致死锁？我原本以为在一个事务中，你需要尝试获取至少两个锁才会发生死锁。插入一行只占用一个锁。我认为你的第一个查询缺少了一些插入或选择操作。 - joseph

Answer 1

一个简单的技巧可以帮助解决大多数死锁问题，那就是按照特定顺序排序操作。

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

• 连接1：锁定key(1)，锁定key(2)；
• 连接2：锁定key(2)，锁定key(1)；

如果两个连接同时运行，连接1将锁定key(1)，连接2将锁定key(2)，每个连接都将等待另一个释放该键 -> 死锁。

现在，如果你更改了查询，使得连接以相同的顺序锁定键，例如：

• 连接1：锁定key(1)，锁定key(2)；
• 连接2：锁定key(1)，锁定key(2)；

这样就不可能发生死锁。

因此，我建议你做以下几点：

1. 确保没有其他查询会一次锁定多个键的访问，除了删除语句。如果有（我怀疑你有），请按升序对它们的WHERE子句进行排序，例如按(k1,k2,..kn)的顺序。

2. 修复你的删除语句，使其按升序工作：

将

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次）。

- Omry Yadan

---

如果启用了事务，就要么全都执行，要么全部不执行。如果出现任何异常，保证整个事务没有产生任何影响。在这种情况下，您会希望重新启动整个事务。 - Omry Yadan

在一个巨大的表上，基于选择条件进行删除的速度比简单删除要慢得多。 - Thermech

非常感谢你，伙计。 "Sort statements" 提示解决了我的死锁问题。 - Miere

据我所知，在MySQL中，你不能在子查询中从正在进行UPDATE操作的同一张表中进行选择（即不支持自查询）。参考链接：dev.mysql.com/doc/refman/5.7/en/update.html - artaxerxe

删除查询中的项目排序如何解决死锁问题？ - a.valchev

Answer 2

当两个事务互相等待对方释放锁时，就会发生死锁。例如：

• 事务1：先锁定A，再锁定B
• 事务2：先锁定B，再锁定A

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

数据库中发生死锁的另一个原因可能是缺少索引。当插入/更新/删除一行数据时，数据库需要检查关系约束，即确保关系一致。为此，数据库需要检查相关表中的外键。这可能导致获取其他锁而不是修改的行的锁。请务必在外键上（当然还包括主键）建立索引，否则可能会导致表锁而不是行锁。如果发生表锁，锁竞争就更高，死锁的可能性也会增加。

- ewernli

---

可能我的问题是用户刷新了页面，因此触发了记录的更新，同时cron正在尝试删除该记录。然而，我在插入操作时遇到了错误，所以cron不会删除刚刚创建的记录。那么，一个还没有被插入的记录怎么会发生死锁呢？ - David

你能提供关于表以及交易的更多信息吗？具体做了什么？ - ewernli

如果每个事务只有一个语句，没有对其他表的操作，也没有特殊的外键或唯一约束条件，也没有级联删除约束条件，我不认为会发生死锁。 - ewernli

没有，没有什么特别的……我想这取决于表的使用性质。每次页面刷新时，访问者都会插入/更新一行。每次可能有1000多个访问者在线。 - David

Answer 3

如果有人仍然在解决这个问题：

我遇到了类似的问题，即两个请求同时发送到服务器。没有以下情况：

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

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

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

后来我发现，在两个表之间存在父子关系，因为有外键。当我在子表中插入记录时，事务会在父表的行上获取锁。紧接着我又试图更新父行，导致将锁升级为独占锁。由于第二个并发事务已经持有共享锁，因此它导致了死锁。

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

- chatsap

---

在我的情况下，看起来问题是一个外键关系。谢谢！ - Chris Prince

对我来说，情况是一样的：更新了一个表，并更新了另一个表中的外键。我使用“无操作”删除了我的约束键。然后，奇迹出现了！当然，它不再检查约束，但在大型更新表上无法使用。非常感谢！ - Patrice G

Answer 4

很可能删除语句会影响表中大部分行。最终这可能会导致在删除时获得表锁定。保持锁定（在这种情况下为行锁或页锁）并获得更多锁定总是死锁的风险。但我无法解释为什么插入语句会导致锁升级-可能与页面拆分/添加有关，但需要了解MySQL的人来解释。

首先，可以尝试为删除语句立即明确获取表锁。请参见LOCK TABLES 和 Table locking issues。

- Anders Abel

Answer 5

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

将其分开做虽然效率较低，但可以避免在删除期间持有键范围锁。此外，修改您的select查询以添加一个where子句，排除900秒以前的行。这避免了对cron作业的依赖，并允许您将其重新安排为更少的运行次数。

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

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

- Brian Sandlin

Answer 6

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

有关详细信息，请参见@RetryTransaction Javadoc。

- Archie

Answer 7

@Omry Yadan的回答（https://dev59.com/73E95IYBdhLWcg3wY85l#2423921）可以通过使用ORDER BY进行简化。

修改为：

可以通过使用ORDER BY对其进行简化。

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

to

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

为了保持你删除的项目的顺序一致，如果在单个事务中进行多次插入，请确保它们也始终按id排序。根据mysql的删除文档，如果指定了ORDER BY子句，则按指定的顺序删除行。你可以在这里找到参考资料: https://dev.mysql.com/doc/refman/8.0/en/delete.html

- joseph

Answer 8

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

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

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

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

只是分享我的经验，我不主张任何事情。

- BitsAndBytes

---

具有相同的行为 - littleAlien

Answer 9

cron是危险的。如果cron的一个实例在下一个实例到来之前无法完成，则它们很可能会相互冲突。

最好运行一个持续运行的任务，该任务将删除一些行，然后休眠一段时间，再重复此过程。

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

但是，如果datetime测试包含超过表格的20％，则DELETE将进行表扫描。更小的块更频繁地删除是一种解决方法。

选择较小的数据块还可以锁定较少的行。

底线：

• INDEX(datetime)
• 持续运行的任务-删除，休息一分钟，重复。
• 为确保上述任务未死机，可以设置一个cron job，其唯一目的是在失败时重新启动它。

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

- Rick James