我有一个在后台发送电子邮件的脚本。该脚本并行运行,可以同时发送多封电子邮件。它基本上是这样工作的,使用MySQL和PHP的混合物:

/* TransmissionId is a PRIMARY KEY */
/* StatusId is a FOREIGN KEY */
/* Token is UNIQUE */

/* Pick a queued (StatusId=1) transmission and set it to pending (StatusId=2) */
/* This is a trick to both update a row and store its id for later retrieval in one query */
SET @Ids = 0;
UPDATE transmission
SET StatusId=IF(@Ids := TransmissionId,2,2), LatestStatusChangeDate=NOW()
WHERE StatusId = 1
ORDER BY TransmissionId ASC
LIMIT 1;

/* Fetch the id of the picked transmission */
$Id = SELECT @Ids;

try {
    /* Fetch the email and try to send it */
    $Email = FetchEmail($Id);
    $Email->Send();

    /* Set the status to sent (StatusId=3) */
    $StatusId = 3;
} catch(Exception $E) {
    /* The email could not be sent, set the status to failed (StatusId=4) */
    $StatusId = 4;
} finally {
    /* Save the new transmission status */
    UPDATE transmission
    SET StatusId=$StatusId, LatestStatusChangeDate=NOW(), Token='foobar'
    WHERE TransmissionId = $Id;
}

问题是我有时会陷入僵局: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction . 这是在执行最后一个查询时发生的。我没有看到在执行第一个查询时发生这种情况。有人能理解在这种情况下僵局是如何发生的吗?可能是第一个查询和最后一个查询锁定吗 StatusId 和 TransmissionId 按相反的顺序?但我认为第一个查询不需要锁定 传输ID ,我也不认为最后一个查询需要锁定 . 我怎样才能发现这一点,我怎样才能修复它?

编辑

还有一个查询也可能发挥作用。每当有人打开电子邮件时,都会运行此查询:

/* Selector is UNIQUE */
UPDATE transmission SET
OpenCount=OpenCount+1
WHERE Selector = 'barfoo'