Using a file lock to stop cron jobs updating the same record

Recently I was faced with an issue of deliberately overlapping cron jobs both trying to work on the same database record.

After some research, using flock (file lock) seemed to be a good option.

At each minute, I trigger a job to update the database with information scraped from another site (public domain information – before you ask). I let each job run for just under 3 minutes, so that at any time there are 3 jobs running. If I want to push it further I can just up the maximum time for each job.

The project is in Laravel and uses Eloquent for ORM and mysql for the database.  The problem faced was that the query to find out which record to service could take a relatively long time so that two processes could come to the same answer about which was the next record to update.

//open a lock file - can be used to pause other processes when they are also trying to query db
$lockfile = fopen(storage_path('locks/operatorUpdate.lock'),"w");

while(microtime(true)-$time_start < 178) {

    flock($lockfile, LOCK_EX);

        // establish oldest record
        $operator = \App\Operator::orderBy('checked_at','asc')->orderBy('id','asc')->first();
        $operator->checked();

    flock($lockfile, LOCK_UN);

    // do what I need here to process the record just grabbed

}
fclose ($lockfile);

I decided to keep the lock file in a storage folder called locks. I used the storage_path() call to ensure that the path is the same irrespective of how I called the function. (cron jobs default to the root user home folder)

Bear in mind that I’m placing a blocking lock on the file just whilst I grab the oldest record and set its checked_at date. A second job coming along at the same time will hit the lock and wait for the quarter second it takes for the original query to run (I have 280,000 rows in the table).