Re: deadlock problem in Ad serving..

Ron Mayer <ron(at)intervideo(dot)com> writes:
> Is it true that the problem happens when updates are done
> in a different order by two transactions like this:

> trans.1: "update banner_stats set imp=imp+1 where uniqid=4330"
> trans.2: "update banner_stats set imp=imp+1 where uniqid=10"
> trans.1: "update banner_stats set imp=imp+1 where uniqid=10"
> trans.2: "update banner_stats set imp=imp+1 where uniqid=4330"

Yeah, I would assume that that's the problem.

> If so, then could the problem be avoided if in his application
> logic he always did the updates in the same order? ... I.e. Make
> sure the each transaction does the updates in the same order by
> sorting his updates based on uniqid in the client?

If I understood correctly, he's tracking webpage hits; so the updates
are going to correspond to the sequence in which visitors move to
different webpages. I don't think he can force a particular order
(and if he could, there'd be little need for the per-page counts
anyway). I suppose he could hold all the updates in a temp table
and apply them in a sorted order at end of transaction, but that seems
like a pain.

I would suggest using a separate transaction for each webpage visited.
Holding a transaction open across multiple page traversals is widely
considered bad news for a number of reasons, not only this one.

regards, tom lane