ORA-00060: deadlock detected while waiting for resource [message #616951] |
Mon, 23 June 2014 10:49  |
 |
gorants
Messages: 85 Registered: May 2014 Location: ATL
|
Member |
|
|
Hello All,
How you doing. In my aplication i see ORA-00060: deadlock detected while waiting for resource errors , How can i identify dead locks, is there any query will tell waiting/lock on tables caused dead lock issues.
Thanks,
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #616953 is a reply to message #616951] |
Mon, 23 June 2014 10:57   |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Oracle is the victim and your application design is the issue. A deadlock is always/mostly due to design flaw and seldom an Oracle issue. I believe in user defined alert mechanism. Log the details and error information as rich as possible. Unleas you are a victim of a bug, YOYO!
You must specify the complete Oracle version. There was a known bug in 11.2.0.2 with bloom filters. Do you have anymore helpful information other than just saying deadlock detected? If so, please post it.
|
|
|
|
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #616960 is a reply to message #616959] |
Mon, 23 June 2014 11:22   |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
As BS already quoted what I said above. It is the hardcore truth, whether you are willing to digest it or not.
Now, tell another thing, you are running load runner. Shall I assume that its a SPL test, or a application load test with virtual users?
P.S. Assuming that its a QA test environment with uncontrolled DML transactions done by SIT/UAT testers.
|
|
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #616963 is a reply to message #616962] |
Mon, 23 June 2014 11:33   |
cookiemonster
Messages: 13967 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Normally a deadlock is where session A locks row 1, session B locks row 2, session A tries to lock row 2 and hangs, then session B tries to lock row 1.
Row 1 and 2 can be in different tables.
The alert log file will list the error and point to a trace file.
The trace file will show which statements caused the error.
Go have a look.
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #616964 is a reply to message #616962] |
Mon, 23 June 2014 11:42   |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
gorants wrote on Mon, 23 June 2014 21:57Yes Lalit
it is application load test with virtual users.
So as I surmised, the load test with vitual users is the root cause. Oracle is just doing its job in intimating the users about the flaw. As you agreed to me that its a part of QA load testing, please make your QA manager or lead aware of it. Do not depend on such application code. Don't mind me saying this, but, PROBLEM EXISTS BETWEEN KEYBOARD AND CHAIR. And the inly responsible person is your database application developer, who might have just taken a shortcut to push his code in hurry of next Application release and not honestly doing the impact analyais. Sigh!
|
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #616966 is a reply to message #616961] |
Mon, 23 June 2014 12:06   |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
gorants wrote on Mon, 23 June 2014 21:54
Let say session 1 doing update at the same time session 2 working on the same record then deadlock issue happned
You are absolutely wrong. Session 2 will keep waiting for session 1 untill it commit or rollback the session. Did you look at the link I posted above to get a basic idea of deadlock error?
Now its high time for you to provide the trace information for the session detecting deadlock. Trust me, during my early days with Oracle, I approached MOS with similar half information. I was simply asked to provide the trace. Could you please try and do that. And beware, ONLY you know your application, not anyone of us here.
|
|
|
|
|
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #616976 is a reply to message #616967] |
Mon, 23 June 2014 13:56   |
cookiemonster
Messages: 13967 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Mon, 23 June 2014 18:09cookiemonster wrote on Mon, 23 June 2014 22:03Row 1 and 2 can be in different tables.
Not necessarily. They could be in the same table as well.
Edit : Proper quote tag.
What do you think the word "can" means?
It doesn't mean the same thing as "must" does it?
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #617096 is a reply to message #617079] |
Tue, 24 June 2014 18:44   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Tue, 24 June 2014 15:23But they are two different sessions and two different tables altogether. Shouldn't get into a deadlock scenario.
It doesn't matter how many tables are involved one or two. Deadlock means session A is holding lock on resource R1 and session B is holding lock on resource R2. Now if session A requests a lock on resource R2 it will wait till session B releases it. And if session B requests a lock on resource R1 it will wait till session A releases it. Voila, we got a deadlock. And keep in mind session != connection. In general, we can get a deadlock even when user is the only one connected to a database.
SY.
|
|
|
|
|
Re: ORA-00060: deadlock detected while waiting for resource [message #617125 is a reply to message #617100] |
Wed, 25 June 2014 05:21   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Session A:
SQL> update emp set sal = sal where ename = 'KING';
1 row updated.
Session B:
SQL> update emp set sal = sal where ename = 'JONES';
1 row updated.
Session C:
SQL> update emp set sal = sal where ename = 'ALLEN';
1 row updated.
Now session A:
SQL> update emp set sal = sal where ename = 'ALLEN';
And hangs waiting for session C to release ALLEN.
Now session B:
SQL> update emp set sal = sal where ename = 'KING';
And hangs waiting for session A to release KING.
Now session C causes a deadlock:
SQL> update emp set sal = sal where ename = 'JONES';
And hangs waiting for session B to release JONES. This causes a deadlock and Oracle chooses A as victim and rolls back update for ALLEN:
SQL> update emp set sal = sal where ename = 'KING';
1 row updated.
SQL> update emp set sal = sal where ename = 'ALLEN';
update emp set sal = sal where ename = 'ALLEN'
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SQL>
Sessions B & C are still hanging since C is waiting for B and B is waiting for A.
SY.
|
|
|
|