Re: deadlock

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Wed, 27 Mar 2013 21:09:34 +0100
Message-ID: <CAJu8R6iDXPbBk2pkSA+pRXLnRyeU6r5NS_JWPJx8u26wJxucXg_at_mail.gmail.com>

When a deadlock occurs Oracle (particularly the PMON process I think) will, contrary to what your developer thinks, *not kill the session* but it will only cancel* one statement of the first session that started waiting*. The session that receives the ORA-0060 deadlock error has to *explicitly rollback* its transaction otherwise the other session will still be locked. You have several good blog articles like those of Jonathan Lewis, and the work done by Marc Bobak in that field. I have also gathered (for me) few information here below about the common deadlock situations

http://hourim.wordpress.com/category/deadlock/

Your developer should also know that if his application is experiencing deadlock then this is a clear symptom of a wrong design somewhere in this application

Best regards
Mohamed Houri

2013/3/27 Ryan January <rjjanuary_at_multiservice.com>

> This is aptly timed as I was also about to submit a question regarding
> deadlocks when using "select for update".
> Could someone verify the order in which rows are locked in this situation?
> Correct me if I'm wrong, but my understanding is that the lock order is
> ultimately determined by the order data blocks are returned satisfying
> the query. Unless table block access for this query can be forced (such
> as using an ordered index) there remains the potential for deadlocks.
>
> If my current understanding of row lock order holds true, restructuring
> the data within the table can have an impact on the possibility of
> deadlocks. Table restructuring may mask your issue temporarily, but it
> can't fix the faulty logic.
>
> Getting away from your question for a moment; What does the response
> profile of the traced session show you?
> The way I see it you have two issues... poor performance, and
> deadlocks. They're very likely related, but until you see where your
> time is being consumed it's only an assumption.
>
> If your time is consumed with contention on this particular object
> you've likely found your culprit. If it's not, the developer may still
> be /partially/ correct. After compiling your data from the trace you
> have irrefutable proof you can present the developer.
>
> Thanks,
> Ryan
>
> On 03/27/2013 01:43 PM, Barbara Baker wrote:
> > I do not want to explain how deadlock works/happens but that is not the
> > cause for slow response. If you get a deadlock situation between two
> > sessions, Oracle automatically terminates one of the sessions immediately
> > and allows other session to continue. Deadlock does not slow down the
> > database response but it does terminates one of the sessions involved in
> > the deadlock.
> > He's trying to talk me into exporting the entire
> > database and then re-importing it.
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 27 2013 - 21:09:34 CET