Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Troubleshoot Deadlock

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
ajaybelde
Asked:
ajaybelde
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>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
 
ajaybeldeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ajaybeldeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>Ok..After looking

Is there a question in there somewhere?
Do you need additional assistance with this question?
0
 
ajaybeldeAuthor Commented:
I got enough information..

Thanks for your Help!
0
 
slightwv (䄆 Netminder) Commented:
Don't forget to close the question.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now