Re: Is consistent (deterministic) ordering possible in our case?

gerry gan <xiang(dot)gan(dot)aalto(at)gmail(dot)com> writes:
> Thanks for the comments! I have a naive question related to the use of
> advisory lock. Based on my current reasoning about our scenario, consistent
> ordering of commands in two transactions might not help to solve the
> deadlock situation. If advisory lock is used, it can return false in case
> it cannot get lock on certain row. This, however, might occur in both
> transactions. Then it seems to be hard to continue from application side
> since the operation is, by any means, required by application logic. In
> other words, I guess this might cause 'deadlock' in applications. Do you
> have any suggestions to solve this situation? And I'm sorry if my question
> is naive. Thank you!

A common answer to this sort of problem is to be willing to retry
transactions after deadlock failures. If the deadlocks are common,
maybe this won't be workable from a performance standpoint. But if
they're rare, or you can tweak things to make them so, think about it.
Retries can be a lot simpler, more robust, and even more performant
than trying to get to a provably-deadlock-free implementation.

regards, tom lane