From: Tom Clegg <tom@curii.com>
Date: Tue, 30 Nov 2021 15:57:08 +0000 (-0500)
Subject: 18488: Close pg connection when waiting for lock.
X-Git-Tag: 2.4.0~146^2
X-Git-Url: https://git.arvados.org/arvados.git/commitdiff_plain/9ed314b7a585970c03c87959286fc1e582d769f7

18488: Close pg connection when waiting for lock.

In practice, pg_advisory_lock() calls pile up and consume database
connection slots, even when the corresponding processes have exited.

Arvados-DCO-1.1-Signed-off-by: Tom Clegg <tom@curii.com>
---

diff --git a/lib/controller/dblock/dblock.go b/lib/controller/dblock/dblock.go
index b0d348870b..1a36822d5b 100644
--- a/lib/controller/dblock/dblock.go
+++ b/lib/controller/dblock/dblock.go
@@ -35,8 +35,8 @@ func (dbl *DBLocker) Lock(ctx context.Context, getdb func(context.Context) (*sql
 	for ; ; time.Sleep(retryDelay) {
 		dbl.mtx.Lock()
 		if dbl.conn != nil {
-			// Already locked by another caller in this
-			// process. Wait for them to release.
+			// Another goroutine is already locked/waiting
+			// on this lock. Wait for them to release.
 			dbl.mtx.Unlock()
 			continue
 		}
@@ -52,9 +52,15 @@ func (dbl *DBLocker) Lock(ctx context.Context, getdb func(context.Context) (*sql
 			dbl.mtx.Unlock()
 			continue
 		}
-		_, err = conn.ExecContext(ctx, `SELECT pg_advisory_lock($1)`, dbl.key)
+		var locked bool
+		err = conn.QueryRowContext(ctx, `SELECT pg_try_advisory_lock($1)`, dbl.key).Scan(&locked)
 		if err != nil {
-			logger.WithError(err).Infof("error getting pg_advisory_lock %d", dbl.key)
+			logger.WithError(err).Infof("error getting pg_try_advisory_lock %d", dbl.key)
+			conn.Close()
+			dbl.mtx.Unlock()
+			continue
+		}
+		if !locked {
 			conn.Close()
 			dbl.mtx.Unlock()
 			continue