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

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;

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.

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.

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);
?>