Simultaneous running cron jobs records processing mix

Question

I'm struggling with multiple same time lunched cronjobes same record process issue

Intro

• I have db table where is present 100+ customers
• For each customer i have to run some script via cronjobe per each 5 minutes
• Cronjobe takes each customer from db immediately update last used timestamp so that next cron will select 5 minutes older processed next record
• Cronjobe processing takes approx 30second
• So it mean to process all 100 customers during required 5 minutes i will need to have approx 10 simulations cron jobes wokring
• all cron's defined as: * * * * * php cron.php (so each 1 minute lunched 10 cronjobes)
• The cron dose some network lookup to that customer ip and log it to log database it should happend for each customer during each 5 minutes as later i will draw chart based on logs for each customer
• code is written in PHP, DB is MySql

The problem is when that 10 cronjobes starts at the same time randomly happens that 2 cronjobes selected same customer from database to process

So it mean 2 crons are start at alsmost same time (micro seconds diffarance) both are select at same time last unprocessed row (e.g. id:17) from database then update same id17 in database 3-dth lunched cronjobe already took id:18. But i need that each coronjobe will take unique next record from database not same

As a workaround i tried to add random sleep(rand(1,10)) delay at beginning of cron.php but don't help mutch, random duplication still happens cause cron continuously selects last unprocessed next customer which sometime matches with another cornjobe next customer select at same time

Is there any solutions present for this situation ???

Answer 1

The solution you seek is collaborative locking. The customer must be marked "locked" by some job, and no script must choose a customer that is locked by another script.

Also, you must do this in such a way that no two jobs choose the same customer to acquire.

In MySQL you can do:

$me = getmypid();
$conn->execute("SELECT GET_LOCK('choosing', 5) AS okay");
// Check the returned value of okay. If it is not 1, exit() immediately.
// choose some customer in some way. The smallest Id with OwnedByPid=0 for example. The query should be fast enough to run in under 5 seconds.
$conn->execute("UPDATE customers SET OwnedByPid={$me} WHERE id={$custId};");
$conn->execute("SELECT RELEASE_LOCK('choosing'");
//

// Do your work

$conn->execute("SELECT GET_LOCK('choosing', 5)");
$conn->execute("UPDATE customers SET OwnedByPid=0 WHERE OwnedByPid={$me};");
$conn->execute("SELECT RELEASE_LOCK('choosing')");

Then, periodically - when no scripts are running - release the customers that might be marked by a script that crashed:

$conn->execute("UPDATE customers SET OwnedByPid=0;");

Or you can add another column, OwningStart, set it to NOW() when you take ownership, so you can check when OwningStart is older than 30 seconds and clear it. Or mark it as free:

SELECT MIN(Id) FROM customers WHERE OwnedByPid=0 OR OwningStart < NOW() - INTERVAL 2 MINUTE;