how to avoid deadlock in mysql

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 w开发者_如何转开发ay 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.

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.

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);