Re: Effective deadlock handling

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 6 Jan 2004 18:02:15 -0600
Message-ID: <uvfnohbll.fsf@standardandpoors.com>

On Mon, 05 Jan 2004, damorgan_at_x.washington.edu wrote:
> Comments in-line.
>
> Galen Boyer wrote:
>

>> On Mon, 05 Jan 2004, damorgan_at_x.washington.edu wrote:
>>
>>>Galen Boyer wrote:
>>>
>>>
>>>>On Sun, 04 Jan 2004, damorgan_at_x.washington.edu wrote:
>>>>
>>>>
>>>>>I always code it with SKIP LOCKED and trap the PK of any
>>>>>missed records for later processing.
>>>>
>>>>So, the application can have logic producing deadlock
>>>>conditions but you put a little bit of dressing on the code
>>>>so the application doesn't experience the deadlock error.
>>>>
>>>
>>>One could say the same thing about EXCEPTION WHEN OTHERS too.
>> This analogy makes no sense.  Here we are catching unexpected
>> errors.  Purposefully ignoring deadlock issues is bad
>> programming.

>
> You and I seem to do a truly marvelous job of
> miscommunicating. Errors can happen ... we trap them and deal
> with them. Deadlocks are errors ... we trap them and deal with
> them.

But this isn't what you do. You stated that you always code with skip lock. Then you go back later and fix what erroneous results might have happened based on the fact that you skipped over locked rows. This is just bad practice because you now have allowed one section of a transaction to succeed without the other section to succeed.

As I said, this is akin to turning off RI and then fixing orphaned rows after the fact.

> A deadlock is no different to a PL/SQL program than an Oracle
> error like a ORA-0051 nor is it any different from a user
> defined exception such as RAISE Credit_Risk;

Yes it is much different. A deadlock should be akin to ORA-600. It is a bug in the application and should be fixed. In Oracle, you shouldn't code for deadlock detection (in SQLServer, you have to, cause it can happen at anytime). You should code so these don't happen. You should devise an exhaustive suite of tests to make sure deadlocks don't occur.

> Whether an error is expected or not expected is a
> point-of-view. I expect no errors because every line of code I
> write is perfect. I put EXCEPTION WHEN OTHERS in everything
> because I've found out my ego is far bigger than my ability to
> anticipate every possible error.

But if a deadlock happens, I don't give a hoot about your ego, this needs to be fixed, not stepped around.

>>>Just a matter of perspective. From my experience ... at least
>>>50% of good code is defensive in nature.
>> But, your analysis of how to handle deadlocks wasn't "fix the
>> logic of the application".  Your analysis of how to handle
>> deadlocks was to "SELECT FOR UPDATE SKIP LOCK" and then fix
>> any errors afterwards.  Thats akin to removing RI and then,
>> after processing is done, run a query which finds orphaned
>> children and fix them.  I guess one could consider it
>> defensive programming...

>
> Defensive coding is defensive coding. Haven't you ever coded
> for a database link? Didn't you write code to reconnect if the
> initial attempt failed? Haven't you ever written for RAC? Isn't
> TAF nothing but trapping errors and fixing the connection
> afterwards? That's what fail-over is by definition. What do you
> do in your code when someone tries to enter a value that
> conflicts with a referential constraint? Raise an exception
> and ....

The only thing I would say is that the end-user shouldn't get a deadlock error, but he should get the most severe error your application shows to users.

> Perhaps we are still miscommunicating here but almost all error
> trapping is fixing things afterwards: The alternative is to
> crash and burn.

I just fundamentally disagree that you code around deadlocks in Oracle.

-- 
Galen Boyer

Received on Tue Jan 06 2004 - 18:02:15 CST