Deadlock_Issue

MIHIR KAR
MIHIR KAR used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.
Mark GeerlingsDatabase Administrator
Commented:
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.
Oracle dba
Top Expert 2009
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial