Re: Deadlock issue - Financials

From: Doug Cowles <dcowles_at_nospambigfoot.com>
Date: Mon, 13 Sep 1999 16:00:44 -0400
Message-ID: <37DD57EB.B25DBBED@nospambigfoot.com>

Thank you very much for your response,

I think we may have to push it back to them, but in the meantime - here is some of
the trace file - I have a couple of questions below. I've abbreviated some of it.

                            Blockers                                Waiters
                           process    session   holds waits  process session

holds waits
Resource Name

TX-blablah          50           32          X                  68
67                   S
TX-blahblah        68           67          X                   50
32                   S

Rows waited on:
Session 67:no row
Session 32: no row

Process State
Process Global information process: bunch of hex etc.,
holding 400001a20 Parent=children enqueue hash chains level=4 state=busy recovery area
Dump of memory
Bunch of stuff that is currently indecipherable to me at present.

Questions: Can I learn anything from this? In particular the wait=S. What does that mean? Also, does the output about children enqueue hash chains have any meaning to anyone?
I'm wondering if the re-org has caused locks to be escalated to page level locks or something?

Once again, any help of any kind is appreciated...

Paul Berger wrote:

> It's been my experience that deadlocks usually occur when a defined
> transaction is too large attempting to do too much work, particularly on a
> busy system.
>
> As you know, a trace file will be generated by Oracle when deadlocks occur.
> A lock map will manifest delimiting the two deadlocking sessions. The trick
> is twofold. One to identify the schema owners of the sessions in question
> and two, ultimately, to determine the code that's deadlocking.
>
> If it's your own code you have some options. Use a generic error handler
> that all programme code utilizes. Subsequently trap such errors in said
> 'handler' dumping the session information when such deadlock errors
> manifest.
>
> In your case, though this would involve some work. You could try to pasre
> the trace file somehow scanning for ora-00060 errors. When they arise engage
> an Oracle session trapping the Oracle session detail as mentioned.
>
> Or since it's Oracle Application code push it back to them.
>
> Good luck,
> PB
> Doug Cowles <dcowles_at_nospambigfoot.com> wrote in message
> news:37DD4346.51E48018_at_nospambigfoot.com...
> > We have a situation where the autoinvoice process now deadlocks after a
> > reorg.
> > This is 10.7 character prod 12. (Oracle 7.3.4) It appears to deadlock
> > after a select statement according to the log. Why would that be?
> > The statement that occurs just before the deadlock in the log is
> > select s.rowid,
> > l.interface_line_id,
> > l.request_id,
> > l.last_update_login,
> > l.last_updated_by
> > from ra_interface_salescredits s,
> > ra_interface_lines l
> > where l.request_id = 369995
> > and l.line_type = 'LINE'
> > and l.interface_line_context = 'OpenUPTIME'
> > and l.interface_line_context = s.interface_line_context
> > and l.interface_line_attribute1 = s.interface_lineattribute1
> > and l.intreface_line_attribute2 = s.interface_lineattribute2
> > and l.interface_line_attribute3 = s.interface_lineattribute3
> > and l.interface_line_attribute4 = s.interface_lineattribute4
> >
> > We checked the indexes on RA_INTERFACE_LINES_ALL,
> > RA_INTERFACE_SALES_CREDITS_ALL,
> >
> > and they appear to be the same in the 2 environments.
> > We have one instance that is an old stamp of the database where
> > autoinvoice still runs fine, and the re-orged one where it does not.
> > Those are the 2 environments.
> >
> > Can anyone make any recommendations how to proceed? Anything at all
> > would be appreciated within any range of the help spectrum.. How to
> > track it down etc., It's
> > proprietary Oracle software so it's not a screwy developer code
> > problem..or even if it is, there is probably a resolution.
> >
> > - Dc.
> >

Received on Mon Sep 13 1999 - 15:00:44 CDT