Re: deadlock debug methodology question

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