Re: Effective deadlock handling

From: VC <boston103_at_hotmail.com>
Date: Mon, 05 Jan 2004 02:37:31 GMT
Message-ID: <K54Kb.735379$HS4.5605072@attbi_s01>

Hello Daniel,

See below...

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1073194956.741806_at_yasure...
> Galen Boyer wrote:
> > On Sat, 03 Jan 2004, damorgan_at_x.washington.edu wrote:
> >
> >>Galen Boyer wrote:
> >>
> >>
> >>>On Sat, 03 Jan 2004, damorgan_at_x.washington.edu wrote:
> >>>
> >>>
> >>>>Galen Boyer wrote:
> >>>>
> >>>>
> >>>>>Thanks for ignoring everything else.
> >>>>
> >>>>I agreed with you. I thought that was obvious from my
> >>>>statement "Exactly".
> >>>
> >>>Okay. You just said that I wouldn't be able to produce a
> >>>deadlock using SELECT FOR UPDATE, even with my "contrived"
> >>>example. You didn't seem to acknowledge that you were wrong.
> >>>
> >>
> >>As I understand it the wait with a SELECT FOR UPDATE is not a
> >>deadlock but rather an intentional wait. With a slight change
> >>in syntax ... SKIP ... that doesn't happen.
> >>
> >>To my way of thinking this is not a deadlock as it generates no
> >>error condition.
> >>
> >>Did I misunderstand you?
> >
> >
> > Try it.

>
> My ISP no longer has your code available. Please send it. Thanks.

Please see the Concepts regarding Data Concurrency and Consistency (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsi s.htm#3138). As soon as you realize that with respect to row locks SELECT FOR UPDATE is no different from an ordinary update/delete, there will be no need to supply a separate test case to illustrate possible dead-locking for concurrent SFUs.

Just for fun, here's one anyway:

Concurrent UPDATES:

drop table t1;
create table t1(x int);

insert into t1 values(1);
insert into t1 values(2);
commit;

Session 1:

SQL> update t1 set x=-x where x=1;

1 row updated.

Session 2:

SQL> update t1 set x=-x where x=2;

1 row updated.

SQL> update t1 set x=-x where x=1;
update t1 set x=-x where x=1
--- Here, Session 2 is blocked by Session 1's update

Session 1:

SQL> update t1 set x=-x where x=2;
-- Here, Session 1 is blocked by Session 2's update

And in Session 2 we see this message:
update t1 set x=-x where x=1

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Concurrent SELECTs FOR UPDATE:

drop table t1;
create table t1(x int);

insert into t1 values(1);
insert into t1 values(2);
commit;

Session 1:

SQL> select * from t1 where x=1 for update;

SQL> Session 2:

SQL> select * from t1 where x=2 for update;

SQL> select * from t1 where x=1 for update; --- Here, Session 2 is blocked by Session 1's SFU

Session 1:

SQL> select * from t1 where x=2 for update; -- Here, Session 1 is blocked by Session 2's SFU

And in Session 2 we see this message:
select * from t1 where x=1 for update

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

As you can see, there are no updates or deletes, just selects for update, and yet a dead-lock happens. However, it should not be surprising for anyone who's read. at least once, the afore-mentioned Oracle "Concepts".

Rgds.

VC Received on Sun Jan 04 2004 - 20:37:31 CST