Re: deadlock debug methodology question

Please don't top-post. I've attempted to reconstruct the conversation
flow.

In response to "antiochus antiochus" <antiochus(dot)usa(at)gmail(dot)com>:
>
> 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.
>
> 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 guaranteed that the subset of rows
> selected with the two 'where' tests will be the same...

I can see two solutions:

BEGIN;
SET TRANSACTION SERIALIZABLE
select ID from tt where ... order by ID asc for update;
update table tt where ...;
COMMIT;

or

BEGIN;
LOCK TABLE tt IN SHARE MODE;
select ID from tt where ... order by ID asc for update;
update table tt where ...;
COMMIT;

Depending on exactly what you need to accomplish.

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