Application locking

Sorry, big typo below:

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?

-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@mcna.net <glipari@mcna.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@mcna.net <glipari@mcna.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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.