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.

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