Re: Stored Procedure with concurrent SELECT / UPDATE

From: joel garry <joel-garry_at_home.com>
Date: Wed, 9 Feb 2011 15:28:03 -0800 (PST)
Message-ID: <21bacebf-1d82-4854-ab93-ad10128cbd2c_at_d23g2000prj.googlegroups.com>

On Feb 9, 10:11 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Mau C 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.
>
> I always agree with my own propositions.

After thinking about this stuff too much, I have to tell myself "not tonight, I have a headache."

jg

--
_at_home.com is bogus.
NOVA show about Watson tonight.

Received on Wed Feb 09 2011 - 17:28:03 CST