Re: Application locking

Sorry, big typo below:

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?

-kt

> If not, can we do better by bundling it all into one statement?:
>
> with nc as (select c.id clm_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) , ic as (insert into rdf (iasid, s,p,oint) select 42, user_id,
> 'started-editing', clm_id from nc returning oint locked) select locked from
> ic limit 1 into locked_id; return locked_id;
>
> If I am all wet, is their a reliable way to achieve this?
>
> Thx, kt
>
> --
> Kenneth Tilton
>
> *Director of Software Development*
>
> *MCNA Dental Plans*
> 200 West Cypress Creek Road
> Suite 500
> Fort Lauderdale, FL 33309
>
> 954-730-7131 X181 (Office)
> 954-628-3347 (Fax)
> 1-800-494-6262 X181 (Toll Free)
>
> ktilton(at)mcna(dot)net <glipari(at)mcna(dot)net> (Email)
>
> www.mcna.net (Website)
> CONFIDENTIALITY NOTICE: This electronic mail may contain information that
> is privileged, confidential, and/or otherwise protected from disclosure to
> anyone other than its intended recipient(s). Any dissemination or use of
> this electronic mail or its contents by persons other than the intended
> recipient(s) is strictly prohibited. If you have received this
> communication in error, please notify the sender immediately by reply
> e-mail so that we may correct our internal records. Please then delete the
> original message. Thank you.
>

--
Kenneth Tilton

*Director of Software Development*

*MCNA Dental Plans*
200 West Cypress Creek Road
Suite 500
Fort Lauderdale, FL 33309

954-730-7131 X181 (Office)
954-628-3347 (Fax)
1-800-494-6262 X181 (Toll Free)

ktilton(at)mcna(dot)net <glipari(at)mcna(dot)net> (Email)

www.mcna.net (Website)
CONFIDENTIALITY NOTICE: This electronic mail may contain information that
is privileged, confidential, and/or otherwise protected from disclosure to
anyone other than its intended recipient(s). Any dissemination or use of
this electronic mail or its contents by persons other than the intended
recipient(s) is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by reply
e-mail so that we may correct our internal records. Please then delete the
original message. Thank you.