In one system I have a table of work to be done (for simplicity in
this explanation I'll use a table consisting of id and status).
A client-side app needs to fetch 10 available items from the table but
naturally we don't want two clients working on the same 10 items.
My first attempt at handling this was, in pseudocode:
begin transaction
select * from worktodo where status = 0 limit 10 for update
update worktodo set status = 1 where id in set selected above
commit
If I test this method by hand on two "simultaneous" transactions I
will get 10 records on the first transaction and the second will
block until the first one commits.
When the first commits the second proceeds but unfortunately returns
no records even though there are plenty available.
I can reach my goal by getting an access exclusive lock on the table
and then doing the transaction but there seems there must be a better
way.
Thoughts? Comments? Ideas? Is there a way to exclude locked rows from
a query?
Cheers,
Steve