Re: Effective deadlock handling

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

-- 
Galen Boyer