Mysql 如何死锁,如何避免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 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.

解决方案

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