Re: Effective deadlock handling

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 03 Jan 2004 00:25:01 -0800
Message-ID: <1073118217.32288@yasure>

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. 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 Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Jan 03 2004 - 02:25:01 CST