如何避免mysql中的死锁 [英] how to avoid deadlock in mysql

问题描述

我有以下查询(所有表均为innoDB)

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?

我得到的错误是:

(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.

推荐答案

如果将"NOT IN"替换为外部联接，则可能会获得更好的性能.

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.

类似这样的东西:

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

这篇关于如何避免mysql中的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！