RE: Deadlock and ORA-0600 ocurred yesterday

If you wait for Oracle to untangle it, it is 60 seconds or so, correct. You don’t have to do that, but it is a strong feature that Oracle will untangle a deadlock.  

Doling out item reservations to customers one item at a time when the logical order they want is several items does not pass the laugh test. (At least not mine.)  

Making the non-zero likelihood of a deadlock a vanishingly small fraction of transactions unlikely to be repeated for a single customer instead of slow by slow processing is by my experience the winning formula.  

Some companies solve the conundrum later by telling the unlucky loser they didn’t really buy all the items they thought they did when the physical pick comes up short. THAT is definitely a great way to lose a customer.  

And that was the simplest real business case I could type in quickly.  

From: Mladen Gogala [mailto:gogala.mladen_at_gmail.com] Sent: Sunday, March 25, 2018 6:57 PM
To: Mark W. Farnham; 'Sayan Malakshinov' Cc: 'ORACLE-L'
Subject: 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 Received on Mon Mar 26 2018 - 01:13:53 CEST