Select query with FOR UPDATE

Question

I am trying to select a record from a database and lock that for update. How do I do that?

The situation is I got a process that gets triggered in multiple sessions (say 2 sessions trigger the same PHP file like a command line). When a session starts, it picks the top record and update the status as 'running' and do the process. Assume 2 sessions started at the same time, the record gets selected should be available for only one session. Next session will have to wait till the first session to release the lock.

Hence I am trying to lock the record while select and when the status get updated, the record gets auto release.

I got the below query.

    $query->select($this->_db->quoteName('jo.job_id'));

    $query->from($this->_db->quoteName('#__jsch_job').' as jo');
    $query->where($this->_db->quoteName('jo.published').'='.$this->_db->quote("1"));
    $query->where($this->_db->quoteName('jo.job_status').'='.$this->_db->quote("Ready"));
    $query->order($this->_db->quoteName('jo.ordering').' ASC');
    $query->setLimit('1');

Where should I add 'FOR UPDATE' in the above query?

Answer 1

It looks like you want to execute InnoDB Record Locking.

I haven't personally done this before and I don't know whether you need to specify a primary id in your query, but if you merely wish to append the FOR UPDATE expression to the end of your query, you can extend the sql string in the setQuery() call.

$db->setQuery($query . " FOR UPDATE");

If you wanted to see the generated string before passing it to setQuery(), you could

JFactory::getApplication()->enqueueMessage($query . " FOR UPDATE" , 'info');

to see the following (I have added newlines for easier viewing in this post):

SELECT `jo`.`job_id`
FROM `#__jsch_job` as jo
WHERE `jo`.`published`='1'
  AND `jo`.`job_status`='Ready'
ORDER BY `jo`.`ordering` ASC
LIMIT 1
FOR UPDATE

---

That said, I only see Record Locking being done within a single transaction. Here's a great read: InnoDB row locking - how to implement

In other words, I don't think the row locking technique alone will be enough for your task.

Instead (if I am understanding correctly), I recommend that you create a claimerId column in your table, then when a user claims the record...

1. The user's sessionId goes into the claimerId column of that row. You might even add a claimedOn column as a datetime stamp and have an independent process clear away claimerId values once claimedOn has exceeded a sensible time allowance.

2. When the user's action executes an update on the claimed row AND the user's sessionId matches the claimerId AND the claimedOn is not expired, include the additional changes in the SET clause (claimerId = NULL, claimedOn = NULL). (Or use a trigger, if you like, when a row with a claimerId is UPDATEd, wipe the claiming column values.)

3. If a user logs out before doing anything with their claimed row, then you will need to execute a query that wipes the claiming column values.

Now, after all of that work is effectively implemented, you still need to mitigate the race condition on the claiming functionality in the first place. I don't know how much traffic you are dealing with, but you may need to rely on table or record locking techniques to prevent claimed row thievery (overwriting the claimerId).

I am thinking that you would something like...

Pseudocode:

[startYourTransaction]

SELECT [yourPrimaryKey]
FROM #__jsch_job
WHERE published = 1 
  AND job_status = 'Ready'
  AND claimerId IS NULL
LIMIT 1
FOR UPDATE

UPDATE #__jsch_job
SET claimerId = [userSessionId], claimedOn = NOW()
WHERE [yourPrimaryKey] = [theIdFromPreviousSelectQuery]

[endTransaction]

... some indeterminate time passes (currently the row is "logically claimed", not "record locked")...

UPDATE #__jsch_job
SET [whateverColumn] = [whateverNewValue], claimerId = NULL, claimedOn = NULL
WHERE [yourPrimaryKey] = [theIdFromPreviousSelectQuery]
  AND claimerId = [userSessionId]

Joomla Documentation on using Transactions: https://docs.joomla.org/Using_transactions_in_Joomla

Sorry that I cannot speak from experience; you will need to continue researching this task. If you make further discoveries or find a better way please post an answer to inform the community.

Answer 2

I have not used it personally but if you want like chaining of your database queries you can use inbuilt Joomla functions released since Joomla 2.5, unlockTables(); and lockTable(); Format to use is $query->lockTable(string $tableName).

$tableName is your tablename with #__

The table gets locked unless released for another query. At the end just do $query->unlockTables(); to release the table.