Please don't top-post. I've attempted to reconstruct the conversation flow. In response to "antiochus antiochus" <antiochus.usa@xxxxxxxxx>: > > On Thu, May 22, 2008 at 2:57 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxxxxxxxx> > wrote: > > > In response to "antiochus antiochus" <antiochus.usa@xxxxxxxxx>: > > > > > > 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@xxxxxxxxxxxxxxxxxxxxxxx Phone: 412-422-3463x4023
