how to avoid deadlock in mysql

Question

I have the following query (all tables are innoDB)

INSERT INTO busy_machines(machine) 
               SELECT machine FROM all_machines 
               WHERE machine NOT IN (SELECT machine FROM busy_machines) 
               and machine_name!='Main' 
               LIMIT 1

Which causes a deadlock when I run it in threads, obviously because of the inner select, right?

The error I get is:

(1213, 'Deadlock found when trying to get lock; try restarting transaction')

How can I avoid the deadlock? Is there a way to change to query to make it work, or do I need to do something else?

The error doesn't happen always, of course, only after running this query lots of times and in several threads.

Solution 1

You will probably get better performance if you replace your "NOT IN" with an outer join.

You can also separate this into two queries to avoid inserting and selecting the same table in a single query.

Something like this:

           SELECT a.machine 
           into @machine
           FROM all_machines a
           LEFT OUTER JOIN busy_machines b on b.machine = a.machine
           WHERE a.machine_name!='Main' 
           and b.machine IS NULL 
           LIMIT 1;

           INSERT INTO busy_machines(machine) 
           VALUES (@machine);

Solution 2

To my understanding, a select does not acquire lock and should not be the cause of the deadlock.

Each time you insert/update/or delete a row, a lock is acquired. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Generally speaking, to avoid deadlock you must acquire lock always in the same order even in different transaction (e.g. always table A first, then table B).

But if within one transaction you insert in only one table this condition is met, and this should then usually not lead to a deadlock. Are you doing something else in the transaction?

A deadlock can however happen if there are missing indexes. When a row in inserted/update/delete, the database need 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.

Not sure what happens exactly in your case, but maybe it helps.

Comments

[Marcus Adams]

This is a great solution if are using MySQL 5.0 or greater, otherwise you won't have user variables available. This should avoid your deadlock issues too.

[olamundo]

I don't understand how is your query similar to mine - the outer join will return also machines that are in busy machines, and not only those that are not

[Ike Walker]

@noam, using the outer join combined with "and b.machine IS NULL" in the where clause excludes the machines that are in busy_machines. It will return the same data as the NOT IN, but do so much more efficiently.

[olamundo]

walker - will this prevent the deadlock even if I don't use user variables, i.e do your solution in one query without the variable assignment?

[Ike Walker]

@noam: Possibly. You should try it that way and see if it resolves your issue. It would be useful to see the "LATEST DETECTED DEADLOCK" info from "SHOW ENGINE INNODB STATUS"

[Mark Amery]

-1; MySQL won't even let you create a foreign key between two columns unless both are indexed, so missing indexes on foreign keys can't possibly explain anything.