SELECT then immediately DELETE mysql record

Question

I have a PHP script that runs a SELECT query then immediately deletes the record. There are multiple machines that are pinging the same php file and fetching data from the same table. Each remote machine is running on a cron job.

My problem is that sometimes it is unable to delete fast enough since some of the machines ping at the exact same time.

My question is, how can I SELECT a record from a database and have it deleted before the next machine grabs it. For right now I just added a short delay but it's not working very well. I tried using a transaction, but I don't think it applies here.

Here is an example snippet of my script:

<?php

$query = "SELECT * FROM `queue` LIMIT 1";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
    $email = $row['email'];
    $campaign_id = $row['campaign'];
}

$queryx = "DELETE FROM `queue` WHERE `email` = '".$email."'";
$resultx = mysql_query($queryx) or die(mysql_error());

?>

Really appreciate the help.

Answer 1

If you're using MariaDB 10:

DELETE FROM `queue` LIMIT 1 RETURNING *

Documentation.