Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Effective deadlock handling

Re: Effective deadlock handling

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 05 Jan 2004 08:20:19 -0800
Message-ID: <1073319533.384450@yasure>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US