如何避免mysql'试图获取锁时发现死锁；尝试重新开始交易" [英] How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'
问题描述
我有一个记录在线用户的innoDB表.用户每次刷新页面时都会更新该信息，以跟踪他们所访问的页面以及它们对该站点的最后访问日期.然后，我会有一个cron，每15分钟运行一次，以删除旧记录.
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.
我在尝试获取锁时发现了死锁；昨晚尝试重新启动事务"大约5分钟，这似乎是在向该表中运行INSERT时发生的.有人可以建议如何避免此错误吗?
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?
===编辑===
以下是正在运行的查询:
Here are the queries that are running:
首次访问该网站:
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
然后它需要进行一些计数才能记录一些统计信息(即:在线成员，在线访客).
It then does some counts to log some stats (ie: members online, visitors online).
推荐答案
一个可以解决大多数死锁的简单技巧是按特定顺序对操作进行排序.
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:
- 连接1:锁定键(1)，锁定键(2)；
- 连接2:锁定键(2)，锁定键(1)；
如果两个都同时运行，则连接1将锁定键(1)，连接2将锁定键(2)，每个连接将等待另一个释放键->死锁.
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.
现在，如果您更改查询以使连接以相同的顺序锁定键，即:
Now, if you changed your queries such that the connections would lock the keys at the same order, ie:
- 连接1:锁定键(1)，锁定键(2)；
- 连接2:锁定键( 1 )，锁定键( 2 );
- connection 1: locks key(1), locks key(2);
- connection 2: locks key(1), locks key(2);
不可能陷入僵局.
这就是我的建议:
-
确保除了delete语句外，没有其他查询一次锁定访问多个键.如果您愿意(并且我怀疑您愿意)，请按升序在(k1，k2，.. kn)中订购他们的WHERE.
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.
修复您的delete语句以升序工作:
Fix your delete statement to work in ascending order:
更改
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个尝试重试此错误).
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'试图获取锁时发现死锁；尝试重新开始交易"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
