Re: Deadlock and ORA-0600 ocurred yesterday

Hi Mark,

Replies in line:

On 03/25/2018 05:55 PM, Mark W. Farnham wrote:
>
> 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.
>

Yes, there are legitimate business requirements which can cause deadlocks. However, the delay caused by deadlocks is 60 seconds, an eternity in terms of web ordering. The question is whether reorganizing the application and using queues is really more expensive than risking deadlocks.  In the case mentioned, a multi-item order can be internally broken into several single item orders, which would avoid deadlocks. Deadlocks cause quite serious delay.

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

Yes, that is why internally breaking the orders into single item orders is a good idea. The problem arises if there is not enough items in stock, but if that is the case it is not possible to fulfill all the orders anyway.

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

Yes, I remember that. Interesting stuff.

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

If there are frequent deadlocks in an application, I tend to consider the developer guilty until she or he proves her or his innocence. Most of the cases of deadlock that I've encountered during my DBA career could have been avoided by the application design.

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

And here we are in agreement once more.

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


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