Thread: deadlock avoidance

I noticed the following in some of our code today:
    select ... <join list> ... for update of a, b;

Inasmuch as the cardinal rule for avoiding deadlocks is to acquire
locks in a consistent order, should such a construction be avoided
in favor of two separate "select ... for update" statements so that
the order of acquisition of a and b is known? I'm assuming that
there is no ordering implied/guaranteed by "for update of a, b".

Or am I missing something?

Clarence

Clarence Gardner <clarence@silcom.com> writes:
> I noticed the following in some of our code today:
>     select ... <join list> ... for update of a, b;

> Inasmuch as the cardinal rule for avoiding deadlocks is to acquire
> locks in a consistent order, should such a construction be avoided
> in favor of two separate "select ... for update" statements so that
> the order of acquisition of a and b is known?

If you're worried about deadlock, what you should be worrying about is
the order in which the individual rows are visited --- and splitting
this into two SQL commands doesn't in itself guarantee more about that
than the command as given.

            regards, tom lane