Re: Stored Procedure with concurrent SELECT / UPDATE

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 9 Feb 2011 07:48:13 -0800 (PST)
Message-ID: <9d29818c-c60c-429a-93e6-6cee406949cd_at_x1g2000yqb.googlegroups.com>

On Feb 9, 4:10 am, Mau C <nob..._at_hotmail.com> wrote:
> Il 08/02/2011 18.51, joel garry ha scritto:
>
> > On Feb 8, 12:29 am, Mau C <nob..._at_hotmail.com> wrote:
> [...]
> > Here are some examples:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4...
>
> I see that the "simple" FOR UPDATE clause is not enough.
> So I've foundhttp://tinyurl.com/69mkw3c
> and SKIP LOCKED seems to be the right way to avoid problems in high
> concurrency process contention.
>
> So, my stored should be changed in
>
> ...
> CURSOR c1 IS
> SELECT pkid,
>     field1,
>     field2
>   FROM tmp_table
>   WHERE status=0
>   AND rownum <=10 FOR UPDATE OF status SKIP LOCKED;
> ..
> FOR cur_row IN c1
> LOOP
> ..
> UPDATE tmp_table SET status=1 WHERE CURRENT OF c1;
> END LOOP;
> COMMIT;
> ..
>
> Does anybody agree ?
>
> Regards,
> M.

The skip lock option which is a newer option than nowait should work better for you than nowait would for what you want to do. Just be sure to run an adequate test trying to get concurrent usage of the logic before you put your code into production.

HTH -- Mark D Powell -- Received on Wed Feb 09 2011 - 09:48:13 CST