Re: Deadlock on 11g - SELECT FOR UPDATE WITH ORDER BY

From: Luis <lcarapinha_at_gmail.com>
Date: Sat, 25 May 2013 18:00:45 +0100
Message-ID: <CANx=q_YyO7uqC=XzE1T=pr06auAt6b4vymx+mzv8civTdFeCOw_at_mail.gmail.com>

Hi Eriovaldo,
If you upgrading to 11.2.0.2/3 you will sure hit that bug (13371104) the behavior of select for update changed, so ORDER BY is completely useless in getting row lock order.
Oracle told me that they will eventually fix in 11.2.0.4, but until then you have 2 options:

1 - Change code and not rely on ORDER BY. This can be very hard at times and may include application architecture changes. 2 - You can apply the workarounds in the blog (they work, as me if you can force full or range scan index sorted) but that may be expensive and time consuming, since you need to make sure that your plan is the correct one.

Thank you,
Luis

1

On Sat, May 25, 2013 at 4:28 PM, Eriovaldo Andrietta <ecandrietta_at_gmail.com>wrote:

> Hi,
> There is a code that run on 10g sucessfully.
> After migration to 11g started getting ORA-00060 (Deadlock).
>
> Then looking for solutions, people got it this link:
>
>
> http://orasql.org/2013/02/16/workaround-for-deadlock-with-select-for-update-order-by-on-11-2-0-2-11-2-0-3/
>
> Any experience with this kind of problem and solution ?
> Did you applied workaround like this and solved problems related to SELECT
> FOR UPDATE WITH ORDER BY ?
>
> I guess that the sample works fines ( I did not test it).
>
> Regards
> Eriovaldo
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cumprimentos,
Luís Marques

--
http://www.freelists.org/webpage/oracle-l

Received on Sat May 25 2013 - 19:00:45 CEST