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

===================================================
ajaybeldeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

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

Thanks for your Help!
slightwv (䄆 Netminder) Commented:
Don't forget to close the question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.