how to atomically claim a row or resource using UPDATE in mysql

Question

i have a table of resources (lets say cars) which i want to claim atomically. I then want information about which resource I just claimed.

If there's a limit of one resource per one user, i can do the following trick:

UPDATE cars SET user = 'bob' WHERE user IS NULL LIMIT 1
SELECT * FROM cars WHERE user = 'bob'

This way, I claim the resource atomically and then I can see which row I just claimed.

This doesn't work when 'bob' can claim multiple cars. I realize I can get a list of cars already claimed by bob, claim another one, and then SELECT again to see what's changed, but that feels hackish.

What I'm wondering is, is there some way to see which rows I just updated with my last UPDATE?

Failing that, is there some other trick to atomically claiming a row? I really want to avoid using SERIALIZABLE isolation level. If I do something like this:

1 SELECT id FROM cars WHERE user IS NULL
2 <here, my PHP or whatever picks a car id>
3 UPDATE cars SET user = 'bob' WHERE id = <the one i picked>

would REPEATABLE READ be sufficient here? In other words, could I be guaranteed that some other transactions won't claim the row my software has picked during step 2?

Answer 1

UPDATE cars SET user = 'bob' WHERE id = 123 AND user IS NULL;

The update query returns the number of changed rows. If it has not updated any, you know the car has already been claimed by someone else.

Alternatively, you can use SELECT ... FOR UPDATE.

Answer 2

I'm not sure why there is so much misinformation in these answers, but the answer is straightforward (even if an advanced SQL topic). This is what "locking" is for in pretty much any RDBMS. The exact syntax depends on the vendor and version, and some offer syntax that tries to hide the lock from the user (typically when both the select and the update are in the same query).

For MySQL, you would first use the SELECT ... FROM ... FOR UPDATE; which tells the database to set an exclusive lock on each record it returns.

Important don't lock more rows than you absolutely need to! Make the "SELECT FOR UPDATE" query as granular as you can, with liberal usage of "WHERE" and "LIMIT" clauses.

Afterwards, when the same connection to the database issues an UPDATE ... on the same rows that were previously locked, that lock is released and others may access that row once more.

So let's say you have a job queue, with a field "STATUS" that is used to set the progress state of each job. 0 is for queued, 1 is for in progress, and 2 is for done, 3 is for failed, etc.

Each runner could atomically obtain a job to run (so that no two runners try to work the same job) by issuing the following:

SELECT ID, * FROM JOBS WHERE STATUS = 0 LIMIT 1 FOR UPDATE;

then

UPDATE JOBS SET STATUS = 1 WHERE JOBS.ID = X;

then it can run the job, and update the database when done:

UPDATE JOBS SET STATUS = [2|3] WHERE JOBS.ID = X;

Important

From the MySQL documentation:

All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

SELECT FOR UPDATE does not lock records if autocommit is enabled. Either disable autocommit or (preferably) using START TRANSACTION; SELECT ... FROM ... FOR UPDATE; UPDATE ...; END TRANSACTION;

Answer 3

update cars  
set @id = id, user= 'bob' 
where user is null

is guaranteed to be atomic and @id will tell you what was the last row you updated.

Answer 4

One thing you can use is a SELECT FOR UPDATE. This will let you do your select, know what you selected and then update those values. The lock is released when the transaction is complete.

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "test");

mysqli_autocommit($link, FALSE);

$result = mysqli_query($link, "SELECT id FROM cars WHERE user IS NULL");

// do something with the results

mysqli_query($link, "UPDATE cars SET user = 'bob' WHERE id = <the one that was picked>");

mysqli_commit($link);

mysqli_close($link);
?>