kv/concurrency: concurrent role DDL deadlocks indefinitely; deadlock-detection push never fires

Open
#176,187 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
24/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
docker, go, postgresql

Research direction

Start with pkg/kv/kvserver/concurrency/lock_table_waiter.go and trace WaitOn state changes and deadlock push timers; then inspect pkg/sql/rolemembershipcache/cache.go and pkg/sql/catalog/lease/lease.go for the high-priority reads described. Use the single-node reproduction and goroutine/log evidence in the issue to compare timer behavior with the observed re-queue loop. Done means the deadlock is detected and one transaction is aborted in the stated reproduction; the issue also describes a separate multi-region retry cycle that needs investigation.

Written by the indexing model from the issue text.

Description

A-kv-transactions branch-release-26.2 C-bug O-support T-kv X-move-internal

Describe the problem

On v26.2.7, concurrent role DDL can block indefinitely. Each session runs a few autocommit statements (CREATE ROLE IF NOT EXISTS, GRANT <role> TO <user>, ALTER USER ... WITH LOGIN, ALTER USER ... SET <var>). The transactions form a lock cycle on the system.descriptor rows of the role tables (users 4, role_members 23, role_options 33, database_role_settings 44) and on rows in those tables. Deadlock detection never breaks the cycle. The statements stay blocked until a client session is cancelled; then everything releases within milliseconds. Logins slow down meanwhile, because they read the same tables.

Production occurrence: a multi-region cluster on v26.2.7. A provisioning job created 21 users at once, one session per user, seven statements each. Role DDL was blocked for 68 minutes, and again later for 10 minutes. There were about 15k user-lookup timeouts during logins. The first stall ended when the job's sessions were cancelled (the job was restarted). The second ended when two of the transactions involved were aborted by pushers (ABORT_REASON_PUSHER_ABORTED); we have not analysed that release further. From the tsdump, kv.concurrency.max_lock_wait_duration_nanos on the system.descriptor leaseholder rose by 60 s every minute to about 4,200 s. That is a single lock wait that was never re-queued or reset, while txnwaitqueue.deadlocks_total kept firing elsewhere in the cluster. The node logs show nine descriptor writes waiting 3,812 to 4,238 s, none of which logged a push during the last hour of its wait, and all of which released in the same millisecond when the sessions were cancelled.

We found two mechanisms that keep the cycle from being detected. The first one reproduces on a single node every time.

1. The deadlock-detection push is starved (single node, reproducible)

With two sessions doing role DDL and one session doing PRIORITY HIGH reads of the role tables, a waiter in the cycle re-enters the lock wait queue every 15 to 20 ms. Each time, it arms a fresh 100 ms deadlock-detection push, which never gets to fire. Log loop (--vmodule=lock_table_waiter=3), on the database_role_settings descriptor key:

lock wait-queue event: wait for txn <holder> holding lock @ key /Table/3/1/44/2/1
pushing after 100ms for: deadlock/liveness detection = true, timeout enforcement = false, priority enforcement = false, wait policy error = false
lock wait-queue event: done waiting

Counts:

  • Original run: 4,471 cycles in 79 s (mean 17.6 ms apart, max 44.2 ms) and zero deadlock pushes from that waiter.
  • Independent rerun: one goroutine armed 7,059 such pushes.

The goroutine dump shows the other side of the cycle in txnwait.(*Queue).waitForPush, and the starved side in lockTableWaiterImpl.WaitOn with no push in flight. The cycle is two ALTER USER ... SET transactions: one holds /Table/3/1/44, the other holds its system.database_role_settings row, and each waits on the other.

Relevant code at v26.2.7, pkg/kv/kvserver/concurrency/lock_table_waiter.go, WaitOn: every waitFor state calls timer.Reset(delay) with the full kv.lock_table.deadlock_detection_or_liveness_push_delay. The lock-timeout path keeps its deadline when the key and holder have not changed (newLock := ...); the deadlock-push delay has no equivalent. Here the waiter also goes through doneWaiting and back in, so the delay restarts across WaitOn calls, not only within one.

Our hypothesis for why the state keeps changing: each high-priority read pushes the holder's timestamp, which updates the lock and wakes the waiter. In the repro, the high-priority reader is a synthetic stand-in for internal reads at MaxUserPriority: the role membership cache refill (pkg/sql/rolemembershipcache/cache.go) and lease lookups (pkg/sql/catalog/lease/lease.go). We have not measured how often those run in production.

Results on one node:

setup result
No high-priority reader No stall in 20 rounds
1 high-priority reader, default 100 ms delay Stalled on every run (4 of 4 in the original testing, 6 of 6 in an independent rerun), in rounds 1 to 5, with no recovery during a 30 to 60 s hold
kv.lock_table.deadlock_detection_or_liveness_push_delay = '0s' or '10ms' No stall, at 2 sessions and at 21 sessions with 8 readers; reset to default and it stalls again

2. A GRANT restart loop that deadlock detection cannot see (multi-region)

On 12-node and 9-node three-region roachprod clusters (v26.2.7, 100 changefeeds, the production statement shape with 21 sessions), every unmodified run stalled, once with only a single job instance running. In the run where we logged every restart, the lock holder was a GRANT holding the role_members descriptor key /Table/3/1/23, with 8 waiters (up to 138 s). It restarted 28 times:

  • 27 × RETRY_SERIALIZABLE - failed preemptive refresh due to conflicting locks on /Table/4/2/<id>/0, a different id each time, about every 6 s.
  • 1 × cannot publish new versions for descriptors: [role_members 23], old versions still in use.

/Table/4/2 is the system.users index on user_id, which the GRANT reads. The conflicting locks are intents from concurrent CREATE ROLE transactions on newly allocated user ids. Those CREATE ROLEs wait on the users descriptor /Table/3/1/4. That key is held by a transaction that also holds /Table/3/1/33 and waits on /Table/3/1/23, which the GRANT holds:

GRANT            holds /Table/3/1/23; refresh fails on CREATE ROLE intents on /Table/4/2/<new id> -> epoch restart, keeps its lock, no push
CREATE ROLE txns wait on /Table/3/1/4
txn X            holds /Table/3/1/4 and /Table/3/1/33; waits on /Table/3/1/23

One edge of this cycle is a failed refresh plus an epoch restart, not a lock wait, so the txn wait queue never sees a cycle. Each CREATE ROLE retry allocates a new user id, so the conflicting key keeps moving and the loop never settles.

With the push delay set to 0 on the 9-node cluster, this shape was still severely degraded. CREATE ROLE statements took 30 to 180 s, GRANTs queued behind a single system.users row, and the stall watchdog (no statement completed for 90 s) tripped in round 1, while deadlock detection was visibly firing. That run was not observed to completion, so we can't say whether it would have cleared on its own. The push delay setting is therefore not a workaround for this shape.

Relation to #172970

The #172970 fix (64a3453978b, "catalog/descs: arm KV txn retry on retryOnModifiedDescriptor") makes "descriptor modified" an epoch restart that keeps locks. That retry ("forcing txn to retry due to modified descriptor") was the most common restart reason in the multi-region runs (309 of roughly 600 cluster-wide in one run), so it feeds the retry storm. The loop that holds the lock, though, is the refresh failure above. We did not test whether versions before the fix behave the same way.

To Reproduce (single node, about 10 minutes)

  1. Start one node: docker run -d --name repro-node -p 26357:26257 -p 8380:8080 cockroachdb/cockroach:v26.2.7 start-single-node --insecure --vmodule=lock_table_waiter=3,txn=2
  2. Run two sessions concurrently. Keep each connection open; a new connection per statement does not reproduce it. Each session first has a base role <r> created, then runs these autocommit statements in order, with a fresh user <u> each round:
    CREATE ROLE IF NOT EXISTS <u>;
    GRANT <r> TO <u>;
    ALTER USER <u> WITH LOGIN;
    ALTER USER <u> SET statement_timeout = '1h';
    ALTER USER <u> SET idle_in_transaction_session_timeout = '1h';
    ALTER USER <u> SET application_name = 'app';
    ALTER USER <u> SET timezone = 'UTC';
    
  3. At the same time, run one session looping this with a 10 ms sleep:
    BEGIN; SET TRANSACTION PRIORITY HIGH;
    SELECT count(*) FROM system.role_members;
    SELECT count(*) FROM system.users;
    SELECT count(*) FROM system.descriptor WHERE id IN (4, 23, 33, 44);
    COMMIT;
    
  4. Within a few rounds, both role sessions stop completing statements and do not recover. crdb_internal.cluster_locks shows the cycle on /Table/3/1/44 (or /Table/3/1/23). Cancelling one of the two sessions released the other within about 30 ms.
  5. Count the armed pushes per goroutine:
    grep -a -A1 'holding lock @ key /Table/3/1/' cockroach-data/logs/cockroach.log | grep -a 'pushing after 100ms' | awk '{print $3}' | sort | uniq -c | sort -rn | head -3
    

A Go driver (pgx v5) that runs this, detects the stall, and captures cluster_locks, queries, transactions and goroutine dumps is included at the end of this issue. Build with go build -o roleddl ., then: ./roleddl -urls "postgresql://root@localhost:26357/defaultdb?sslmode=disable" -admin http://localhost:8380 -roles 2 -rounds 20 -pushers 1 -pushprio HIGH -stall 45s -hold 30s -out results/repro (control: -pushers 0).

Expected behavior

The cycle is detected about 100 ms after it forms, one transaction is aborted, and the rest proceed.

Additional data

During the single-node stall, the two blocked sessions showed empty active_queries in SHOW CLUSTER SESSIONS and did not appear in SHOW CLUSTER STATEMENTS. The blocked statement appeared only in last_active_query, which the docs describe as the most recently completed query. This makes stuck statements hard to find in an incident and may be a separate issue. Seen with pgx v5 on a single node; not checked multi-region.

Environment:

  • CockroachDB v26.2.7 (production: self-hosted, multi-region; repro: docker aarch64 single node, roachprod x86_64 9 and 12 nodes)
  • Client: Go pgx v5 (repro); production client is a Kubernetes-based provisioning service

Additional context

Impact: role provisioning in production blocked for 68 and 10 minutes, with login degradation, until the client was restarted.

Workarounds we tested:

  • Running role DDL one statement at a time cluster-wide: no stall in any configuration.
  • statement_timeout: bounds a stall, but the blocked statements tend to time out together and collide again if retried immediately.
  • server.authentication_cache.enabled = false: still stalls.
  • Push delay 0: fixes the single-node shape but not the multi-region one.
Repro driver: main.go
// roleddl reproduces concurrent role DDL from a user-provisioning job: one session per role, each
// running seven role-DDL statements serially in autocommit, all sessions at
// once. A watchdog declares a stall when statements are in flight and none has
// completed for -stall, captures evidence, holds for -hold to see whether the
// cluster clears it alone, then cancels every session (the operator roll).
package main

import (
	"context"
	"encoding/csv"
	"flag"
	"fmt"
	"io"
	"net/http"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"sync/atomic"
	"time"

	"github.com/jackc/pgx/v5"
)

var (
	urls     = flag.String("urls", "postgresql://root@localhost:26301/defaultdb?sslmode=disable,postgresql://root@localhost:26302/defaultdb?sslmode=disable,postgresql://root@localhost:26303/defaultdb?sslmode=disable", "comma-separated gateway URLs; sessions round-robin across them")
	admin    = flag.String("admin", "http://localhost:8301,http://localhost:8302,http://localhost:8303", "admin URLs for goroutine dumps")
	nroles   = flag.Int("roles", 21, "concurrent role sessions per round")
	rounds   = flag.Int("rounds", 10, "rounds to run; stops at the first stall")
	stall    = flag.Duration("stall", 90*time.Second, "no completion for this long with work in flight = stall")
	hold     = flag.Duration("hold", 3*time.Minute, "after a stall, wait this long for self-resolution before cancelling")
	logins   = flag.Int("logins", 0, "background sessions repeatedly connecting as created users")
	out      = flag.String("out", "results/run", "output directory")
	tag      = flag.String("tag", "", "role-name prefix; defaults to a timestamp")
	serial   = flag.Bool("serial", false, "one session runs every role's statements in order")
	setup    = flag.String("setup", "", "SQL run once on each role session before its statements")
	pushers  = flag.Int("pushers", 0, "sessions looping high-priority reads of the role tables, to push writers' timestamps")
	pushPrio = flag.String("pushprio", "HIGH", "priority of the pusher transactions: HIGH, NORMAL or LOW")
	phased   = flag.Bool("phased", false, "every session runs CREATE ROLE, then all wait for each other, then the rest")
)

var stmts = []string{
	"CREATE ROLE IF NOT EXISTS %[1]s_u",
	"GRANT %[1]s_r TO %[1]s_u",
	"ALTER USER %[1]s_u WITH LOGIN",
	"ALTER USER %[1]s_u SET statement_timeout = '1h'",
	"ALTER USER %[1]s_u SET idle_in_transaction_session_timeout = '1h'",
	"ALTER USER %[1]s_u SET application_name = 'app'",
	"ALTER USER %[1]s_u SET timezone = 'UTC'",
}

type rec struct {
	round, role, stmt int
	gw                string
	start, end        time.Time
	err               string
}

func main() {
	flag.Parse()
	if *phased && *serial {
		panic("-phased and -serial are mutually exclusive")
	}
	must(os.MkdirAll(*out, 0o755))
	gws := strings.Split(*urls, ",")
	if *tag == "" {
		*tag = "t" + time.Now().Format("150405")
	}
	logf, err := os.Create(filepath.Join(*out, "driver.log"))
	must(err)
	logw := io.MultiWriter(os.Stdout, logf)
	say := func(f string, a ...any) { fmt.Fprintf(logw, time.Now().UTC().Format("15:04:05.000 ")+f+"\n", a...) }

	csvf, err := os.Create(filepath.Join(*out, "statements.csv"))
	must(err)
	cw := csv.NewWriter(csvf)
	cw.Write([]string{"round", "role", "stmt", "gateway", "start", "end", "ms", "err"})
	var mu sync.Mutex
	write := func(r rec) {
		mu.Lock()
		defer mu.Unlock()
		cw.Write([]string{fmt.Sprint(r.round), fmt.Sprint(r.role), fmt.Sprint(r.stmt), r.gw,
			r.start.UTC().Format(time.RFC3339Nano), r.end.UTC().Format(time.RFC3339Nano),
			fmt.Sprint(r.end.Sub(r.start).Milliseconds()), r.err})
		cw.Flush()
	}

	ctl, err := pgx.Connect(context.Background(), gws[0])
	must(err)
	_, err = ctl.Exec(context.Background(), "SET allow_unsafe_internals = true")
	must(err)
	var ver string
	must(ctl.QueryRow(context.Background(), "SELECT version()").Scan(&ver))
	say("binary: %s", ver)
	say("config: roles=%d rounds=%d stall=%s hold=%s logins=%d gateways=%d tag=%s", *nroles, *rounds, *stall, *hold, *logins, len(gws), *tag)

	var created sync.Map // user names that exist, for login workers
	loginCtx, stopLogins := context.WithCancel(context.Background())
	defer stopLogins()
	var loginOK, loginErr atomic.Int64
	for i := 0; i < *logins; i++ {
		go func(i int) {
			for loginCtx.Err() == nil {
				var u string
				created.Range(func(k, _ any) bool { u = k.(string); return i%2 == 0 })
				if u == "" {
					time.Sleep(200 * time.Millisecond)
					continue
				}
				url := strings.Replace(gws[i%len(gws)], "root@", u+"@", 1)
				c, err := pgx.Connect(loginCtx, url)
				if err != nil {
					loginErr.Add(1)
				} else {
					loginOK.Add(1)
					c.Close(context.Background())
				}
				time.Sleep(50 * time.Millisecond)
			}
		}(i)
	}

	var pushes atomic.Int64
	for i := 0; i < *pushers; i++ {
		go func(i int) {
			c, err := pgx.Connect(loginCtx, gws[i%len(gws)])
			if err != nil {
				return
			}
			defer c.Close(context.Background())
			c.Exec(loginCtx, "SET allow_unsafe_internals = true")
			for loginCtx.Err() == nil {
				tx, err := c.BeginTx(loginCtx, pgx.TxOptions{})
				if err != nil {
					continue
				}
				tx.Exec(loginCtx, "SET TRANSACTION PRIORITY "+*pushPrio)
				tx.Exec(loginCtx, "SELECT count(*) FROM system.role_members")
				tx.Exec(loginCtx, "SELECT count(*) FROM system.users")
				tx.Exec(loginCtx, "SELECT count(*) FROM system.descriptor WHERE id IN (4, 23, 33, 44)")
				tx.Commit(loginCtx)
				pushes.Add(1)
				time.Sleep(10 * time.Millisecond)
			}
		}(i)
	}
	say("pushers: %d at priority %s", *pushers, *pushPrio)

	for round := 1; round <= *rounds; round++ {
		base := fmt.Sprintf("%s_r%d", *tag, round)
		for i := 0; i < *nroles; i++ {
			_, err := ctl.Exec(context.Background(), fmt.Sprintf("CREATE ROLE IF NOT EXISTS %s_s%d_r", base, i))
			must(err)
		}
		say("round %d: %d base roles created, starting %d sessions", round, *nroles, *nroles)

		ctx, cancel := context.WithCancel(context.Background())
		var inflight, done atomic.Int64
		var lastDone atomic.Int64
		lastDone.Store(time.Now().UnixNano())
		var wg sync.WaitGroup
		start := make(chan struct{})
		var barrier sync.WaitGroup
		if *phased {
			barrier.Add(*nroles)
		}
		workers, perWorker := *nroles, 1
		if *serial {
			workers, perWorker = 1, *nroles
		}
		for w := 0; w < workers; w++ {
			wg.Add(1)
			go func(w int) {
				defer wg.Done()
				gw := gws[w%len(gws)]
				c, err := pgx.Connect(ctx, gw)
				if err != nil {
					say("worker %d connect: %v", w, err)
					return
				}
				defer c.Close(context.Background())
				passed := false // phased: whether this session has reached the barrier
				defer func() {
					if *phased && !passed {
						barrier.Done()
					}
				}()
				if *setup != "" {
					if _, err := c.Exec(ctx, *setup); err != nil {
						say("worker %d setup: %v", w, err)
						return
					}
				}
				<-start
				for j := 0; j < perWorker; j++ {
					i := w + j
					name := fmt.Sprintf("%s_s%d", base, i)
					for s, tmpl := range stmts {
						if *phased && s == 1 {
							passed = true
							barrier.Done()
							barrier.Wait()
						}
						q := fmt.Sprintf(tmpl, name)
						for attempt := 1; ; attempt++ {
							inflight.Add(1)
							t0 := time.Now()
							_, err := c.Exec(ctx, q)
							t1 := time.Now()
							inflight.Add(-1)
							e := ""
							if err != nil {
								e = err.Error()
							}
							write(rec{round, i, s, gwHost(gw), t0, t1, e})
							if err == nil {
								done.Add(1)
								lastDone.Store(t1.UnixNano())
								if s == 0 {
									created.Store(name+"_u", true)
								}
								break
							}
							if ctx.Err() != nil || attempt >= 5 {
								return
							}
						}
					}
				}
			}(w)
		}
		t0 := time.Now()
		close(start)

		finished := make(chan struct{})
		go func() { wg.Wait(); close(finished) }()
		stalled := false
		tick := time.NewTicker(5 * time.Second)
	watch:
		for {
			select {
			case <-finished:
				break watch
			case <-tick.C:
				idle := time.Since(time.Unix(0, lastDone.Load()))
				say("round %d: done=%d/%d inflight=%d idle=%s logins ok/err=%d/%d pushes=%d", round, done.Load(), *nroles*len(stmts), inflight.Load(), idle.Round(time.Second), loginOK.Load(), loginErr.Load(), pushes.Load())
				if inflight.Load() > 0 && idle > *stall && !stalled {
					stalled = true
					say("STALL round %d: %d statements in flight, no completion for %s", round, inflight.Load(), idle.Round(time.Second))
					capture(ctl, filepath.Join(*out, fmt.Sprintf("stall-r%d-a", round)), say)
					deadline := time.Now().Add(*hold)
					for time.Now().Before(deadline) {
						select {
						case <-finished:
							say("round %d cleared by itself %s after the stall was declared", round, time.Since(deadline.Add(-*hold)).Round(time.Second))
							break watch
						case <-time.After(5 * time.Second):
						}
						say("holding: done=%d inflight=%d idle=%s", done.Load(), inflight.Load(), time.Since(time.Unix(0, lastDone.Load())).Round(time.Second))
					}
					capture(ctl, filepath.Join(*out, fmt.Sprintf("stall-r%d-b", round)), say)
					say("cancelling all sessions (operator roll)")
					tc := time.Now()
					cancel()
					<-finished
					say("all sessions returned %s after cancel", time.Since(tc).Round(time.Millisecond))
					break watch
				}
			}
		}
		tick.Stop()
		cancel()
		say("round %d: finished in %s, done=%d/%d, stalled=%v", round, time.Since(t0).Round(time.Millisecond), done.Load(), *nroles*len(stmts), stalled)
		if stalled {
			say("RESULT: STALLED in round %d", round)
			return
		}
	}
	say("RESULT: NO STALL in %d rounds", *rounds)
}

// capture saves lock state and goroutine dumps from every node.
func capture(ctl *pgx.Conn, dir string, say func(string, ...any)) {
	os.MkdirAll(dir, 0o755)
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()
	for name, q := range map[string]string{
		"cluster_locks":   "SELECT range_id, lock_key_pretty, txn_id::STRING, ts, lock_strength, durability, granted, contended, duration FROM crdb_internal.cluster_locks WHERE contended ORDER BY lock_key_pretty, granted DESC",
		"cluster_queries": "SELECT node_id, session_id, txn_id::STRING, start, query, phase FROM crdb_internal.cluster_queries ORDER BY start",
		"txns":            "SELECT node_id, id::STRING, start, txn_string, num_retries, num_auto_retries FROM crdb_internal.cluster_transactions ORDER BY start",
	} {
		rows, err := ctl.Query(ctx, q)
		f, _ := os.Create(filepath.Join(dir, name+".tsv"))
		if err != nil {
			fmt.Fprintln(f, "ERROR:", err)
			f.Close()
			continue
		}
		for rows.Next() {
			v, _ := rows.Values()
			s := make([]string, len(v))
			for i := range v {
				s[i] = fmt.Sprint(v[i])
			}
			fmt.Fprintln(f, strings.Join(s, "\t"))
		}
		if err := rows.Err(); err != nil {
			fmt.Fprintln(f, "ERROR:", err)
		}
		rows.Close()
		f.Close()
	}
	for i, a := range strings.Split(*admin, ",") {
		resp, err := http.Get(a + "/debug/pprof/goroutine?debug=2")
		if err != nil {
			continue
		}
		f, _ := os.Create(filepath.Join(dir, fmt.Sprintf("goroutines-n%d.txt", i+1)))
		io.Copy(f, resp.Body)
		resp.Body.Close()
		f.Close()
	}
	say("captured evidence into %s", dir)
}

func must(err error) {
	if err != nil {
		panic(err)
	}
}

// gwHost returns host:port from a postgres URL, for the statements CSV.
func gwHost(u string) string {
	s := u[strings.Index(u, "@")+1:]
	if i := strings.IndexAny(s, "/?"); i >= 0 {
		s = s[:i]
	}
	return s
}
go.mod
module roleddl

go 1.25.0

require github.com/jackc/pgx/v5 v5.10.0

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
	github.com/jackc/puddle/v2 v2.2.2 // indirect
	golang.org/x/sync v0.17.0 // indirect
	golang.org/x/text v0.29.0 // indirect
)

Jira issue: CRDB-70629

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.