Solved

Troubleshoot Deadlock

Posted on 2012-04-10
7
579 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
  • 4
  • 3
7 Comments
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now