OraFAQ Forum: SQL & PL/SQL » ORA-00060: deadlock detected while waiting for resource

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00060: deadlock detected while waiting for resource (ORACLE 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Code: [Select all] [Show/ hide]

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,

Report message to a moderator

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616954 is a reply to message #616953]

Mon, 23 June 2014 10:59

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

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616955 is a reply to message #616953]

Mon, 23 June 2014 11:00

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/

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616957 is a reply to message #616954]

Mon, 23 June 2014 11:12

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616959 is a reply to message #616957]

Mon, 23 June 2014 11:16

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.

Report message to a moderator

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616961 is a reply to message #616959]

Mon, 23 June 2014 11:24

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616962 is a reply to message #616960]

Mon, 23 June 2014 11:27

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Yes Lalit

it is application load test with virtual users.

Report message to a moderator

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.

Report message to a moderator

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: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!

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616965 is a reply to message #616964]

Mon, 23 June 2014 12:00

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

Report message to a moderator

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616967 is a reply to message #616963]

Mon, 23 June 2014 12:09

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

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616969 is a reply to message #616968]

Mon, 23 June 2014 13:02

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616970 is a reply to message #616969]

Mon, 23 June 2014 13:07

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616972 is a reply to message #616970]

Mon, 23 June 2014 13:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

are there any BITMAP INDEX on table CONSOL_LOCN?

Report message to a moderator

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: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?

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616977 is a reply to message #616972]

Mon, 23 June 2014 14:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from following SQL

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616978 is a reply to message #616976]

Mon, 23 June 2014 14:03

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616980 is a reply to message #616977]

Mon, 23 June 2014 15:05

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

BS,

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

Thanks

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #616981 is a reply to message #616980]

Mon, 23 June 2014 15:47

cookiemonster
Messages: 13967
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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617064 is a reply to message #616981]

Tue, 24 June 2014 09:33

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617066 is a reply to message #617064]

Tue, 24 June 2014 09:43

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617067 is a reply to message #617064]

Tue, 24 June 2014 09:44

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

And the reason was ...?

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617078 is a reply to message #617067]

Tue, 24 June 2014 14:10

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 .

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617079 is a reply to message #617078]

Tue, 24 June 2014 14:23

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617083 is a reply to message #617079]

Tue, 24 June 2014 14:51

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.

Report message to a moderator

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

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617097 is a reply to message #617096]

Tue, 24 June 2014 19:08

Solomon Yakobson
Messages: 3305
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

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

Report message to a moderator

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:

Code: [Select all] [Show/ hide]

SQL> update emp set sal = sal where ename = 'KING';

1 row updated.

Session B:

Code: [Select all] [Show/ hide]

SQL> update emp set sal = sal where ename = 'JONES';

1 row updated.

Session C:

Code: [Select all] [Show/ hide]

SQL> update emp set sal = sal where ename = 'ALLEN';

1 row updated.

Now session A:

Code: [Select all] [Show/ hide]

SQL> update emp set sal = sal where ename = 'ALLEN';

And hangs waiting for session C to release ALLEN.

Now session B:

Code: [Select all] [Show/ hide]

SQL> update emp set sal = sal where ename = 'KING';

And hangs waiting for session A to release KING.

Now session C causes a deadlock:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: ORA-00060: deadlock detected while waiting for resource [message #617201 is a reply to message #617125]

Wed, 25 June 2014 22:31

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Nice

Report message to a moderator

Previous Topic:	utl_http how to close the request?
Next Topic:	Oracle function/package same as SAS spedis function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 11:41:14 CDT 2025