MySQL – Locking reads using transaction

Here is a quick post before I hit work on a Sunday afternoon. I was trying to create some sort of locking mechanism designed to trigger the same tasks from multiple clients but only one should be executed/run. I tried using transactions but it’s not enough.

Objective

The main objective is to let our current setup work as it is, ex: to have the same configuration and everything, including cron jobs but at the same time allow only one cron job to be executed at a time. No same tasks should ever executed as it would result on duplicate events and notifications. We can setup a dedicated server to handle the cron but at this time, there are constraints that won’t let us do this.

First Approach

My first approach was just to wrap the select and update in a transaction. It goes like this.

START TRANSACTION;
SELECT scheduled_task WHERE name = 'task_name';
/* If not locked, then acquire lock, else, abort */
UPDATE scheculed_task SET last_run = timestamp_here WHERE name = 'task_name';
COMMIT;

I didn’t actually test this as I’m not sure if I can properly trigger them at exactly the same time. However, I remember that there is an option on terminal where you can type a command and it gets copied to another terminal and as a result, you can execute the same command at the same time.

Using my current terminal, Konsole from KDE (but I’m not using KDE), I split the terminal window and tried to run the task script at the same time using the feature described above. To my disappointment, both terminal executed the same tasks at the same time. The lock didn’t work after all.

SELECT … FOR UPDATE

MySQL has an option on SELECT that locks records from reading while the current transaction is not yet committed. Simply add FOR UPDATE at the end of the SELECT query.

START TRANSACTION;
SELECT scheduled_task WHERE name = 'task_name' FOR UPDATE;
/* If not locked, then acquire lock, else, abort */
UPDATE scheculed_task SET last_run = timestamp_here WHERE name = 'task_name';
COMMIT;

If my understanding is correct, it locks the specific row from reading, ex: other clients won’t be able to see the record until the transaction is committed.

PHP Codes

To translate these into PHP world, the code block may look like this (details redacted).

public function getLock($name)
{
    // Note: Custom data mapper library
    // Start transaction
    $this->getConnection()->beginTransaction();

    try {
        // Get task, query has FOR UPDATE
        $task = $this->getTask($name, true);

        // Check if the task is not yet locked
        // Update lock/acquire lock, commit and return true
        // ELSE rollback and return false
        
        // Commit, return true
        $this->getConnection()->commit();
    } catch (\Exception $e) {
        // Rollback, return false
        $this->getConnection()->rollBack();
    }
}

After implementing the new SELECT query, I’m able to verify that only one script successfully executed the task while the other throws error due to transaction lock.

That’s it.