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

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

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.