Re: 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(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".
>
>
> 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