Re: Effective deadlock handling

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 04 Jan 2004 09:40:27 -0800
Message-ID: <1073237941.526203@yasure>

Saeed wrote:

> In message <1073168436.842183_at_yasure>, Daniel Morgan
> <damorgan_at_x.washington.edu> writes
>

>> But I am still hesitant about your statement:
>> "If this was just a single UPDATE transaction, there would be no 
>> problem, but there are 2 UPDATEs, and other sessions may require the 2 
>> UPDATEs to operate on rows in reverse order to some other sessions, 
>> the chances of deadlock occurring are very real."
>>
>> Have you tried the following UPDATE syntax to see if you can perform 
>> the update in a single statement?
>>
>> UPDATE (<SELECT Statement>)
>> SET ....;
>>

>
> No I haven't tried that, more out laziness than anything else. If I get
> time (system testing is starting this week) I'll give it a go and post
> an update here to let you know the results.
>
> Kind regards,
>
> Saeed
>
> sr_ng 786

Consider this ... you can create a global temporary table that holds the primary key of the table you are working with.

Use SELECT FOR UPDATE and if you can't obtain the lock then copy the PK to the temp table. Then after all processing is done go back and handle the few records in the temp table. You should have no blocking problems and a very small set of records that require rework.

-- 
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 - 11:40:27 CST