Re: how to determine cause of deadlock

Are you sure you're seeing deadlock, not regular locks? The term deadlock should be used in the correct sense. How do you know you're experiencing a deadlock? When it happens, your udump trace file should tell you something like:

A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object STAGE.WELL_HDR

A deadlock could be caused by two users updating the same row at the same time. It's not easy to completely get rid of this problem. Steve Adams has a message posted on 12/28/99 on this group. Unfortunately I can't find it at deja.com now. He says if it's a library cache lock, v$lock won't show it at all. Only enqueue locks are in v$lock. I have to digest what he says.

Yong Huang

<oratune_at_aol.com> wrote in message news:8kl34o$s25$1_at_nnrp1.deja.com...
> In article <0175edcc.7d7910ae_at_usw-ex0101-006.remarq.com>,
> gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> > Does anyone know of a way to find the cause of a deadlock
> > problem? We've got multiple processes inserting and updating
> > against oracle 8.1.5. The application is believed to have been
> > designed in such a way that contention would not occur.
> > Obviously, we were wrong because every so often, a deadlock
> > problem will occur. We've looked at the code of the application
> > to try to determine the potential statement or statements that
> > might be causing this condition, but this has been quite
> > difficult and the whole premise behind this approach is based on
> > guess-work and trial and error. We've made some guesses,
> > changed some code and re-ran and we still hit the deadlock
> > problem, (so obviously we guessed wrong).
> >
> > So my question is, once we hit this deadlock again, is there any
> > way for me to find out the table or perhaps even the row that
> > the deadlock problem occurred against?
[snipped] Received on Thu Jul 13 2000 - 00:00:00 CDT