Re: deadlocks between single update statements ?

<hasta_l3_at_hotmail.com> wrote in message news:1159209270.548494.266030_at_e3g2000cwe.googlegroups.com...
> DA Morgan wrote :
>
>> My error. I meant to suggest SELECT FOR UPDATE NOWAIT.
>
> Why NOWAIT, BTW ?
>
> A transaction with
> - a select for update correctly ordered
> - followed by an update of the same rows
>
> should do the trick, isn't it ?
>

Lots of implementation issues to address:

select for update with or without wait:

    Generates undo and redo.

    You have to select the rowids or PKs

    Then you have to update one row at a time, because     if you issue an update with the same where clause as     the select for update, some new rows may be come     into scope - which takes you back to the previous problem.

Select for update without wait still leaves you exposed to the deadlock problem.

    session 1 gets row A
    session 2 gets row B
    session 1 requests row A and waits
    session 2 requests row B and hangs
    session 1 detects deadlock and rolls back select statement.

Select for update nowait STILL leaves you exposed to the deadlock problem - though the time window reduces:

    session 1 gets 20 rows for update, including row A     select 2 gets 20 rows for update, including row B

    session 1 hits row B as the next row to lock, and fails     and starts to rollback the select (not the transaction)

    session 2 hits row A as the next row to lock, and fails     and starts to rollback the select (not the transaction).

    Now what ? If you get the sessions to try again the     same collision can occur.

If you want to avoid serializing through dbms_lock, you just have to code to do something sensible on deadlock and take the (occasional?) hit.

-- 
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