Re: Advisory lock deadlock issue

On 06/07/2017 08:11 AM, David Rosenstrauch wrote:
>
>
> On 06/07/2017 10:32 AM, Merlin Moncure wrote:
>> On Wed, Jun 7, 2017 at 9:16 AM, David Rosenstrauch <darose(at)darose(dot)net>
>> wrote:
>>> * How could it be possible that there are 2 PG processes trying to
>>> acquire
>>> the same lock? Spark's partitioning should ensure that all updates
>>> to the
>>> same user record get routed to the same process, so this situation
>>> shouldn't
>>> even be possible.
>>
>> That's really a question for the Spark team. Obviously they are --
>> advisory locks lay on top of the basic locking mechanics and are very
>> well tested and proven. What I can tell you is that in the core
>> functions provided by postgres there are no advisory locks thrown --
>> you own the locking space (that is, code under your control).
>
>>> * How/why am I winding up acquiring advisory locks in the first
>>> place? I'm
>>> never requesting them. I looked at the PG JDBC driver code a bit,
>>> thinking
>>> that it might automatically be creating them for some reason, but that
>>> doesn't seem to be the case. Maybe the PG database itself is? (E.g.,
>>> Does
>>> the PG automatically use advisory locks with UPSERTs?)
>>
>> Some code under your control is. This could be an external module,
>> application code, or an sproc.
>
>>> And, last but not least:
>>>
>>> * How do I resolve this "waits for ExclusiveLock on advisory lock"
>>> issue?
>>> There's precious little info available regarding exactly what that error
>>> message is and how to solve.
>>
>> Barring some reponse from Spark team, here is how I would narrow the
>> problem down:
>
>> merlin
>
>
> Thanks much for the suggestions. I'll look into them..
>
> As far as the source of the advisory locks, I don't think they're coming
> from Spark as I'm not using any Spark code to access PG. (Just straight
> JDBC.)
>
> I'm actually using an offshoot of PG (CitusDB), so perhaps Citus is
> somehow initiating them. I'll try to pin this down a bit further.

Aah that is an important piece of info. A quick search found:

https://www.citusdata.com/blog/2017/04/11/rebalancing-your-database-with-citus/

"
...

While this move is happening it takes a standard Postgres advisory locks
..."

>
> Thanks,
>
> DR
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com