regression, deadlock in high frequency single-row UPDATE

From: Andrew Sackville-West <awest(at)janrain(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: regression, deadlock in high frequency single-row UPDATE
Date: 2014-07-31 23:30:51
Message-ID: 20140731233051.GN17765@andrew-ThinkPad-X230
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs


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.

Multiple instances of the *exact* same single row update:

"z8z6px927zu6qzzbnb5ntgghxg"."access_grants" ag
"z8z6px927zu6qzzbnb5ntgghxg"."oauth_clients" oc
WHERE = ag.client_id
AND ag.entity_name = 'user'
AND ag.entity_id = 129
AND oc.client_id = '3hp45h9d4f9wwtx7cvpus6rdb4s5kb9f'

if performed with sufficient concurrency will produce a deadlock.

I've attached a snippet of logs, all uncommented lines from
postgresql.conf, and the table definitions of the involved
tables. This first instance of the deadlock appears in line 142; I
left a bunch of lead-in in case it's relevant.

I think this is a regression as we only see the behavior under
postgres 9.3.x (reproduced locally on 9.3.4 and 9.3.5 in a VMWare VM
running Ubuntu 11.04, but also evident in 9.3.3 on Amazon RDS). I am
unable to reproduce in the earlier versions I've been able to test against
(9.0.something and 9.1.9).

To reproduce the problem, I have to fork about 100 api calls against
our application, with the results you see in the logs. The queries in
the log are the *only* activity in the application and database at the
time and I have not filtered the logs at all, other than snipping to a
reasonable window around the problem. I can produce more extensive
logs if needed, but there really is nothing else.

I have not been able to reproduce the deadlock by making concurrent
UPDATEs via what amounts to a bash fork-bomb w/ psql, but I suspect my
methodology might be too crude there. The application is able to spin
up a large number of lightweight threads fairly quickly and presumably
attain the needed level of concurrency.



Attachment Content-Type Size
deadlock_log text/plain 55.5 KB
postgresql.conf text/plain 1.3 KB
table_defs text/plain 4.1 KB


Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2014-08-01 00:59:50 Re: regression, deadlock in high frequency single-row UPDATE
Previous Message Tom Lane 2014-07-31 16:19:28 Re: Is this a bug?