Re: deadlock problem in Ad serving..

Ron Mayer <ron@intervideo.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