how to determine cause of deadlock

From: gdas <gdas1NOgdSPAM_at_yahoo.com.invalid>
Date: 2000/07/13
Message-ID: <0175edcc.7d7910ae@usw-ex0101-006.remarq.com>#1/1

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?

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).

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 Received on Thu Jul 13 2000 - 00:00:00 CDT