如何避免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

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

当前回答

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

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

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

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

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

底线:

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

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

2019-12-05 21:27:26

其他回答

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

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

关于死锁有许多问题和答案。每次插入/更新/或删除一行时，都会获得一个锁。为了避免死锁，必须确保并发事务不会按照可能导致死锁的顺序更新行。一般来说，即使在不同的事务中，也尽量以相同的顺序获取锁(例如，总是先获取表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 might result 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 lock instead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.

2010-02-25 09:21:43

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

我也遇到过类似的问题，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

2019-02-26 13:41:54

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

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

2010-03-06 19:42:29

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

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

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

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

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

2018-08-04 13:29:44

@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

2021-08-10 16:28:12

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

推荐文章

最新文章

标签