Re: Effective deadlock handling

Daniel,

Yes, you are quite right regarding the 'SKIP LOCKED' modifier.

However, the SKIP LOCKED has several potential issues:

1. it's undocumented;
2. it's not supported by Oracle for anything besides AQ;
3. it was designed to be used for Advanced Queueing only;

Rgds.

VC

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1073271246.697564_at_yasure...
> 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)
>
Received on Sun Jan 04 2004 - 22:22:15 CST