Re: Advisory lock deadlock issue using PG JDBC driver

On 6 June 2017 at 16:46, David Rosenstrauch <darose(at)darose(dot)net> wrote:

> I'm running a Spark job that is writing to a postgres db (v9.6), using the
> JDBC driver (v42.0.0), and running into a puzzling error:
>
> 2017-06-06 16:05:17.718 UTC [36661] dmx(at)dmx ERROR: deadlock detected
> 2017-06-06 16:05:17.718 UTC [36661] dmx(at)dmx DETAIL: Process 36661 waits
> for ExclusiveLock on advisory lock [16649,0,102440,5]; blocked by process
> 36662.
> Process 36662 waits for ExclusiveLock on advisory lock
> [16649,0,102439,5]; blocked by process 36661.
>
> However, I can't for the life of me figure out a) how the advisory locks
> are getting created (as I'm not calling for them myself), and b) how to fix
> this issue.
>
>
> A bit of background about my setup. My Spark job obviously runs as
> multiple processes on multiple machines. Each process is performing the
> writes to pgsql using the jdbc driver. The writes are performed a) as PG
> UPSERTS, b) as JDBC batches, and c) using JDBC prepared statements. So each
> process, when it's time to write to the db, creates several prepared
> statements, adds a bunch of UPSERTs to each prepared statement (i.e., each
> prepared statement contains a batch of a few hundred UPSERTs), and then
> performs an executeBatch() on each statement to perform the write. That
> executeBatch() call is where I'm running into the error.
>
> In theory, since there's multiple processes that are issuing these batched
> DB writes, there could be a record locking problem if, say, 2 processes
> tried to perform updates to the same user record. But in reality this is
> impossible. Spark partitions everything based on a key - in my case userID
> - so all DB writes for the same user should be happening in the same
> process. So at worst I could just have a batch that contains multiple
> UPSERTs to the same user record - never updates to the same user from
> different processes.
>
>
> So, I'm very puzzled by that deadlock error. Specifically:
>
> * 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.
>
> * How/why am I winding up acquiring advisory locks in the first place? I'm
> never requesting them. Is the PG JDBC driver automatically creating them
> for some reason? (E.g., Does the driver automatically use advisory locks
> with prepared statements? With batches? With UPSERTs?)
>

We do not (AFAIK) create advisory locks.

>
> 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.
>
> I would guess the first step would be to figure out who is acquiring them.
Increasing the logging on the server would be my first step.

>
> Any help or pointers greatly appreciated!
>
> Thanks,
>
> DR

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>