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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
