Re: Effective deadlock handling

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 3 Jan 2004 08:37:17 -0600
Message-ID: <uoetldrt2.fsf@standardandpoors.com>

On Sat, 03 Jan 2004, damorgan_at_x.washington.edu wrote:
> Galen Boyer wrote:
>

>> On Fri, 02 Jan 2004, anacedent_at_hotmail.com wrote:
>>
>>>With Oracle, readers do NOT block writers & writers do NOT
>>>block readers. Please provide specific & actual evidence of a
>>>deadlock within your Oracle instance.
>> In any old sqlplus session execute the following;
>>     ORA> create table deadlock (id number, fld char(1));
>>     ORA> insert into deadlock values (1,'A');
>>     ORA> insert into deadlock values (2,'B');
>>     ORA> commit;
>> Now, open up another sqlplus session
>> In Session1, issue the following:
>>     ORA> update deadlock set fld = 'M' where id = 1;
>> Next, In Session2, issue the following;
>>     ORA> update deadlock set fld = 'N' where id = 2;
>> Next, in Session1, issue the following;
>>     ORA> update deadlock set fld = 'X' where id = 2;
>> Hm... Notice a waiting session?
>> Next, in Session2, issue the following;
>>     ORA> update deadlock set fld = 'Y' where id = 1;
>> Hm... Does one of those sessions now show the following error?
>> ERROR at line 1:
>> ORA-00060: deadlock detected while waiting for resource
>>

>
> But of course. Anyone can intentionally create a deadlock.
>
> But you weren't paying attention to the OP's code. The OP had
> SELECT FOR UPDATE.
Oh, fur christs sake Daniel. I didn't pay attention to his code?

> And if you use SELECT FOR UPDATE no deadlock can occur. Not
> even with your contrived example.
>
> Given what the OP posted I don't believe any deadlock
> existed. Though I stand ready to be proven incorrect if he
> responds to our request that he explain what it is he thinks
> demonstrates that a deadlock has taken place.

Daniel, that was his last step tried. The first iteration would most definitely have produced a deadlock, and he explained it in a manner that was clear he understood that. I clearly said his results confused me as well.

-- 
Galen Boyer

Received on Sat Jan 03 2004 - 08:37:17 CST