Re: Stored Procedure with concurrent SELECT / UPDATE
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Wed, 09 Feb 2011 19:11:44 +0100
Message-ID: <4d52d8df$0$14260$ba620e4c_at_news.skynet.be>
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:4530093713805
>
> I see that the "simple" FOR UPDATE clause is not enough.
> So I've found http://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.
Date: Wed, 09 Feb 2011 19:11:44 +0100
Message-ID: <4d52d8df$0$14260$ba620e4c_at_news.skynet.be>
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:4530093713805
>
> I see that the "simple" FOR UPDATE clause is not enough.
> So I've found http://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. Received on Wed Feb 09 2011 - 12:11:44 CST