Skip to Main Content
  • Questions

  • Who locked whom? Which objects are locked ? What sql led to the dead lock ? Who all are waiting for the locked objects?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nag.

Asked: June 30, 2002 - 9:11 pm UTC

Last updated: November 19, 2004 - 10:07 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom

Recently I trouble shooted a deadlock in production which became a showstopper.

I realised that having scripts for the following will really make life easy...

Who locked whom?
Which objects are locked ?
What sql led to the dead lock ?
Who all are waiting for the locked objects?
What kind of lock has been acquired?
What is the order of requests for locks on an objects which is already locked , and other sessions have queued up for it.

When a user comes to me and says that his session is hanging up, and assuming that it is because of a lock , how should I proceed to understand the problem and resolve it?


If I had the above scripts it would have taken 60% less time for me to resolve the issue. Can you please provide me links to the scripts.

Thanks

Nag

and Tom said...

Look in the user_dump_destination on the server.

When we deadlock, we consider that an application error and actually dump a trace file for you.


Now, a "hang" is not a deadlock. A deadlock is an ora-60, an error message. As soon as you deadlock -- we resolve it.

If you have a "hang", you can:

o run catblock.sql (from $ORACLE_HOME/rdbms/admin) once using svrmgrl when connected as SYS or INTERNAL.

o when you have the hang, run utllockt (found in same directory) in svrmgrl when connected as sys or internal. It'll show a blocker/blockee report. You can use my "showsql" (search here for showsql) to see the sql the person being blocked is trying to execute. Now, you know a) the blocker, b) the blockee, c) the thing the blockee is trying to do.



60% less time, interesting -- how did you arrive at that random number? :)







Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What is a 'Hang' and what causes it ?

I Singh, July 09, 2002 - 6:44 pm UTC

Tom I have cleared 'hangs' in the past but never understood how they were different from a deadlock with relation to Oracle not being able to detect/clear them. Also is there a parameter that controls the frquency with which Oracle checks for deadlocks? I had read 3 secs somewhere but I am positive when I tried it on my m/c Oracle detected some right away and some took more than 3 secs. Thanks in advance.

Tom Kyte
July 10, 2002 - 7:22 am UTC

3 seconds for a deadlock.

But a "hang" is not a deadlock.

A deadlock, if left alone, would be unresovable. It means I have locked something YOU want and you have locked something I want. Unless someone tells one of us to "go away", we'll be waiting for eachother forever.

Your "hang" is due to you locking something I want and leaving it locked for hours. Until you release the lock, I'll be waiting (apparently hung, but not really).

Well...

Sergey, January 09, 2003 - 7:51 am UTC

>>
Now, you know ... c) the thing the blockee is trying to do.
<<

What about the thing the blocker is doing at the moment? The problem might be on the blocker side.

Thanks a lot.


svrmgrl missing from 9i?

Li, August 14, 2003 - 1:00 pm UTC

Hi, Tom:
I tried to run the scripts in order to see lock information, but for 9i, there is no svrmgrl under /bin directly. Then, what shall I do to run the scripts without svrmgrl?
Thanks
Li

Tom Kyte
August 14, 2003 - 1:59 pm UTC

sqlplus

Re: svrmgrl missing from 9i

A reader, August 14, 2003 - 2:06 pm UTC

"Then, what shall I do to run the scripts without svrmgrl?"

Scary.


Tom Kyte
August 14, 2003 - 2:11 pm UTC

[ora920@localhost bin]$ ln -s sqlplus svrmgrl

will fix that right up ;)

RE: will fix that right up...

Mark A. Williams, August 14, 2003 - 2:15 pm UTC

... ln -s sqlplus svrmgrl ...

Yeah, but you just know that 'connect internal' will be coming next... :)

- Mark

Tom Kyte
August 14, 2003 - 2:21 pm UTC

DOH!

A reader, August 14, 2003 - 4:08 pm UTC

hahaha

A reader, August 14, 2003 - 4:13 pm UTC

Tom,

I have seen resumes of some dba's who had mentioned troubleshoot "deadlocks". I dont get this, how smart is that, these guys have been working for more than 5 years and they dont understand what deadlock is....

hang

A reader, September 10, 2004 - 1:48 pm UTC



so if we are running a large update and I expect more time,
and let us say it hang, how do we make sure it is not "hanged " vs large update ?

what if develop does not have dba privillage

Tom Kyte
September 10, 2004 - 2:23 pm UTC

v$session_wait
v$session_event
and maybe
v$session_longops


you need access to the v$ views if you want to perform any sort of diagnoses.

Deadlock

Reader, November 18, 2004 - 6:01 pm UTC

Tom,

Should we restart the instance after a deadlock?


Tom Kyte
November 19, 2004 - 10:07 am UTC

no, not any more than you would after an ora-00001

it is self resolving, the offending session gets an ora-00060 and decides what to do. nothing gets "stuck"

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.