chore: add PGLocks query to analyze what locks are held in pg

Emyrk · Emyrk · commit 6646d0488e95 · 2024-10-31T10:53:04.000-04:00
diff --git a/coderd/database/db.go b/coderd/database/db.go
@@ -12,6 +12,9 @@ import (
 	"context"
 	"database/sql"
 	"errors"
+	"fmt"
+	"sort"
+	"strings"
 	"time"
 
 	"github.com/jmoiron/sqlx"
@@ -28,6 +31,7 @@ type Store interface {
 	wrapper
 
 	Ping(ctx context.Context) (time.Duration, error)
+	PGLocks(ctx context.Context) (PGLocks, error)
 	InTx(func(Store) error, *TxOptions) error
 }
 
@@ -134,6 +138,98 @@ func (q *sqlQuerier) Ping(ctx context.Context) (time.Duration, error) {
 	return time.Since(start), err
 }
 
+type PGLocks []PGLock
+
+func (l PGLocks) String() string {
+	// Try to group things together by relation name.
+	sort.Slice(l, func(i, j int) bool {
+		return safeString(l[i].RelationName) < safeString(l[j].RelationName)
+	})
+
+	var out strings.Builder
+	for i, lock := range l {
+		if i != 0 {
+			out.WriteString("\n")
+		}
+		out.WriteString(lock.String())
+	}
+	return out.String()
+}
+
+// PGLock docs see: https://www.postgresql.org/docs/current/view-pg-locks.html#VIEW-PG-LOCKS
+type PGLock struct {
+	// LockType see: https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LOCK-TABLE
+	LockType           *string    `db:"locktype"`
+	Database           *string    `db:"database"` // oid
+	Relation           *string    `db:"relation"` // oid
+	RelationName       *string    `db:"relation_name"`
+	Page               *int       `db:"page"`
+	Tuple              *int       `db:"tuple"`
+	VirtualXID         *string    `db:"virtualxid"`
+	TransactionID      *string    `db:"transactionid"` // xid
+	ClassID            *string    `db:"classid"`       // oid
+	ObjID              *string    `db:"objid"`         // oid
+	ObjSubID           *int       `db:"objsubid"`
+	VirtualTransaction *string    `db:"virtualtransaction"`
+	PID                int        `db:"pid"`
+	Mode               *string    `db:"mode"`
+	Granted            bool       `db:"granted"`
+	FastPath           *bool      `db:"fastpath"`
+	WaitStart          *time.Time `db:"waitstart"`
+}
+
+func (l PGLock) String() string {
+	granted := "granted"
+	if !l.Granted {
+		granted = "waiting"
+	}
+	var details string
+	switch safeString(l.LockType) {
+	case "relation":
+		details = ""
+	case "page":
+		details = fmt.Sprintf("page=%d", *l.Page)
+	case "tuple":
+		details = fmt.Sprintf("page=%d tuple=%d", *l.Page, *l.Tuple)
+	case "virtualxid":
+		details = "waiting to acquire virtual tx id lock"
+	default:
+		details = "???"
+	}
+	return fmt.Sprintf("%d-%5s [%s] %s/%s/%s: %s",
+		l.PID,
+		safeString(l.TransactionID),
+		granted,
+		safeString(l.RelationName),
+		safeString(l.LockType),
+		safeString(l.Mode),
+		details,
+	)
+}
+
+// PGLocks returns a list of all locks in the database currently in use.
+func (q *sqlQuerier) PGLocks(ctx context.Context) (PGLocks, error) {
+	rows, err := q.sdb.QueryContext(ctx, `
+	SELECT
+		relation::regclass AS relation_name,
+	    *
+	FROM pg_locks;
+	`)
+	if err != nil {
+		return nil, err
+	}
+
+	defer rows.Close()
+
+	var locks []PGLock
+	err = sqlx.StructScan(rows, &locks)
+	if err != nil {
+		return nil, err
+	}
+
+	return locks, err
+}
+
 func DefaultTXOptions() *TxOptions {
 	return &TxOptions{
 		Isolation: sql.LevelDefault,
@@ -218,3 +314,10 @@ func (q *sqlQuerier) runTx(function func(Store) error, txOpts *sql.TxOptions) er
 	}
 	return nil
 }
+
+func safeString(s *string) string {
+	if s == nil {
+		return "<nil>"
+	}
+	return *s
+}
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -602,6 +602,9 @@ func prepareSQLFilter(ctx context.Context, authorizer rbac.Authorizer, action po
 func (q *querier) Ping(ctx context.Context) (time.Duration, error) {
 	return q.db.Ping(ctx)
 }
+func (q *querier) PGLocks(ctx context.Context) (database.PGLocks, error) {
+	return q.db.PGLocks(ctx)
+}
 
 // InTx runs the given function in a transaction.
 func (q *querier) InTx(function func(querier database.Store) error, txOpts *database.TxOptions) error {
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -338,6 +338,9 @@ func newUniqueConstraintError(uc database.UniqueConstraint) *pq.Error {
 func (*FakeQuerier) Ping(_ context.Context) (time.Duration, error) {
 	return 0, nil
 }
+func (*FakeQuerier) PGLocks(ctx context.Context) (database.PGLocks, error) {
+	return []database.PGLock{}, nil
+}
 
 func (tx *fakeTx) AcquireLock(_ context.Context, id int64) error {
 	if _, ok := tx.FakeQuerier.locks[id]; ok {
diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go

Original file line number	Diff line number	Diff line change
`@@ -602,6 +602,9 @@ func prepareSQLFilter(ctx context.Context, authorizer rbac.Authorizer, action po`
`602`	`602`	`func (q *querier) Ping(ctx context.Context) (time.Duration, error) {`
`603`	`603`	`return q.db.Ping(ctx)`
`604`	`604`	`}`
	`605`	`+func (q *querier) PGLocks(ctx context.Context) (database.PGLocks, error) {`
	`606`	`+ return q.db.PGLocks(ctx)`
	`607`	`+}`
`605`	`608`
`606`	`609`	`// InTx runs the given function in a transaction.`
`607`	`610`	`func (q querier) InTx(function func(querier database.Store) error, txOpts database.TxOptions) error {`
Original file line number	Diff line number	Diff line change
`@@ -338,6 +338,9 @@ func newUniqueConstraintError(uc database.UniqueConstraint) *pq.Error {`
`338`	`338`	`func (*FakeQuerier) Ping(_ context.Context) (time.Duration, error) {`
`339`	`339`	`return 0, nil`
`340`	`340`	`}`
	`341`	`+func (*FakeQuerier) PGLocks(ctx context.Context) (database.PGLocks, error) {`
	`342`	`+ return []database.PGLock{}, nil`
	`343`	`+}`
`341`	`344`
`342`	`345`	`func (tx *fakeTx) AcquireLock(_ context.Context, id int64) error {`
`343`	`346`	`if _, ok := tx.FakeQuerier.locks[id]; ok {`