how to atomically claim a row or resource using UPDATE in mysql
- by Igor
i have a table of resources (lets say cars) which i want to claim atomically. 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 IS bob
that 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?
