Re: Effective deadlock handling

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
>>

Daniel, this statement makes it perfectly clear that you didn't read the OP's post. My example was a sqlplus example of exactly what his step 1 of code would produce.

> 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.

Hm... Are you sure?

Session 1:

    ORA> select id, fld from deadlock where id = 1 for update;

            ID F

• - 1 A

Session 2:

    ORA> select id, fld from deadlock where id = 2 for update;

            ID F

• - 2 B

Session 1:

    ORA> select id, fld from deadlock where id = 2 for update;

Hm... The above waits.

Session 2:

    ORA> select id, fld from deadlock where id = 1 for update;

Deadlock.

> 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.

From his post, I am ready to believe he understands what a deadlock is, but maybe on his subsequent attempts, he truly didn't get a deadlock. Then, well, okay. It just looks like he knows what a deadlock is and how he would identify it.

Hey Saeed, on your subsequent attempts, how do you know you got deadlocks?

-- 
Galen Boyer