MySQL 在 2 次更新时死锁

Question

我们遇到了一些死锁，这是我们最新的SHOW ENGINE INNODB STATUS

查询 1:

UPDATE actions SET
    hv_clientid='56c46db2dd',
    hv_clientid_time=NOW(),
    hv_lastcheck=NOW() 
WHERE 
    hv_callid!=''
    AND is_done='N'
    AND (hv_lastcheck
        BETWEEN DATE_SUB(NOW(),INTERVAL 8 HOUR)
        AND DATE_SUB(NOW(),INTERVAL 120 SECOND)
    OR hv_lastcheck='0000-00-00 00:00:00')
    AND (
        hv_clientid IS NULL
        OR (
            hv_clientid_time IS NOT NULL
            && hv_clientid_time<DATE_SUB(NOW(),INTERVAL 2 MINUTE)
        )
    )
LIMIT 60

查询 2:

UPDATE actions SET
    hv_clientid=NULL,
    result='answer',
    is_done='Y',
    hv_callid=''
WHERE action_id='145291132'

我们有多个脚本实例来检查要更新的状态。 查询 1 应抓取行，然后根据设置的 hv_clientid 选择这些行。一旦该选择迭代数据，它就会运行查询2。

为什么会发生这种情况？

Answer 1

来自MySQL Documentation - 14.2.7.9 How to Cope with Deadlocks (已添加突出显示):

When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock. For example, organize database operations into functions within your application, or call stored routines, rather than coding multiple similar sequences of INSERT, UPDATE, and DELETE statements in different places.

如果这是原因，向查询 1 添加 ORDER BY 子句应该会有所帮助。