Prevent SELECTing same rows in concurrent process - MySql PHP

Question

I developed a web application for running email campaigns. A cron is running to send multiple emails (upto 100 in single request) per minute.

SELECT id,email,email_text FROM recipients 
 WHERE sent_status=0 LIMIT 100

This script takes approx 70-100 seconds to send all the email using php. After sending each email, I update the sent_status=1.

Now the problem is that dues to shared hosting the script is not able to process more than 50-60 records in 60 seconds, then another request started which also select those 40 records that are still processing with first request not updated yet. Due to this some recipients receives duplicate emails.

Can this prevent by using Locking or any other solution ?

UPDATE

However my question is very similar with the linked duplicate question, except that I am actually SELECTing data from multiple tables, using GROUP BY and using ORDER BY clause on multiple columns including RAND().

My actual query something like this

SELECT s.sender_name, 
       s.sender_email, 
       r.recipient_name, 
       r.email, 
       c.campaign_id, 
       c.email_text 
FROM users s, recipients r, campaigns c 
WHERE c.sender_id=s.sender_id
  AND c.recipient_id=r.recipient_id
  AND sent_status=0
GROUP BY c.sender_id, r.recipient_id
ORDER BY DATE(previous_sent_time), RAND()
LIMIT 100

Thanks

Possible duplicate of [mysql - Locking rows for select query?](https://stackoverflow.com/questions/11157488/mysql-locking-rows-for-select-query) — IncredibleHat, Feb 26 '18 at 21:52
@IncredibleHat I updated my question. The `RAND()` will cause problem I think. Any solution for this ? — prograshid, Feb 26 '18 at 22:30

score 3 · Answer 1 · answered Feb 26 '18 at 22:46

You shouldn't try to fix this by using some database mechanics.

Instead, you should rethink your method of processing the "sending".

In your case, I would perform the following steps:

Create the emails you want to send, store them inside the database. Maybe 100.000 records in 10 seconds - that's no issue.
Use a script that processes these records according to your limitations (50-60 mails per minute) - That's a simple SELECT with proper limits, called every minute.

Voila, Your mails are beeing send. 100.000 Mails with 60 mails per minute would require about 27 hours - but you can't bypass "Hosting-Limitations" by altering code.

Wrap the execution into a Singleton, or some "locking" method to make sure, there is only one Mail-Queue-Processor active. Then you don't have any issues with double selects of the same mail-queue-entry .

Yeah. His update to show the much more complex sql query, makes the other method more cumbersome/impractical. What you have described here, is what I have done before and it works well within its limits. — IncredibleHat, Feb 26 '18 at 23:22

score 0 · Answer 2 · answered Feb 26 '18 at 21:54

0

I actually ran into this issue myself when developing a similar app. My solution was that at the beginning of the cron, I set every processing task in the database to be marked as in process.

Once the script is done, it marks it as done and moves on.

Using this method, if another script runs over the same item, it will automatically skip it.

answered Feb 26 '18 at 21:54

Newah

102
1
9

But this will reduce number of total emails to be send in day as script is taking more than 60 seconds, every other minute no mail will send – prograshid Feb 26 '18 at 22:01
@prograshid I think you misunderstood. Each thread (each run of the script), grabs X to do, immediately marks those X as "in progress" before the long process, then does them and marks them "completed". That way no run of the script a minute later will grab those same rows the first run is working on. – IncredibleHat Feb 26 '18 at 22:11
Got you @IncredibleHat – prograshid Feb 26 '18 at 22:21

Prevent SELECTing same rows in concurrent process - MySql PHP

2 Answers2