Re: Which query is best?

   For a deadlock to occur you need two locked resources - one held by process p1, also wanting a resource help by process p2, who doesn't want to give it up before p1 releases what it is currently holding. You mention only ONE query, which suggests that the resources in that case are rows from the same table, which in turns suggests that they are locked in a different order. That means that the primary keys that are used probably involve MORE columns than the ones that are shown in the query - if you lock only one row each time, with one table involved, you might have waits but no deadlock. And of course there is RAC, that adds locking of its own. No bitmap index I hope? Bitmap indexes are great for creating deadlocks ...
Hmm, if all instances are trying to lock the same rows, perhaps RAC is the wrong idea ...

And check if you have any triggers.

That's about all that comes to my mind now ...

HTH SF

Syed Jaffar Hussain wrote:
> Niall,
>
> Almost all sessions runs this query frequently followed by an update
> statement. During a dead lock situation, I found most of the session
> waiting on 'enq: TX - row lock contention' wait event. I would say
> table2 is a core table in this application.
>
> Regards,
>
> Jaffar
>
> On Tue, Dec 15, 2009 at 10:57 AM, Niall Litchfield
> <niall.litchfield_at_gmail.com <mailto:niall.litchfield_at_gmail.com>> wrote:
>
> If it's a deadlock what are the other transactions doing? That looks
> at first right and implying some constraints from the index names a
> reasonable plan for that part of the puzzle?
>
> On 12/15/09, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com
> <mailto:sjaffarhussain_at_gmail.com>> wrote:
> > Hi everyone,
> >
> > The following simple join query on one of our business critical RAC
> > databases with two instances was frequently leading into a dead lock
> > situation ultimately a causing database hang scenario:
> >
> > SELECT u.u_user_id, u..u_mcr_cust_id
> > FROM table1 U,table2 A
> > WHERE
> > A.UCS_CHNL_ID = :b1 AND A.UCS_LOGIN_NAME = :b2 AND A.UCS_USER_ID =
> > U.U_USER_ID FOR UPDATE
>

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