"select for update; update"
-->
"BEGIN; select for update; update; COMMIT;"
The uuid approach:
* Would need index on uuid
* Would require updating the uuid index
* Would still lock row(s)
Would it be even better to stuff random numbers in the table to start with (as opposed to when the update comes along)? This might be a way to grab the row:
BEGIN;
$id = SELECT id, ... FROM grabber_in
WHERE status = 'FREE'
AND rnd >= RAND()
LIMIT 1 FOR UPDATE;
UPDATE grabber_in SET status = 'BUSY' WHERE id=$id;
COMMIT;
You might get away with a single statement, something like this:
UPDATE grabber_in SET
status = 'BUSY',
id = LAST_INSERT_ID(id)
WHERE status = 'FREE'
AND rnd >= RAND()
LIMIT 1;
SELECT LAST_INSERT_ID(); -- session-specific, no need for LOCK or BEGIN
(Caveat: I am not sure of the details.)
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id