RE: Deadlock and ORA-0600 ocurred yesterday

Deadlocks can be the side effect of a challenging business case, such as processing a multi-row customer order against inventory reservations times n in flow that would cause too much queuing delay to serialize by order due to the size of n and the time to optimally process one order.  

There are ways to minimize the concurrency footprint of such transactions, but even if you order the tables and order the rows within tables in the updates the possibility of a deadlock remains, whilst the alternative of going out of business because customers won’t wait and some items are too pricey to allocate enough float in inventory to secure the items later.  

Consider if you want items A, B, and Z and I want items B, C and Z. I may well get B after you get A and before you get B.  

If memory serves there is quite an archive on this on oracle-l featuring the full picture of choices and tradeoffs. I believe several, including Mark Bobak and Graham Wood, contributed.  

I’ve published a paper on using stored PL/SQL packages to minimize the concurrency footprint of logical units of work some years ago that was pretty well received.  

On the other hand, I’ve also seen non-challenging applications written that seem almost to have been designed to cause a deadlock. Deadlocks CAN be a symptom of bad application design or they can be a symptom of no winner in an inevitable race condition driven by the application requirements. At least Oracle unwinds them in a reasonable and predictable way.  

Having the tools at hand to figure out which is the case and getting guided to them is the beauty of a list like this.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Sunday, March 25, 2018 1:29 PM
To: Sayan Malakshinov
Cc: ORACLE-L
Subject: Re: Deadlock and ORA-0600 ocurred yesterday  

Well, I would call an application which would run multiple simultaneous batch updates of the same data set a bad design. That is precisely what I've been saying. Deadlocks are a symptom of bad application design.  

On 03/25/2018 02:37 AM, Sayan Malakshinov wrote:    

I thought it's obvious that when I say about sorted updates(order by) I mean updating multiple rows...

--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--

http://www.freelists.org/webpage/oracle-l Received on Sun Mar 25 2018 - 23:55:14 CEST