Re: deadlock
Date: Wed, 27 Mar 2013 14:54:32 -0500
Message-ID: <51534E78.3000809_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-lReceived on Wed Mar 27 2013 - 20:54:32 CET