SELECT stuff ... FOR UPDATE;
fetches `stuff` and prevents (via a lock) other connections from messing with row(s).
work with `stuff`.
UPDATE the rows SELECTed (or a subset of them)
UPDATE Queue... WHERE ... LIMIT... -- Grab row for self
SELECT ... WHERE ... -- Make sure I got it, and fetch id
if got it -- Perhaps someone else got it
work on application things
DELETE FROM Queue WHERE ... -- remove from queue
Pattern 1 locks the row(s) longer, but allows more flexibility in what is done.
Pattern 2 has two quick locks. But, because of other threads potentially messing with the same row(s), you have to check that you really got the row.
Since you mentioned queuing, think of it this way... You have two structures: (1) the queue, and (2) your application data. In general it is better to handle them separately. The way to do that is to avoid having both in the same transaction.
Having both in the same transaction causes locks to last longer for both. And it forces rollback of both, even if that is inappropriate.
Pattern 1 is better when the
SELECT and the
UPDATE are both working on application stuff.
Something like Pattern 2 is useful with you need to hang onto something "too long". (That is, more than a few seconds. Or from one html page to another, where you have lost the connection.)