Saeed wrote:
> 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
The records stay in the "deadlocked" state until the transaction is
completed by either a commit or rollback. So the problem is also mostly
solvable by performing incremental commits.
--
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 Mon Jan 05 2004 - 10:20:19 CST
