Re: how to determine cause of deadlock

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?
>
> I was looking at several v$ views including V$lock and
> v$session_wait but those didn't appear to contain the
> information I was looking for. I then found v$locked_object
> (which I couldn't find any detailed documentation on in oracle's
> documentation). This one looks promising. It has the following
> columns:
>
> Name
> -----------------
> XIDUSN
> XIDSLOT
> XIDSQN
> OBJECT_ID
> SESSION_ID
> ORACLE_USERNAME
> OS_USER_NAME
> PROCESS
> LOCKED_MODE
>
> A couple questions: 1. What is object_id? Will it tell me the
> table that is locked? If so, it's a number. What should I join
> this to to find the table name?
>

The object_id will tell you which object is locked; the object id and the objec_name fields are found in DBA_OBJECTS. Join this table to DBA_OBJECTS to obtain the object_name from the object_id.

> 2. Locked_mode... This column also contains a number. But I
> have no idea what these numbers mean and I couldn't find their
> defintions in any of the oracle documentation that I have...and
> I have it all (but maybe I'm not looking in the right place).
>

Share update == 2
Row exclusive == 3
Share == 4
Share row exclusive == 5
Exclusive == 6

Using the above information you should be able to use decode to display the type of lock being held on a given object.

> Maybe I'm completely on the wrong track...Again. All I want to
> find out is what is table/source that is the source of the
> contention. If anyone knows of any way, to find this
> information out, I'd appreciate your response.
>
> Or perhaps the first question should be: Is there anyway to
> find this information out?
>
> Thanks,
> Gavin
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
>

You are on the right track. Use V$LOCKED_OBJECT and DBA_OBJECTS to report the information you seek.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.