From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Kenneth Tilton *EXTERN*" <ktilton(at)mcna(dot)net>, pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Application locking |
Date: | 2013-07-01 07:54:00 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17BC251F@ntex2010a.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2013-07-01 08:01:39 | Re: Postgres case insensitive searches |
Previous Message | Albe Laurenz | 2013-07-01 07:36:11 | Re: (Default) Group permissions |