Re: Effective deadlock handling

In message <K54Kb.735379$HS4.5605072_at_attbi_s01>, VC <boston103_at_hotmail.com> writes
>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;
>
> X
>----------
> 1
>
>SQL>
>
>Session 2:
>---------
>SQL> select * from t1 where x=2 for update;
>
> X
>----------
> 2
>
>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
>
>

I understand the above, but the point I was trying to a was that if both sessions do the following at the same time:

Session 1: SELECT * from t1 where x = 1 or x = 2 for update Session 2: SELECT * from t1 where x = 1 or x = 2 for update

then, to my mind deadlock should not occur since one of these will get to the first row first, blocking the other, and then succeeding in the next row too.

That is what I find puzzling.

Kind regards,

Saeed

sr_ng 786 Received on Mon Jan 05 2004 - 08:48:06 CST