Re: Which query is best?

From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
Date: Tue, 15 Dec 2009 12:35:47 +0300
Message-ID: <97b7fd2f0912150135u1e8e1938y81ebcbf155f6746_at_mail.gmail.com>



My main idea behind looking for an alternative sql was to reduce the waiting time for the select time to avoid long locking period for the record.

Regards,

Jaffar

On Tue, Dec 15, 2009 at 12:16 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> Jaffar,
>
> 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
> >
>
>
>
>

-- 
Best Regards,

Syed Jaffar Hussain
Oracle Certified Master (10g)
http://www.oracle.com/technology/ocm/shussain.html
Oracle ACE
http://apex.oracle.com/pls/otn/f?p=19297:4:4640302666204919::NO:4:P4_ID:186
OCP 8i,9i & 10g DBA
RAC Certified Expert
Official Oracle RAC SIG Representative for Saudi Arabian region  (
http://www.oracleracsig.org/)
I blog at
http://jaffardba.blogspot.com/
LinkedIn : http://www.linkedin.com/pub/syed-jaffar-hussain/2/a71/918
--------------------
"Winners don't do different things. They do things differently."

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 15 2009 - 03:35:47 CST

Original text of this message