Solved

Troubleshoot Deadlock

Posted on 2012-04-10
7
590 Views
Last Modified: 2012-06-21
Hi,

I am working 10.2.0.4 Warehouse DBs & so many applications are using those DBs. I got Deadlock error(ORA-00060) alert & tracefile says as follows.

Can you guys help me to find cause for  this error?


DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TM-000033c7-00000000        29     598         SSX       24     498          SS

TM-000033c7-00000000        24     498    SX             29     598         SSX

session 598: DID 0001-001D-000744FC     session 498: DID 0001-0018-0005899D

session 498: DID 0001-0018-0005899D     session 598: DID 0001-001D-000744FC

Rows waited on:

Session 498: no row

Session 598: no row

Information on the OTHER waiting sessions:

Session 498:

  pid=24 serial=6394 audsid=2985638 user: 32/CVNCODS

  O/S info: user: SVC_BusObj, term: INDAPP84, ospid: 4224:332, machine: ENT\INDAPP84

            program: al_engine.exe

  application name: al_engine.exe, hash value=4255119177

  Current SQL Statement:

  DELETE FROM PD_INVESTIGATOR_SITE_ROLE WHERE INVESTIGATOR_SITE_FK = 161176

End of information on OTHER waiting sessions.

Current SQL statement for this session:

DELETE FROM RF_PARTICIPANT WHERE Natural_Key = '513794|R308898|HMT18'

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

c0000002bc2d1fc8        88  procedure CVNCODS.PRC_CASCADE_DELETE

c0000002bc2d2a18         1  anonymous block

c0000002bc258a28       508  package body CVNCSTG.PKG_ZC_FILE

c0000002bcefe370         1  anonymous block

===================================================
0
Comment
Question by:ajaybelde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829444
>>Can you guys help me to find cause for  this error?

Two sessions were issuing those delete statements and neither one could complete until the other committed.  Thus the deadlock.

Check the two tables and see if they have cascading deletes.
0
 

Author Comment

by:ajaybelde
ID: 37829521
PD_INVESTIGATOR_SITE_ROLE  table ddl has
cvncods.prc_trg_cascade_delete('PD_INVESTIGATOR_SITE_ROLE',:OLD.INVESTIGATOR_SITE_ROLE_PK);

'RF_PARTICIPANT' table ddl has the following
cvncods.prc_trg_cascade_delete('RF_PARTICIPANT',:OLD.PARTICIPANT_PK);


Are they causing this error?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37829556
Are you not understanding what a deadlock is?

That looks like part of a trigger.  Take a look at what the triggers are doing and see if they might step on each other.

I have no way of knowing for sure if these are causing the deadlock or not.  The error message gave you the two commands that caused it.  You now just need to investigate those commands.

You should know your system.  We really have no way of knowing enough about it to debug the exact cause of the deadlock.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:ajaybelde
ID: 37829630
Ok..After looking at the both tables ddls..1st table has a foreignkey constariant which references PK in 2nd table causing cascading delete & when 2nd delete issues deadlocks are occuring.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829648
>>Ok..After looking

Is there a question in there somewhere?
Do you need additional assistance with this question?
0
 

Author Comment

by:ajaybelde
ID: 37829668
I got enough information..

Thanks for your Help!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829676
Don't forget to close the question.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question