PHP: How to prevent multiple execution of a code (if it is already in process)

Question

Explanation

An API call (to another service) which usually takes 10-20 seconds to respond is stored in database,

After it is stored, System will try to use the API instantly to show the result to user, but it might fail (and display that it failed but we will try again automatically), therefore there is also a Cron Job set to run every 30 seconds and try the (failed) queries again.

If the API return success (whether in instant usage or using Cron Job) the flag is changed to success in database and it will not run again.

Issue

My problem is while the Instant Call to API is in process, the Cron Job might also try another call as it is not yet flagged as successful,

Also in rare cases, while the previous Cron Job is in process, the next Cron Job might run the code again.

What I have already tried to prevent the issue

I tried storing In Process API calls in a database table with Status=1 and delete them when the API call was successful or set status to 0 if it failed,

 if ($status === 0)
 {
     
     // Set Status to 1 in Database First (or die() if database update failed)
     
     // Then Call The API

     // If Failed Set Status to 0 so Cron Job can try again
     
     // If Successful Change Flag to success and remove from queue

 }
     

But what if the Instant Call and the Cron Job Call happen at the exact same time? they both check if status is 0 which it is, then both set status to 1 and execute the API Call...

Questions

1. Is what I have tried the correct way to handle this?

2. Should I worry about them happening at the exact time (the issue i explained in the Yellow Quote above) if there are a lot of calls (sometimes +500/sec)

Update Before Bounty

Isn't there really an easy way to handle such cases on the PHP side? if not, which way is better in experts' opinion? below are some methods but none of them are detailed enough and none of them have any Downvotes/Upvotes.

P.S. There are many updates/inserts to database, I don't think locking is an efficient idea and I'm not sure about the rest of ideas.

Answer 1

This is exactly why Semaphore was created for.

In php, it can be used in the following way : Using semaphores in PHP is actually very straight forward. There are only 4 semaphore functions:

sem_acquire() – Attempt to acquire control of a semaphore.
sem_get() – Creates (or gets if already present) a semaphore.
sem_release() – Releases the a semaphore if it is already acquired.
sem_remove() – Removes (deletes) a semaphore.

So how do they all work together?

1. First, you call sem_get() to fetch the identifier for the semaphore.
2. After that, one of your processes will call sem_acquire() to try and acquire the semaphore. If it’s currently unavailable, sem_acquire() will block until the semaphore is released by another process.
3. Once the semaphore is acquired, you may access the resource that you are controlling with it.
4. After you are done with the resource, call sem_release() so that another process can acquire the semaphore.
5. When all is said and done, and you’ve made sure that none of your processes require the semaphore anymore, you can call sem_remove() to remove the semaphore completely.

You can find more information and example about this in this article.

Answer 2

what I do in scripts is (pseudocode)

SCRIPT START
LOCK FILE 'MYPROCESSFILE.LOCK'
DO SOMETHING I WANT
UNLOCK FILE 'MYPROCESSFILE.LOCK'
SCRIPT END

So if the file is locked the second (duplicated) process wont run (will lock/halt/wait) UNTIL the file is UNLOCKED by the original process.

EDIT updated with WORKING PHP code

<?php

    class Locker {

        public $filename;
        private $_lock;

        public function __construct($filename) {
            $this->filename = $filename;
        }

        /**
         * locks relevant file
         */
        public function lock() {
                touch($this->filename);
                $this->_lock = fopen($this->filename, 'r');
                flock($this->_lock, LOCK_EX);
        }

        /**
         * unlock above file
         */
        public function unlock() {
                flock($this->_lock, LOCK_UN);
        }

    }

    $locker = new Locker('locker.lock');
    echo "Waiting\n";
    $locker->lock();
    echo "Sleeping\n";
    sleep(30);
    echo "Done\n";
    $locker->unlock();

?>

Answer 3

You need a proper queuing solution here. You can implement it yourself using a queue table and table locks to avoid different processes picking up the same job.

So you can pick up tasks from the queue table like this:

LOCK TABLES table WRITE;
SELECT * FORM table WHERE status = 0 LIMIT 1;
set status = 1 for the selected row
UNLOCK TABLES;

Locking the table will ensure that other processes don't do SELECTs and don't pick up the same row from the table.

Inserting the job to the queue as simple as this:

INSERT INTO table (job_id, status) VALUES(NULL, status);

Removing the job after processing is completed:

DELETE FROM table WHERE job_id = 12345;