Re: Stored Procedure with concurrent SELECT / UPDATE
From: Mau C <nobody_at_hotmail.com>
Date: Wed, 09 Feb 2011 10:10:50 +0100
Message-ID: <iitlml$4j4$1_at_nnrp-beta.newsland.it>
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
Date: Wed, 09 Feb 2011 10:10:50 +0100
Message-ID: <iitlml$4j4$1_at_nnrp-beta.newsland.it>
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.
Received on Wed Feb 09 2011 - 03:10:50 CST