Re: deadlocks between single update statements ?

Jonathan Lewis wrote :

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

Yes, I had forgotten phantom reads :-(

I guess one could think of the serializable transaction isolation level.
But we never used it, and it may have its own issues ?

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

I'm afraid I am not following you here. If the two selects are ordered according to the same canonical order, they cannot deadlock, isn't-it ?

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

Yep. But in our case, handling the deadlock requires pretty widespread changes in the code base.

At this point I think my interest is becoming academic.

Right now we are updating one row at a time, each in its own transaction. I raised the question because a single update of many rows is eight times faster.

But perhaps it is nevertheless wiser to keep things the way they are ? Received on Tue Sep 26 2006 - 00:44:15 CDT