Skip to content

Commit 3a9270d

Browse files
EmyrkEmyrk
authored
chore: add PGLocks query to analyze what locks are held in pg (#15308)
adds `PGLocks` query for debugging what pg_locks are held during transactions.
1 parent 6ce8bfe commit 3a9270d

File tree

8 files changed

+162
-1
lines changed

8 files changed

+162
-1
lines changed

‎coderd/database/db.go

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ type Store interface {
2828
wrapper
2929

3030
Ping(ctx context.Context) (time.Duration, error)
31+
PGLocks(ctx context.Context) (PGLocks, error)
3132
InTx(func(Store) error, *TxOptions) error
3233
}
3334

@@ -218,3 +219,10 @@ func (q *sqlQuerier) runTx(function func(Store) error, txOpts *sql.TxOptions) er
218219
}
219220
return nil
220221
}
222+
223+
func safeString(s *string) string {
224+
if s == nil {
225+
return "<nil>"
226+
}
227+
return *s
228+
}

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -603,6 +603,10 @@ func (q *querier) Ping(ctx context.Context) (time.Duration, error) {
603603
return q.db.Ping(ctx)
604604
}
605605

606+
func (q *querier) PGLocks(ctx context.Context) (database.PGLocks, error) {
607+
return q.db.PGLocks(ctx)
608+
}
609+
606610
// InTx runs the given function in a transaction.
607611
func (q *querier) InTx(function func(querier database.Store) error, txOpts *database.TxOptions) error {
608612
return q.db.InTx(func(tx database.Store) error {

‎coderd/database/dbauthz/dbauthz_test.go

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -152,7 +152,10 @@ func TestDBAuthzRecursive(t *testing.T) {
152152
for i := 2; i < method.Type.NumIn(); i++ {
153153
ins = append(ins, reflect.New(method.Type.In(i)).Elem())
154154
}
155-
if method.Name == "InTx" || method.Name == "Ping" || method.Name == "Wrappers" {
155+
if method.Name == "InTx" ||
156+
method.Name == "Ping" ||
157+
method.Name == "Wrappers" ||
158+
method.Name == "PGLocks" {
156159
continue
157160
}
158161
// Log the name of the last method, so if there is a panic, it is

‎coderd/database/dbauthz/setup_test.go

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@ var errMatchAny = xerrors.New("match any error")
3434
var skipMethods = map[string]string{
3535
"InTx": "Not relevant",
3636
"Ping": "Not relevant",
37+
"PGLocks": "Not relevant",
3738
"Wrappers": "Not relevant",
3839
"AcquireLock": "Not relevant",
3940
"TryAcquireLock": "Not relevant",

‎coderd/database/dbmem/dbmem.go

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -339,6 +339,10 @@ func (*FakeQuerier) Ping(_ context.Context) (time.Duration, error) {
339339
return 0, nil
340340
}
341341

342+
func (*FakeQuerier) PGLocks(_ context.Context) (database.PGLocks, error) {
343+
return []database.PGLock{}, nil
344+
}
345+
342346
func (tx *fakeTx) AcquireLock(_ context.Context, id int64) error {
343347
if _, ok := tx.FakeQuerier.locks[id]; ok {
344348
return xerrors.Errorf("cannot acquire lock %d: already held", id)

‎coderd/database/dbmetrics/querymetrics.go

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/pglocks.go

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,119 @@
1+
package database
2+
3+
import (
4+
"context"
5+
"fmt"
6+
"reflect"
7+
"sort"
8+
"strings"
9+
"time"
10+
11+
"github.com/jmoiron/sqlx"
12+
13+
"github.com/coder/coder/v2/coderd/util/slice"
14+
)
15+
16+
// PGLock docs see: https://www.postgresql.org/docs/current/view-pg-locks.html#VIEW-PG-LOCKS
17+
type PGLock struct {
18+
// LockType see: https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LOCK-TABLE
19+
LockType *string `db:"locktype"`
20+
Database *string `db:"database"` // oid
21+
Relation *string `db:"relation"` // oid
22+
RelationName *string `db:"relation_name"`
23+
Page *int `db:"page"`
24+
Tuple *int `db:"tuple"`
25+
VirtualXID *string `db:"virtualxid"`
26+
TransactionID *string `db:"transactionid"` // xid
27+
ClassID *string `db:"classid"` // oid
28+
ObjID *string `db:"objid"` // oid
29+
ObjSubID *int `db:"objsubid"`
30+
VirtualTransaction *string `db:"virtualtransaction"`
31+
PID int `db:"pid"`
32+
Mode *string `db:"mode"`
33+
Granted bool `db:"granted"`
34+
FastPath *bool `db:"fastpath"`
35+
WaitStart *time.Time `db:"waitstart"`
36+
}
37+
38+
func (l PGLock) Equal(b PGLock) bool {
39+
// Lazy, but hope this works
40+
return reflect.DeepEqual(l, b)
41+
}
42+
43+
func (l PGLock) String() string {
44+
granted := "granted"
45+
if !l.Granted {
46+
granted = "waiting"
47+
}
48+
var details string
49+
switch safeString(l.LockType) {
50+
case "relation":
51+
details = ""
52+
case "page":
53+
details = fmt.Sprintf("page=%d", *l.Page)
54+
case "tuple":
55+
details = fmt.Sprintf("page=%d tuple=%d", *l.Page, *l.Tuple)
56+
case "virtualxid":
57+
details = "waiting to acquire virtual tx id lock"
58+
default:
59+
details = "???"
60+
}
61+
return fmt.Sprintf("%d-%5s [%s] %s/%s/%s: %s",
62+
l.PID,
63+
safeString(l.TransactionID),
64+
granted,
65+
safeString(l.RelationName),
66+
safeString(l.LockType),
67+
safeString(l.Mode),
68+
details,
69+
)
70+
}
71+
72+
// PGLocks returns a list of all locks in the database currently in use.
73+
func (q *sqlQuerier) PGLocks(ctx context.Context) (PGLocks, error) {
74+
rows, err := q.sdb.QueryContext(ctx, `
75+
SELECT
76+
relation::regclass AS relation_name,
77+
*
78+
FROM pg_locks;
79+
`)
80+
if err != nil {
81+
return nil, err
82+
}
83+
84+
defer rows.Close()
85+
86+
var locks []PGLock
87+
err = sqlx.StructScan(rows, &locks)
88+
if err != nil {
89+
return nil, err
90+
}
91+
92+
return locks, err
93+
}
94+
95+
type PGLocks []PGLock
96+
97+
func (l PGLocks) String() string {
98+
// Try to group things together by relation name.
99+
sort.Slice(l, func(i, j int) bool {
100+
return safeString(l[i].RelationName) < safeString(l[j].RelationName)
101+
})
102+
103+
var out strings.Builder
104+
for i, lock := range l {
105+
if i != 0 {
106+
_, _ = out.WriteString("\n")
107+
}
108+
_, _ = out.WriteString(lock.String())
109+
}
110+
return out.String()
111+
}
112+
113+
// Difference returns the difference between two sets of locks.
114+
// This is helpful to determine what changed between the two sets.
115+
func (l PGLocks) Difference(to PGLocks) (new PGLocks, removed PGLocks) {
116+
return slice.SymmetricDifferenceFunc(l, to, func(a, b PGLock) bool {
117+
return a.Equal(b)
118+
})
119+
}

0 commit comments

Comments
 (0)