Re: Application locking

On Fri, Jun 28, 2013 at 1:16 PM, Kenneth Tilton <ktilton(at)mcna(dot)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.