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

Some months later (stupid business priorities...) we have something to
report on this. We have been able to produce a minimal schema to
demonstrate the problem, and successfully induced the deadlock using
pgbench (thanks to Paulo Tanimoto, cc-ed here).

Please see:

https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3

for detailed instructions on how to trigger the deadlock.

I would be thrilled to learn that we've done something wrong here,
otherwise I think this represents a regression introduced in 9.3.

A

On Mon, Aug 04, 2014 at 12:30:27PM -0400, Alvaro Herrera wrote:
> Andrew Sackville-West wrote:
>
> > > 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.
> >
> > Ah you're correct, I'll attach the description of that table. But,
> > really, that is all that's going on in the database.
>
> See, access_tokens has a foreign key to access_grants. So when the
> insert to access_tokens happens, the process also acquires a lock on a
> tuple on access_grants, which should not conflict with the UPDATE; but
> apparently it is blocking in some cases, for unknown reasons. But note
> that one of the original tables had a large number of foreign keys to
> other tables; are those necessary to reproduce the problem? I mean, if
> you just copy those three tables to an otherwise empty database and then
> run the hanging application, do you see a problem? If not, does adding
> all those other tables cause the problem to show up?
>
> Note that the set of columns covered by unique indexes is very
> important. Your \d does not have an unique index on the updated column
> AFAICS; but if it did have one, things would be completely different
> (because then the insert would definitely conflict with the update).
>
> It would help a lot of you posted the table definitions as pg_dump -t
> output rather than \d, because it's much easier for me to reproduce the
> setup, rather than cutting and pasting the \d output into CREATE TABLE
> commands.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--