Re: deadlocks between single update statements ?

<hasta_l3_at_hotmail.com> wrote in message news:1159004103.400694.132700_at_k70g2000cwa.googlegroups.com...
> Jonathan Lewis wrote :
>
>> <hasta_l3_at_hotmail.com> wrote in message
>> news:1159000862.384129.73710_at_i3g2000cwc.googlegroups.com...
>> > Dear group,
>> >
>> > Assume a transaction A with a single update statement updating many
>> > rows of a table.
>> > Assume another transaction B also with a single update, concurrently
>> > updating the same rows, but perhaps in a different order.
>> >
>> > Can these single updates deadlock ?
>> >
>> If the row updates are in exactly the same
>> order, the second transaction will wait for
>> the first transaction to commit or rollback.
>>
>> If the row updates are in a different order
>> you will get a deadlock.
>>
>
> Thanks, Jonathan.
>
> I would prefer to have the two statements update the row in the same
> order.
> But how do I impose an order in an update statement ?
>

You probably can't - in general.

Since correct ordering would result in a wait anyway, you could consider serializing the update through dbms_lock.

Create a named lock (dbms_lock.allocate_unique) for the activity, then make each process request the lock in exclusive mode before doing the update. Set the lock to commit on release.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html