Re: deadlock debug methodology question

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

In response to "antiochus antiochus" <antiochus.usa@gmail.com>:
>
> On Thu, May 22, 2008 at 2:57 PM, Bill Moran <wmoran@collaborativefusion.com>
> wrote:
>
> > In response to "antiochus antiochus" <antiochus.usa@gmail.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@collaborativefusion.com
Phone: 412-422-3463x4023