Re: deadlocks between single update statements ?

DA Morgan wrote:
> 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.
> >
> >> How do you prevent this ?
> >>
> >
> > a) Serialize the two updates
> > b) Program for deadlock detection so that
> > you can rollback and retry.
> >
> >> Thanks much.

>

Because

1. this implies PL/SQL or host program loop processing, while single UPDATE is desired; and
2. this may skip rows you actually want to update, which may be undesired?

Besides, SKIP LOCKED is an undocumented extension to SELECT FOR UPDATE and as such is not guaranteed to be present in upcoming Oracle releases.

Given the requirements, however vague, the OP presented, it looks like the two solutions Jonathan suggested are the only working ones. You have to either serialize these updates to prevent deadlocks, or be ready for them and program accordingly (and be ready to waste a few seconds before Oracle actually triggers ORA-60 and provision space in user_dump_dest for associated trace files...) Another possible solution would be to use an ordered FOR UPDATE cursor, but it again implies loop processing.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Sep 25 2006 - 02:36:53 CDT