Deadlock found in MySQL (InnoDB)
I'm getting the following error pretty often when running a certain query in my database (all tables use th开发者_如何学Ce 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?
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.
Presumably you get the error more often because this is now a much slower query.
The &
op on userflags
makes 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 (eg TINYINT
) 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:
DELETE sessions
FROM sessions
JOIN users ON users.userid=sessions.userid
WHERE sessions.lastactivity<1289594761 AND admin=1
AND (users.userflags&1048576)=0
(joins on DELETE
is a non-ANSI SQL extension in MySQL.)
精彩评论