Re: Deadlock when inserting from multiple processes

On Sun, May 1, 2016 at 7:03 PM, Keith <keith(at)keithf4(dot)com> wrote:

>
>
> On Sun, May 1, 2016 at 11:05 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
>
>> On Sat, Apr 30, 2016 at 8:07 PM, Keith <keith(at)keithf4(dot)com> wrote:
>>
>>>
>>>
>>> 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
>>>
>>
>> We're using RHEL/CentOS because of its extended life cycle, so
>> unfortunately, upgrading to 9.5 is not an option for us.
>>
>> But either way, we're not trying to do an UPSERT. The multiple processes
>> will generate duplicate records at time and we only want to store the first
>> one with any subsequent ones being dropped.
>>
>> Having said all of that, my original question was if this is some sort of
>> issue/race condition in Postgres itself that needs to be investigated.
>>
>> Thanks,
>> Dave
>>
>
> It is a race condition, but it's not a problem that cant be handled for
> all cases in versions < 9.5.
>

So this is a known issue with < 9.5 and there's no need to look into it
further?

> The reasons your running into a deadlock trying to handle this with a
> function exception are handled with the new INSERT ... ON CONFLICT. It's
> not just for UPSERTs, that's just the most common case it was created for.
> It's for handling INSERT conflicts in general and handles this exact case
> as well. Look at INSERT ... ON CONFLICT DO NOTHING.
>

I had heard about UPSERT but didn't know about the ON CONFLICT DO NOTHING
stuff. That's definitely good to know.

> PostgreSQL provides RPMs for the latest versions going back to CentOS 5.
> Unless you've got some internal policy that you can only use packages from
> specific repositories, there's no technical reason you cannot upgrade. I
> run 9.4 myself on CentOS 6 from these exact RPMs.
>
> http://www.postgresql.org/download/linux/redhat/
>

Yes, and there's also the SCLs provided by RedHat, but the problem is that
it takes a long time for us to get things approved to start testing and
then testing something as central/complex as Postgres isn't exactly quick
either. RHEL 7 has been out for almost 2 years and we've just barely been
able to start testing/using it on our systems. Getting newer versions of
Postgres approved problem wouldn't take 2 years like an OS, but it when you
get down to it, the 2-3 year life cycle of SCLs makes them a none-starter
and the 5 year life cycle of Postgres means that we'd have to be chasing
versions every 2-3 years and that's not a game that I want to start playing.