Re: deadlock debug methodology question

Thanks for your responses, I appreciate the help.

I gather from this that 2 transactions concurrently running the exact same:

update table tt where ...

could end up in deadlock because it is not garanteed row-level locks are
taken in a consistent order in an update.

One possibility might then seem to do something like:

update table tt where ID in (select ID from tt where ... order by ID asc
for update);

but unfortunately 'for update' is not allows in subqueries. Therefore, one
could do:

select ID from tt where ... order by ID asc for update;
update table tt where ...;

However, in read committed mode, it is not garanteed that the subset of rows
selected with the two 'where' tests will be the same...

Therefore, it seems impossible to solve this issue without using some form
of explicit locking.

Does this make sense to you?

On Thu, May 22, 2008 at 2:57 PM, Bill Moran <wmoran(at)collaborativefusion(dot)com>
wrote:

> In response to "antiochus antiochus" <antiochus(dot)usa(at)gmail(dot)com>:
> >
> > I have a deadlock situation, two transactions waiting on each other to
> > complete. Based on the details below, would anyone have recommendations
> for
> > me, please?
>
> I have a theory on deadlocks, and that theory is that it's damn near
> impossible to track them all down, so your best bet is to wrap all
> SQL calls in a function that detects deadlock and sleep/retries.
>
> [snip]
>
> > Careful inspection of these (unfortunately complex) queries seems to
> > indicate row-level locks are acquired in consistent order, assuming that
> any
> > command of the type
> >
> > update tt where ....
> >
> > will always lock rows in a consistent order (can someone confirm that it
> is
> > necessarily the case).
>
> I believe that assertion is incorrect. Without seeing your entire
> query, I can only speculate, but unless you have an explicit ordering
> clause, there's no guarantee what order rows will be accessed in.
>
> Try putting an explicit ORDER BY in the queries and see if the problem
> goes away.
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran(at)collaborativefusion(dot)com
> Phone: 412-422-3463x4023
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>