Re: Deadlock issue - Financials

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 - 14:34:14 CDT