Re: regression, deadlock in high frequency single-row UPDATE
От | Andrew Sackville-West |
---|---|
Тема | Re: regression, deadlock in high frequency single-row UPDATE |
Дата | |
Msg-id | 20141208235552.GZ6930@andrew-ThinkPad-X230 обсуждение исходный текст |
Ответ на | Re: regression, deadlock in high frequency single-row UPDATE (Alvaro Herrera <alvherre@2ndquadrant.com>) |
Ответы |
Re: regression, deadlock in high frequency single-row UPDATE
(Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
Re: regression, deadlock in high frequency single-row UPDATE (Alvaro Herrera <alvherre@2ndquadrant.com>) |
Список | pgsql-bugs |
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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs --
В списке pgsql-bugs по дате отправления: