From: | Keith <keith(at)keithf4(dot)com> |
---|---|
To: | Dave Johansen <davejohansen(at)gmail(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Deadlock when inserting from multiple processes |
Date: | 2016-05-01 03:07:25 |
Message-ID: | CAHw75vsR_VkHdJMM2sQb9PV+VPFR8vTgfM3b8-tcNPqt3v3yfw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Fri, Apr 29, 2016 at 12:39 PM, Dave Johansen <davejohansen(at)gmail(dot)com>
wrote:
> On Wed, Apr 27, 2016 at 1:45 PM, Keith <keith(at)keithf4(dot)com> wrote:
>
>>
>>
>> On Wed, Apr 27, 2016 at 3:10 PM, Dave Johansen <davejohansen(at)gmail(dot)com>
>> wrote:
>>
>>> I'm using Postgres 9.2.15 on CentOS 7.2 with the method described below
>>> to support INSERTing from multiple processes:
>>>
>>> http://www.postgresql.org/message-id/CAAcYxUf9o3NpiKaHdeShO_iJjm92314eX6ko0JvJidmksutkJg@mail.gmail.com
>>>
>>> Last night, one of the processes experienced a dead lock. The error
>>> looked like this:
>>> ERROR: deadlock detected
>>> DETAIL: Process 23527 waits for ShareLock on transaction 1537228819;
>>> blocked by process 34184
>>> Process 34184 waits for ShareLock on transaction 1537228441;
>>> blocked by process 23527.
>>>
>>> Is there anything I can do to help diagnose what the cause of this issue
>>> was?
>>>
>>> Thanks,
>>> Dave
>>>
>>
>> Look in the postgresql logs. It should give you more info on what queries
>> were running in each session that conflicted with each other. If you didn't
>> have the PID as part of your log_line_prefix, that can make it a bit more
>> challenging to figure out which sessions were which, though. I've found
>> this prefix to be very useful (note there is a space at the end):
>>
>> "%t [%r] [%p]: [%l-1] user=%u,db=%d,e=%e "
>>
>> 9.2 should give you enough info, but I believe more recent versions are
>> much more verbose with their deadlock output to help with debugging.
>>
>
> Sorry, I didn't include that part because I was retyping the log output,
> but it did provide that information about the queries and it was two
> instances like the one that I linked to above. Basically, it's a function
> that wraps an INSERT with exception handling so that duplicated records
> won't cause an error. According to the log, the two queries were executing
> the actual INSERT when the deadlock happened.
>
Are you trying to handle an UPSERT situation (try to insert; if row exists,
update instead)? I'd highly recommend upgrading to 9.5 if so. You can do
the exception handling method prior to 9.5, but you run into this very
situation on high traffic systems. 9.5 handle UPSERT properly.
https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Johansen | 2016-05-01 15:05:43 | Re: Deadlock when inserting from multiple processes |
Previous Message | Dave Johansen | 2016-04-30 03:30:46 | Re: Best way to handle "read only" paritions |