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@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.
[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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general