Deadlock_Issue

Hi Expert,

In my project Multiple Oracle jobs are running through a production batch scheduling(Control -M) tool .

I have faced multiple times Dead Lock issue . Is there any feature in oracle which we can handle for overcome the issue .

Please Suggest your suggestion is highly appreciable .

thanks In Advance
Mihir
MIHIR KAR#Oracle_DB #UNIX beginnerAsked:
Who is Participating?
 
Geert GOracle dbaCommented:
some devs have no idea of the power they have ... I mean : power of failure and causing havoc

you can identify the statements causing the deadlock via the alertlog of the database
on the database host, open the alertlog and search for deadlock
there should be an entry for each deadlock pointing to a trace file with more info

open each trace file, list the deadlock graph and the statements
that's usually from the beginning of the trace file until "PROCESS STATE"

explain to the devs, or the vendor, that their app is causing this deadlock
with the statements, they should be able to solve it.

where to find the oracle alert_log is explained here:
http://www.orafaq.com/wiki/Alert_log
1
 
sdstuberCommented:
Deadlocks indicate an application design flaw.

It's not a problem with Oracle, it's a problem with the way your application acquires  and uses locks.

To fix it, either serialize access (i.e. only one process runs at a time) or alter the application so two different sessions don't attempt to lock the same rows simultaneously.
1
 
Mark GeerlingsDatabase AdministratorCommented:
I basically agree with sdstuber, but I suggest that his description is not quite complete.  A deadlock happens when "two different sessions [or programs] ... attempt to lock the same rows [from at least two different tables] simultaneously [but in the opposite order]."

For example, session 1 starts modifying or deleting a row or rows in table "A", then tries to modify or delete rows in table "B".  But at the same time, session 2 started by modifying or deleting rows in table "B", then tries to modify or delete the same row(s) from table "A" that session 1 had already locked.  Oracle recognizes this as a deadlock condition and will kill one of the two sessions, so the other session can proceed.  Note that inserting new records will not cause a deadlock, unless the inserts are in tables that have triggers that involve other tables.

You can't fix or prevent this in the database.  This is an application problem.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.