Thread: Application locking

Le vendredi 28 juin 2013 à 13:18 -0400, Kenneth Tilton a écrit :

>
> 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".
>
>
>         A naive approach would be (in a stored procedure):
>
>
>                 next_case_id := null;
>
>
>         select id into next_case_id
>         from cases c
>         where unfinished = true
>         and not exists (select 1 from table_lock
>         where table_name = 'case' and row_id = c.id)
>         limit 1;
>         if found then
>         insert into table_lock (table_name, row_id) values ('case',
>         next_case_id);
>         end if;
>
>         return next_case_id;
>
>
>         I suspect it would be possible for two users to get the same
>         case locked that way. Yes?
>
>
>         If so, would adding "for update" to the initial select prevent
>         a second caller to block on their select until the first
>         caller had written out the lock, effectively preventing two
>         callers from locking the same case?
>
>
> Change "prevent" to "cause":
>
>
> If so, would adding "for update" to the initial select cause a second
> caller to block on their select until the first caller had written out
> the lock, effectively preventing two callers from locking the same
> case?
>
>

You could put a unique constraint on table_lock, or make (table_name,
row_id) the primary key; this would prevent the second user from locking
the same case and you can treat the exception in your code.



--
Salutations, Vincent Veyron
http://vincentveyron.com
Logiciels de gestion des sinistres assurance et des contentieux pour le service juridique

Kenneth Tilton 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".
> 
>     A naive approach would be (in a stored procedure):
> 
>             next_case_id := null;
> 
>     select id into next_case_id
>     from cases c
>     where unfinished = true
>     and not exists (select 1 from table_lock
>     where table_name = 'case' and row_id = c.id)
>     limit 1;
>     if found then
>     insert into table_lock (table_name, row_id) values ('case', next_case_id);
>     end if;
> 
>     return next_case_id;
> 
>     I suspect it would be possible for two users to get the same case locked that way. Yes?
> 
>     If so, would adding "for update" to the initial select prevent a second caller to block on their
> select until the first caller had written out the lock, effectively preventing two callers from
> locking the same case?
> 
> 
> Change "prevent" to "cause":
> 
> If so, would adding "for update" to the initial select cause a second caller to block on their select
> until the first caller had written out the lock, effectively preventing two callers from locking the
> same case?

That should work, did you test it?

Actually, I'd do it in a different way.  I think that the extra
"table_lock" table is unnecessarily difficult.

I'd change the "unfinished" field to a field that can hold
three stati: "open", "in progress" and "done".

Then I'd use something like the following:

CREATE OR REPLACE FUNCTION get_next() RETURNS integer
   LANGUAGE plpgsql VOLATILE STRICT AS
$$DECLARE
   c CURSOR FOR SELECT id FROM cases WHERE status = 'open' FOR UPDATE;
   next_id integer;
BEGIN
   OPEN c;
   FETCH NEXT FROM c INTO next_id;
   UPDATE cases SET status = 'in_progress' WHERE CURRENT OF c;
   RETURN next_id;
END;$$;

Yours,
Laurenz Albe