MySQL (InnoDB) 中发现死锁
在数据库中运行某个查询时（所有表都使用 InnoDB 存储引擎），我经常收到以下错误：“尝试获取锁定时发现死锁；尝试重新启动事务”
查询是 DELETE FROM session WHERE userid != 0 AND 最后活动 < 1289594761 AND admin = 1 AND userid NOT IN (SELECT userid FROM users WHERE (userflags & 1048576))
当我将 NOT IN 部分添加到 WHERE 语句中时，错误开始发生。 为什么这会引起问题？我可以采取什么措施来防止这种情况发生？
I'm getting the following error pretty often when running a certain query in my database (all tables use the InnoDB storage engine): "Deadlock found when trying to get lock; try restarting transaction"
The query is DELETE FROM sessions WHERE userid != 0 AND lastactivity < 1289594761 AND admin = 1 AND userid NOT IN (SELECT userid FROM users WHERE (userflags & 1048576))
The errors started to occur when I've added the NOT IN part to my WHERE statement.
Why is this causing issues and what can I do to prevent this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一个简单的解决方案是将其分成两个连续的查询。即：
SELECT userid into #tmptable FROM users WHERE (userflags & 1048576);
从会话中删除，其中 userid != 0 AND lastactivity < 1289594761 AND admin = 1 AND userid NOT IN（从 #tmptable 中选择用户 ID）；
这样，您就可以使用第二个表中值的本地会话副本，并且不会导致对其进行读锁定。然而，这只是一个快速而肮脏的解决方案。更好的解决方案是分析涉及这两个表的所有活动的事务锁定设置，并重写查询（如果您将定期重新使用它）。
An easy solution would be to separate this into two consecutive queries. I.e.,:
SELECT userid into #tmptable FROM users WHERE (userflags & 1048576);
DELETE FROM sessions WHERE userid != 0 AND lastactivity < 1289594761 AND admin = 1 AND userid NOT IN (select userid from #tmptable);
That way you're working with a local session copy of the values from the second table and not causing a read lock on it. However, this is just a quick-and-dirty solution. A better solution would be to analyze the transaction lock settings from all activities that touch these two tables and to rewrite the query, if you'll be re-using it regularly.
想必您会更频繁地收到错误，因为现在查询速度要慢得多。
userflags上的&操作使子查询不可索引。标志词通常不是好的模式设计，因为它们需要破坏索引的计算。如果您经常进行位测试查询，则单独的小数据类型列（例如TINYINT）可能会更好。如果您的模式按照它看起来的方式工作，您应该能够使用简单的 JOIN 来完成此操作，它通常比子查询执行得更好：（
DELETE上的联接是非 ANSI SQL 扩展在 MySQL 中。）Presumably you get the error more often because this is now a much slower query.
The
&op onuserflagsmakes the subquery unindexable. Flag words aren't usually good schema design, as they require computation that defeats indexing. If you are doing bit testing queries a lot, separate columns of small datatypes (egTINYINT) may be better.If your schema works the way it looks like it might, you should be able to do this using a simple JOIN, which usually performs better than a subqueries:
(joins on
DELETEis a non-ANSI SQL extension in MySQL.)