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 KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
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.

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

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