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@xxxxxxxxx] ;
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
