CODEWITHSUNDEEP
phpmysqlsqlcron
Curtis
Curtis

Reputation: 2704

parallel cron jobs picking up the same SQL row

I've basically got a cron file which sends a multi_curl at the same time to 1 file thus being parallel, Inside the cron file.

My cron file looks like this (sends a parallel request)

<?php
require "files/bootstrap.php";
$amount = array(
    "10","11","12","13","14"
);
$urls = array();
foreach($amount as $cron_id) {
    $urls[] = Config::$site_url."single_cron.php?cron_id=".$cron_id;
}
$pg = new ParallelGet($urls);
?>

Then inside my single_cron.php I've got the following query

SELECT * 
  FROM accounts C JOIN proxies P 
                    ON C.proxy_id = P.proxy_id 
 WHERE C.last_used < DATE_SUB(NOW(), INTERVAL 1 MINUTE)
   AND C.status = 1
   AND C.running = 0
   AND P.proxy_status = 1
   AND C.test_account = 0
 ORDER BY uuid() 
 LIMIT 1

Even though I've got the uuid inside the query they still appear to be picking up the same row somehow, what's the best way to prevent this? I've heard something about transactions

The current framework I'm using is PHP, so if any solution in that would work, then I'm free to solutions.

Upvotes: 0

Views: 214

Answers (1)

Guntram Blohm
Guntram Blohm

Reputation: 9819

Check the select for update command. This prevents other parallel queries from selecting the same row by blocking them until you do a commit. So your select should include some condition like last_process_time > 60 , and you should update the row after selecting it, setting last_processed_time to the current time. Maybe you have a different mechanism to detect whether a row has been recently selected/processed, you can use that as well. The important thing about it is that select for update will place a lock on the row, so even if you run your queries in parallel, they will be serialized by the mysql server.

This is the only way to be sure you don't have 2 queries selecting the same row - even if your order by uuid() worked correctly, you'd select the same row in 2 parallel queries every now and then anyways.

The correct way to do this with transactions is:

START TRANSACTION;

SELECT * 
    FROM accounts C JOIN proxies P 
                ON C.proxy_id = P.proxy_id 
    WHERE C.last_used < DATE_SUB(NOW(), INTERVAL 1 MINUTE)
    AND C.status = 1
    AND C.running = 0
    AND P.proxy_status = 1
    AND C.test_account = 0
    LIMIT 1;

(assume you have a column 'ID' in your accounts table that identifies rows uniquely)

UPDATE accounts
set last_used=now(), .... whatever else ....
where id=<insert the id you selected here>;

COMMIT;

The query that reaches the server first will be executed, and the returned row locked. All the other queries will be blocked at that point. Now you update whatever you want to. After the commit, the other queries from other processes will be executed. They won't find the row you just changed, because the last_used < ... condition isn't true anymore. One of these queries will find a row, lock it, and the others will get blocked again, until the second process does the commit. This continues until everything is finished.

Instead of START TRANSACTION, you can set autocommit to 0 in your session as well. And don't forget this only works if you use InnoDB tables. Check the link i gave you if you need more details.

Upvotes: 1

Related Questions