Hi,
What should the official behaviour of
select ... for update limit 1 be?
This is one of the methods I'm considering for multiple worker processes to
each select a _different_ task from the same "todo queue" table.
e.g.
begin;
select taskid from todoqueue where assignedpid=0 for update limit 1;
update todoqueue set assignpid=$mypid where taskid=$taskid;
commit;
Would it be better to lock the table instead? Locking the table would stop
the scheduler from inserting new tasks to the queue table, which is not
desirable.
Right now select for update limit 1 seems to lock all rows in the where
clause, not just retrieved rows. This is ok (would be nicer if it was just
one, but that's pushing it ;) ), but I suspect I'm approaching a less
travelled region with this :). Any potential gotchas I should look out for?
Thanks,
Link.