Re: Effective deadlock handling

VC wrote:

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

You are correct as far as it goes but I suggested SKIP LOCKED

So try this instead:

Session 1:

SQL> select * from t1 where x=1

   2 for update skip locked;

          X

          1

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

1 row updated.

SQL> Session 2:

SQL> select * from t1 where x=2

   2 for update skip locked;

          X

          2

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

1 row updated.

SQL> select * from t1 where x=1

   2 for update skip locked;

no rows selected

SQL> Perhaps I have been far less clear than I should have been with my FOR UPDATE suggestion and for that I apologize. I always code it with SKIP LOCKED and trap the PK of any missed records for later processing.

Properly coded no deadlock is possible. At least not with the currently contrived examples. I've no doubt someone can come up with one and if you do ... I'll gladly use it at the U as part of the curriculum.

Once again my apologies. I no doubt wrote what I did without being explicit.

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