Re: Deadlock when inserting from multiple processes - Mailing list pgsql-admin

From Dave Johansen
Subject Re: Deadlock when inserting from multiple processes
Date
Msg-id CAAcYxUeJVA2mOC-3QhMUH9K4xvTDVCv14r+mopGjq2mddsxyMA@mail.gmail.com
Whole thread Raw
In response to Re: Deadlock when inserting from multiple processes  (Keith <keith@keithf4.com>)
List pgsql-admin
On Sun, May 1, 2016 at 7:03 PM, Keith <keith@keithf4.com> wrote:


On Sun, May 1, 2016 at 11:05 AM, Dave Johansen <davejohansen@gmail.com> wrote:
On Sat, Apr 30, 2016 at 8:07 PM, Keith <keith@keithf4.com> wrote:


On Fri, Apr 29, 2016 at 12:39 PM, Dave Johansen <davejohansen@gmail.com> wrote:
On Wed, Apr 27, 2016 at 1:45 PM, Keith <keith@keithf4.com> wrote:


On Wed, Apr 27, 2016 at 3:10 PM, Dave Johansen <davejohansen@gmail.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.

pgsql-admin by date:

Previous
From: Keith
Date:
Subject: Re: Deadlock when inserting from multiple processes
Next
From: Peter Brunnengräber
Date:
Subject: Hot_standby WAL archiving question