Re: regression, deadlock in high frequency single-row UPDATE

Andrew Sackville-West wrote:
> Hi,
>
> I've discussed this problem on irc a couple of times and think I've
> found a regression that plagues our application, introduced in some
> version newer than 9.1.9, and still present in 9.3.5.

I don't think you have provided everything: for instance I see that your
log has INSERTs into table access_tokens, which has a foreign key
relationship to/from some other table you showed.

I'm too lazy to unscramble the whole thing into a real schema right now.
pg_dump output would be more helpful, as would some trivial sample data
enough to reproduce the problem.

It'd probably be easier to reproduce if you wrap the statements in
transactions and have them sleep before COMMIT. (If you run two of
these updates in parallel, the first one blocks the second until the
first one commits, because they update the same row. What happens if
the blocking session runs the aforementioned INSERT while the other is
blocked? Perhaps three sessions are needed to show a problem without
explicit transactions.)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services