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.

how doesn't it apply? that sounds *exactly* like what a transaction is good for. — mpen, Feb 04 '12 at 01:53
@Mark - Do transactions also prevent `SELECT`s from operating? I'm wondering if this might not be the problem. @john - If these are run by `cron`, what is the point of `die(mysql_error())`? Why not log the error to a file or something? — Jared Farrish, Feb 04 '12 at 01:55
Heh, running these things out of `cron` (which can only execute programs on exact minutes) practically guarantees exactly this kind of resource contention. While Mark's advice of using transactions sounds like a good approach to solving this problem, I have to think that some other mechanism entirely would be beneficial. — sarnold, Feb 04 '12 at 01:58
@JaredFarrish: Not sure, but I think you can fetch the result of a select if it's the last statement? http://stackoverflow.com/questions/7435770/mysql-transaction-mixing-insert-select-to-attain-last-insert-id — mpen, Feb 04 '12 at 02:01
@ItayMoav: A transaction would make the select+delete atomic, no? No other queries can be executed inbetween. — mpen, Feb 04 '12 at 02:01
Off the top of my head, you could create a [`uniqid()`](http://php.net/manual/en/function.uniqid.php), run an `UPDATE` first and set the ID to a `Requested` column, `SELECT` for that matching ID, then `DELETE`. I would think that would work. — Jared Farrish, Feb 04 '12 at 02:02
@Mark - No I do not believe a transaction prevents selects from outside the transaction in innoDB, but then again, I might be wrong. — Itay Moav -Malimovka, Feb 04 '12 at 02:05
@ItayMoav: Maybe I'm mistaken then. I guess you have to use locks in that case. http://stackoverflow.com/a/4227957/65387 — mpen, Feb 04 '12 at 02:11

score 8 · Answer 1 · answered Jun 28 '15 at 07:47

8

If you're using MariaDB 10:

DELETE FROM `queue` LIMIT 1 RETURNING *

Documentation.

answered Jun 28 '15 at 07:47

anthonyryan1

4,867
2
34
27

score 6 · Answer 2 · answered Feb 04 '12 at 02:03

6

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.

answered Feb 04 '12 at 02:03

Jaspreet Chahal

2,759
1
15
17

score 2 · Answer 3 · answered Feb 04 '12 at 02:16

2

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

answered Feb 04 '12 at 02:16

Whatever Kitchen

700
2
14
24

Interesting. If the point is to clear all queued rows by "random" email, this could work. – Jared Farrish Feb 04 '12 at 02:28
Also, the `SELECT *` is great advice. `SELECT email` should be sufficient. – Jared Farrish Feb 04 '12 at 02:33
You cannot use LIMIT inside a subquery. Otherwise this was exactly what I was trying to do. – john Feb 04 '12 at 04:12

Itay Moav -Malimovka · Answer 4 · 2012-02-04T05:11:39.397

1

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.

edited Feb 04 '12 at 05:11

answered Feb 04 '12 at 02:04

Itay Moav -Malimovka

52,579
61
190
278

1

[SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads](http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html). – Jared Farrish Feb 04 '12 at 02:06
Only question I have with my comment above, can you `DELETE` from the `SELECT ... FOR UPDATE` mode? Or do you first have to unlock (and hence back to where we started)? – Jared Farrish Feb 04 '12 at 02:08

Kyle R · Accepted Answer · 2012-02-04T02:15:04.567

-4

Put your delete queries inside the while loop, just incase you ever want to increase the limit from your select.

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

while($row = mysql_fetch_array($query)){
    mysql_query("DELETE FROM `queue` WHERE `email` = '" . $row['email'] . "' LIMIT 1") or die(mysql_error());
}
?>

The above code would be just the same as running:

mysql_query("DELETE FROM `queue` LIMIT 1") or die(mysql_error());

Be careful using your delete query, if the email field is blank, it will delete all rows that have a blank email. Add LIMIT 1 to your delete query to avoid multiple rows being deleted.

To add a random delay, you could add a sleep to the top of the script,

eg:

<?php
$seconds = mt_rand(1,10);
sleep($seconds);
?>

edited Feb 04 '12 at 02:15

answered Feb 04 '12 at 02:00

Kyle R

568
2
13

1

I don't know that this is going to get rid of the race condition/contention problem, it just "shortens" amount of "possible" activity. It could work in some cases, but there still might be anomalies. Note, as well, since there's a `LIMIT 1`, the `while()` is superfluous. So I don't know this really would do anything productive in the end... – Jared Farrish Feb 04 '12 at 02:12
If he ever decides to change the SELECT to more than 1 result, the delete query will only run on the first result. So in the end it is more productive and no different 'resource wise' to having the DELETE outside the loop. – Kyle R Feb 04 '12 at 02:12
"If"? There's lots of if's. If the OP is popping a queue, I imagine it's only meant to be one. The point is still the same (and amplified if there are many results returned for the later processed results). – Jared Farrish Feb 04 '12 at 02:14
I think the bigger question is, if he is only using `LIMIT 1`, what is the point of him using a while loop? And thats why my second code snippet only shows a DELETE query, as it is the exact same. – Kyle R Feb 04 '12 at 02:17
"I think the bigger question is, if he is only using LIMIT 1, what is the point of him using a while loop?" Are you messing with me? First comment: *Note, as well, since there's a LIMIT 1, the while() is superfluous.* An unnecessary `while()` is unnecessary IMO, but that's just my opinion. – Jared Farrish Feb 04 '12 at 02:21
Once I tried my hand at a solution, I noticed the `SELECT`s might not match (see my [comment](http://stackoverflow.com/a/9137862/451969) under this question). Looking at the whole code, it looks like the point is to actually remove any queued items for an (apparently random) email. The OP may have been confused by this, hence the `while()` (to loop over each queued row *by email*). It's confused, so it's hard to tell the true intent. – Jared Farrish Feb 04 '12 at 02:32

SELECT then immediately DELETE mysql record

5 Answers5

Linked