Re: Which query is best?
Date: Tue, 15 Dec 2009 10:16:23 +0100
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 ...
Syed Jaffar Hussain wrote:
> 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.
> 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