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.

Answer 2

well I would use table locks read more here

Locking is safe and applies to one client session. A table lock protects only against inappropriate reads or writes by other sessions.

Answer 3

You should use subquery as follows...

<?php

$queryx = "DELETE FROM `queue` WHERE `email` IN (SELECT email FROM `queue` LIMIT 1)";
$resultx = mysql_query($queryx) or die(mysql_error());

?>

*Note: Always select only the fields you want... try to avoid select *... this will slow down the performance

Answer 4

run an update query that will change the key before you do your select. Do the select by this new key, whicj is known only in the same session.
If the table is innoDB the record is locked, and when it will be released, the other selects won't find the record.