Solved

Troubleshoot Deadlock

Posted on 2012-04-10
7
589 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 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
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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
use lov values 2 64
Oracle encryption 12 48
DB migration from Mssql to 12c oracle , data not loading. 3 39
plsql job on oracle 18 41
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 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