Deadlock in script for sending emails

Question

I have a script for sending emails in the background. The script runs in parallel to send out multiple emails simultaneously. It works basically like this, with a mixture of MySQL and 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;
}

The issue is that I sometimes get a deadlock: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction. This has happened when executing the last query. I've not seen it happen when executing the first query. Can anyone understand how a deadlock can happen in this case? Could it be that the first query and the last query lock StatusId and TransmissionId in opposite order? But I don't think the first query needs to lock TransmissionId, nor do I think the last query needs to lock StatusId. How can I find this out, and how can I fix it?

Edit

There is another query that might also play a role. Whenever someone opens the email, this query is run:

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

Answer 1

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted. dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.ht‌​ml