Re: deadlock trace file in weblogic environment
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 14 Feb 2009 15:26:16 -0800 (PST)
[Resend with less quoted text]
I always thought that mode number was the same as we saw in v$lock or the
parameter for 'enq:%' (or 'enqueue') events. But according to
http://www.rachelp.nl/index_kb.php?menu=articles&actie=show&id=15
these RAC wait-for-graph lock mode numbers start from 0, not 1! (No wonder why
I can't figure out what would cause a TX mode 5 lock.) So in your case, you get
a share mode (4) lock with WFG showing number 3. With that in mind, you would
have guessed it may not be due to a simple out-of-order update by the
developers. If pinning them to one node still produced the deadlock, you could
even set cluster_database to false and get a nice non-RAC deadlock graph with
all familiar info and the SQL involved. But obviously it requires downtime.
Speaking of ITL shortage, unless you intentionally set pctfree very small or
maxtrans very small, why would ITLs not automatically expand? Free space in the
index block was used up? Was there parallel DML involved? It's getting
off-topic now.
Yong Huang
--- On Sat, 2/14/09, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx> wrote:
> From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
> Subject: Re: deadlock trace file in weblogic environment
> To: yong321@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Date: Saturday, February 14, 2009, 2:07 PM
>
> Hi Huang
> That was a complex problem (with simple solution). In fact, original issue
> involved three instances in a deadlock scenario (happening randomly).
> Developers swore that these threads in different instances will not lock
> same rows and of course, since all three instances were involved in this
> deadlock and LMD trace files were quite useless. We ended up pinning them to
> one node and debugged this further. Deadlock graph I posted was that for
> that scenario.
> When we resolved it, problem was very simple, initrans of 2 on one index (
> that was reorged!) caused this issue and rebuilding that index with higher
> initrans fixed it.
>
> Cheers
> Riyaj
>
> On Sat, Feb 14, 2009 at 12:02 AM, Yong Huang
> <yong321@xxxxxxxxx> wrote:
>
> > You're right, Riyaj. I googled and found a few wait-for-graphs that have
> > mode 3 or 4, although most are 5.
> >
> > I think your graph can be interpreted sequentially along the lines: process
> > with transaction ID's (whatever it is) [65662,2751] is blocked by process
> > [65657,2197], which is blocked by the next one, which is blocked by next,
> > ... and it wraps back up to [65662,2751]. I don't know what they're doing,
> > and if all of them are on the same instance as in your case, you probably
> > don't even see the SQL involved. Oracle definitely needs to improve the
> > trace file to at least the same as a non-RAC deadlock trace.
> >
> > Thanks for the info about _lm_dd_interval. I noticed 11g shortens the
> > default value of it from 60 to 10, and added a few new _lm_dd% params.
> >
> > Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
