Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00060: deadlock detected while waiting for resource (ORACLE 11g)
ORA-00060: deadlock detected while waiting for resource [message #616951] Mon, 23 June 2014 10:49 Go to next message
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 Go to previous messageGo to next message
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 #616954 is a reply to message #616953] Mon, 23 June 2014 10:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How can i identify dead locks, is there any query will tell waiting/lock on tables caused dead lock issues.

the alert log file should contain clues about both SQL involved with the deadlock
Re: ORA-00060: deadlock detected while waiting for resource [message #616955 is a reply to message #616953] Mon, 23 June 2014 11:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Also, did you get a chance to replicate the deadlock issue in your application? If not, then follow these basic steps to replicate it http://lalitkumarb.wordpress.com/2014/02/25/oracle-deadlock-replication/
Re: ORA-00060: deadlock detected while waiting for resource [message #616957 is a reply to message #616954] Mon, 23 June 2014 11:12 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Would it be DB or application issue? it wasn't doing updates quickly, when multiple users are working.

I was using Load runner to test functionality where in mutilple users will execute same transactions.
Re: ORA-00060: deadlock detected while waiting for resource [message #616959 is a reply to message #616957] Mon, 23 June 2014 11:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gorants wrote on Mon, 23 June 2014 09:12
Would it be DB or application issue? it wasn't doing updates quickly, when multiple users are working.

I was using Load runner to test functionality where in mutilple users will execute same transactions.


which part of below do you NOT understand?

Oracle is the victim and your application design is the issue.
Re: ORA-00060: deadlock detected while waiting for resource [message #616960 is a reply to message #616959] Mon, 23 June 2014 11:22 Go to previous messageGo to next message
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 #616961 is a reply to message #616959] Mon, 23 June 2014 11:24 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
My understanding is applcation issue.

Let say session 1 doing update at the same time session 2 working on the same record then deadlock issue happned ? correct me if i am wrong.

in this case i have to do lock in my application code to prevent this.
Re: ORA-00060: deadlock detected while waiting for resource [message #616962 is a reply to message #616960] Mon, 23 June 2014 11:27 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Yes Lalit

it is application load test with virtual users.
Re: ORA-00060: deadlock detected while waiting for resource [message #616963 is a reply to message #616962] Mon, 23 June 2014 11:33 Go to previous messageGo to next message
cookiemonster
Messages: 13920
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
gorants wrote on Mon, 23 June 2014 21:57
Yes 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 #616965 is a reply to message #616964] Mon, 23 June 2014 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Let say session 1 doing update at the same time session 2 working on the same record then deadlock issue happned ? correct me if i am wrong.
You are wrong!

You should actually implement the exact scenario you describe above & post the results back here; which will not be an ORA-00060 error
Re: ORA-00060: deadlock detected while waiting for resource [message #616966 is a reply to message #616961] Mon, 23 June 2014 12:06 Go to previous messageGo to next message
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 #616967 is a reply to message #616963] Mon, 23 June 2014 12:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
cookiemonster wrote on Mon, 23 June 2014 22:03
Row 1 and 2 can be in different tables.


Not necessarily. They could be in the same table as well.

Edit : Proper quote tag.

[Updated on: Mon, 23 June 2014 12:23]

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616968 is a reply to message #616967] Mon, 23 June 2014 12:55 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thank you all for your responses.

I dont have access to trace log file. those are on DB servers. i will raise DBA request to get trace log file.

Re: ORA-00060: deadlock detected while waiting for resource [message #616969 is a reply to message #616968] Mon, 23 June 2014 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> i will raise DBA request to get trace log file.

You don't need the whole file; just the excerpt which contain the ORA-00060 error and any associated trace files.
Re: ORA-00060: deadlock detected while waiting for resource [message #616970 is a reply to message #616969] Mon, 23 June 2014 13:07 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Ok sure, i will get olny those from trace log.


Below are the application errors i see in log files.

Pid=1290 Tid=1405425984 06/23/2014 06:54:50.792 - Exception for cursor TWSE_864_519518
DBError exception occurred in ORA11GDynamicSql::openCursor(StmtExecute)
   ORA-00060: deadlock detected while waiting for resource

sql string
SELECT  CONSOL_LOCN.DIRCT_VOL, CONSOL_LOCN.DIRCT_UOM_QTY FROM CONSOL_LOCN WHERE ( CONSOL_LOCN.PKT_CONSOL_LOCN_ID = :1 ) FOR UPDATE WAIT 120
input variables
  1: Address(0x2aaac055b7c0) Length(0) Type(2) "5491" - Indicator @ 0x2aaac0007618 = 0

  
Pid=1290 Tid=1085401408 06/23/2014 07:32:10.708 - lock detected (SQL aborted)
dbSqlString = SELECT  CONSOL_LOCN.DIRCT_VOL, CONSOL_LOCN.DIRCT_UOM_QTY FROM CONSOL_LOCN WHERE ( CONSOL_LOCN.PKT_CONSOL_LOCN_ID = :1 ) FOR UPDATE WAIT 120
connectionName = TWSE_2109
Re: ORA-00060: deadlock detected while waiting for resource [message #616972 is a reply to message #616970] Mon, 23 June 2014 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
are there any BITMAP INDEX on table CONSOL_LOCN?
Re: ORA-00060: deadlock detected while waiting for resource [message #616976 is a reply to message #616967] Mon, 23 June 2014 13:56 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Mon, 23 June 2014 18:09
cookiemonster wrote on Mon, 23 June 2014 22:03
Row 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 #616977 is a reply to message #616972] Mon, 23 June 2014 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from following SQL
SELECT ai.index_name, 
       aic.column_name, 
       ai.index_type 
FROM   all_indexes ai, 
       all_ind_columns aic 
WHERE  ai.table_name = 'CONSOL_LOCN' 
       AND ai.index_name = aic.index_name 
       AND ai.table_name = aic.table_name 
       AND ai.owner = aic.index_owner 
Re: ORA-00060: deadlock detected while waiting for resource [message #616978 is a reply to message #616976] Mon, 23 June 2014 14:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
cookiemonster wrote on Tue, 24 June 2014 00:26

What do you think the word "can" means?
It doesn't mean the same thing as "must" does it?


Ok. I keep "could" and "can" for different usages. Sorry, but that's what I was told. Don't mind please.
Re: ORA-00060: deadlock detected while waiting for resource [message #616980 is a reply to message #616977] Mon, 23 June 2014 15:05 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
BS,

I see 1000 entries returned, all of the index_type are NORMAL

Thanks
Re: ORA-00060: deadlock detected while waiting for resource [message #616981 is a reply to message #616980] Mon, 23 June 2014 15:47 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
So sounds like it's a standard deadlock problem as I described above. You need the DB trace file from the server.
Re: ORA-00060: deadlock detected while waiting for resource [message #617064 is a reply to message #616981] Tue, 24 June 2014 09:33 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thank you guys for all your suggestions.

Yes from trace log we were able to identify reasons for dead lock.
Re: ORA-00060: deadlock detected while waiting for resource [message #617066 is a reply to message #617064] Tue, 24 June 2014 09:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
And whats tjat you found in trace log? Can you please tell us more about it. Like how you analyzed and fixed the issue. Curiousity you know.
Re: ORA-00060: deadlock detected while waiting for resource [message #617067 is a reply to message #617064] Tue, 24 June 2014 09:44 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And the reason was ...?
Re: ORA-00060: deadlock detected while waiting for resource [message #617078 is a reply to message #617067] Tue, 24 June 2014 14:10 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member

Reason is

session-1 is requesting for executing Update statement on CONSOL_LOCN (table) for rows that held by session-2.

session-2 is requesting for executing update statement on HDR (table) for rows that held by session-1 .
Re: ORA-00060: deadlock detected while waiting for resource [message #617079 is a reply to message #617078] Tue, 24 June 2014 14:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
But they are two different sessions and two different tables altogether. Shouldn't get into a deadlock scenario.
Re: ORA-00060: deadlock detected while waiting for resource [message #617083 is a reply to message #617079] Tue, 24 June 2014 14:51 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Let me put in clear way

session-1 locks row 1 in CONSOL_LOCN (table), session-2 locks row 2 in CONSOL_LOCN (table), session-1 tries to lock row 2 CONSOL_LOCN (table) and hangs, then session-2 tries to lock row 1 in CONSOL_LOCN (table)

same is the case with HDR table.
Re: ORA-00060: deadlock detected while waiting for resource [message #617096 is a reply to message #617079] Tue, 24 June 2014 18:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Lalit Kumar B wrote on Tue, 24 June 2014 15:23
But 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 #617097 is a reply to message #617096] Tue, 24 June 2014 19:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
And deadlock can be caused by a chain of multiple (more than 2) sessions. For example:

session A locks resource R1
session B locks resource R2
session C locks resource R3
session A requests lock on resource R3
session B requests lock on resource R1
session C requests lock on resource R2 ==> deadlock (victim can be any of three sessions A, B or C).

SY.

[Updated on: Tue, 24 June 2014 19:12]

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617100 is a reply to message #617097] Tue, 24 June 2014 23:42 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Solomon Yakobson wrote on Wed, 25 June 2014 10:08
... deadlock (victim can be any of three sessions A, B or C).


http://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1336

Oracle Concepts manual says
The statement rolled back belongs to the transaction that detects the deadlock.


Which should be session C, shouldn't it?

Ross Leishman
Re: ORA-00060: deadlock detected while waiting for resource [message #617125 is a reply to message #617100] Wed, 25 June 2014 05:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
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.
Re: ORA-00060: deadlock detected while waiting for resource [message #617201 is a reply to message #617125] Wed, 25 June 2014 22:31 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Nice
Previous Topic: utl_http how to close the request?
Next Topic: Oracle function/package same as SAS spedis function
Goto Forum:
  


Current Time: Thu Apr 25 04:39:37 CDT 2024