On Fri, Jun 28, 2013 at 1:16 PM, Kenneth Tilton <ktilton@mcna.net> wrote:
We want to make sure no two examiners are working on the same case at the same time, where the cases are found by searching on certain criteria with limit 1 to get the "next case".
I've been using this pattern for about 14 years with never a failure:
begin;
select from mytable where work_started IS NULL FOR UPDATE ORDER BY work_whentostart ASC LIMIT 1;
update mytable set work_started=NOW() where work_id=XXX;
commit;
... do the work on row id XXX.
This pattern minimizes the time that the lock is held, and it is only held on that one row so other work continues unaffected on other rows. If you can arrange your query that picks the next thing to work on be more randomized, you can minimize the lock contentions too. For my use, randomization would possibly leave to starvation, so I chose to take the hit on the locks.