MySQL: simultaneous access

Question

I have the following algorithm:

1. Choose a row that has no answer and that is not locked (status 0)
2. Lock this row to prevent others from getting it (status 1)

there is also status 2 - row has an answer and finished.

SELECT * FROM details WHERE RowStatus=0 AND taskid=".$task_id." ORDER BY RAND() LIMIT 1

 UPDATE details SET RowStatus=1 ,Agent='".$_SESSION['username']."' where TaskID=".$row['TaskID']." and RowId=".$row['RowId']

The problem is that if i have 10 rows left and 8 agents working on the task, sometimes happens that they submit previous row in exactly the same time. Then both of them select the same row and only one of them locks the row with his name meaning - one of them is working on the same row.

Its sounds like a minor issue but somehow i got this happen 5-10 times in a 2500 rows task. Is there any way to lock the row in the same time as selecting it? Prevent other user to select this row?

Answer 1

you should lock row (of course if you are using InnoDB). Scenario is below:

begin;
select ... FOR UPDATE;

doing what you want with task

update selected task

commit;

Answer 2

One way (much better) is using a transaction: PHP + MySQL transactions examples

The second options is making an UPDATE first (lock task), and then SELECT task by Agent and Status.

Answer 3

A simple way may be:

After UPDATE, run a SELECT on that row and check that Agent is the current user. If it isn't, re-select a new row

Answer 4

I got an idea how to do it without any special access, stored procedures, triggers or transactions.

You need to create a table called for example Semaphores with two columns - name (text) and state (int). And for your purpose we will create one row called details with the state of 0.

Then your code can look like this:

$semaphore = query("UPDATE Semaphore SET state = 1 WHERE name = 'details'"); // It's just pseudo code for querying database - do it yourself

while(affected_rows($semaphore) == 0) {
    $semaphore = query("UPDATE Semaphore SET state = 1 WHERE name = 'details'");
    sleep(10); // sleep to not query too often
}

fetch_and_query("SELECT * FROM details WHERE RowStatus=0 AND taskid=".$task_id." ORDER BY RAND() LIMIT 1");

query("UPDATE details SET RowStatus=1 ,Agent='".$_SESSION['username']."' where TaskID=".$row['TaskID']." and RowId=".$row['RowId']);

query("UPDATE Semaphore SET state = 0");

This way every user who runs this query will have to wait for others to finish their SELECT+UPDATE thing. Also it is just a trick - the work of the lock does the part with updating the Semaphore table, you just need one query to find out if the value of the lock was 0. If it was then the function affected_rows will tell you that you can go on with your script (please note that affected_rows might be in PHP for example mysqli_affected_rows but also any other equivalent). Remember that when you update a row which doesn't need updating then this function will return a value of zero anyway.

My answer on my blog: http://kelostrada.pl/en/mysql-en/locking-rows-with-myisam-php/