Re: Advisory lock deadlock issue

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.

Thanks,

DR