First of all, I don't see how I could be getting any deadlock at all, since I am using no explicit locking, there's only one table involved, there's a separate process each to insert, select, and update rows, only one row is inserted or updated at a time, and each process only rarely (perhaps once a minute) runs at all.
It's an email queue:
CREATE TABLE `emails_queue` (
`id` varchar(40) NOT NULL,
`email_address` varchar(128) DEFAULT NULL,
`body` text,
`status_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`status` enum('pending','inprocess','sent','discarded','failed') DEFAULT NULL,
KEY `status` (`status`),
KEY `status_time` (`status`,`status_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The generating process, in response to some user action but roughly every 90 seconds, does an insert to the table, setting the status to "pending".
There's a monitoring process that every minute checks that the number of "pending" and "failed" emails is not excessive. It takes less than a second to run and has never given me any trouble.
Every minute, the sending process grabs all the pending emails. It loops through and one email at a time, sets its status to "inprocess", tries to send it, and finally sets its status accordingly to "sent", "discarded" (it has reasons for deciding an email shouldn't go out), or "failed" (rejected by the SMTP system).
The statement for setting the status is unusual.
UPDATE emails_queue SET status=?, status_time=NOW() WHERE id=? AND status = ?
That is, I only update the status if the current status it already what I believe it to be. Before this mechanism, I accidentally kicked off two sending processes and they would each try to send the same email. Now, if that were to happen, one process would successfully move the email from "pending" to "inprocess", but the second one would update zero