pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test: TestLogic_select_for_share failed

[github-actions]

pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test.TestLogic_select_for_share failed on master @ 0d45639df842f24573ea3224e532abe0b9338783:

=== RUN   TestLogic_select_for_share
    test_log_scope.go:172: test logs captured to: outputs.zip/logTestLogic_select_for_share2530558074
    test_log_scope.go:82: use -show-logs to present logs inline
    test_server_shim.go:146: using test tenant configuration from testserver factory defaults
    test_server_shim.go:150: cluster virtualization disabled due to issue: #156124 (expected label: C-bug)
    logictestbase.go:582: statement/query skipped with reason: unsupported configuration fakedist-vec-off, statement/query only supports local-read-committed (no issue given)
    logictestbase.go:582: statement/query skipped with reason: unsupported configuration fakedist-vec-off, statement/query only supports local-repeatable-read (no issue given)
    logictestbase.go:582: statement/query skipped with reason: unsupported configuration fakedist-vec-off, statement/query only supports weak-iso-level-configs (no issue given)
    logic.go:3252: 
         
        /mnt/engflow/worker/work/2/exec/bazel-out/k8-fastbuild/bin/pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test_/fakedist-vec-off_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/select_for_share:175: SELECT database_name, schema_name, table_name, lock_key_pretty, lock_strength, durability, isolation_level, granted, contended FROM crdb_internal.cluster_locks
        expected:
            database_name  schema_name  table_name  lock_key_pretty   lock_strength  durability    isolation_level  granted  contended
            test           public       t           /Table/106/1/2/0  Shared         Unreplicated  SERIALIZABLE     true     false
            test           public       t           /Table/106/1/2/0  Shared         Unreplicated  SERIALIZABLE     true     false
            
        but found (query options: "colnames,retry,rowsort" -> ignore the following ordering of rows) :
            database_name  schema_name  table_name    lock_key_pretty                 lock_strength  durability    isolation_level  granted  contended
            system         ·            role_members  /Table/23/1/"admin"/"root"/0    Intent         Replicated    SERIALIZABLE     true     false
            system         ·            role_members  /Table/23/1/"admin"/"root"/3/1  Intent         Replicated    SERIALIZABLE     true     false
            system         ·            role_members  /Table/23/1/"admin"/"root"/4/1  Intent         Replicated    SERIALIZABLE     true     false
            system         ·            role_members  /Table/23/1/"admin"/"root"/5/1  Intent         Replicated    SERIALIZABLE     true     false
            test           public       t             /Table/106/1/2/0                Shared         Unreplicated  SERIALIZABLE     true     false
            test           public       t             /Table/106/1/2/0                Shared         Unreplicated  SERIALIZABLE     true     false
            
[16:33:06] --- progress: /mnt/engflow/worker/work/2/exec/bazel-out/k8-fastbuild/bin/pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test_/fakedist-vec-off_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/select_for_share: 32 statements
[16:33:06] --- done: /mnt/engflow/worker/work/2/exec/bazel-out/k8-fastbuild/bin/pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test_/fakedist-vec-off_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/select_for_share with config fakedist-vec-off: 32 tests, 1 failures
    logic.go:4672: 
        /mnt/engflow/worker/work/2/exec/bazel-out/k8-fastbuild/bin/pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test_/fakedist-vec-off_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/select_for_share:182: error while processing
    logic.go:4672: /mnt/engflow/worker/work/2/exec/bazel-out/k8-fastbuild/bin/pkg/sql/logictest/tests/fakedist-vec-off/fakedist-vec-off_test_/fakedist-vec-off_test.runfiles/com_github_cockroachdb_cockroach/pkg/sql/logictest/testdata/logic_test/select_for_share:182: too many errors encountered, skipping the rest of the input
    panic.go:615: -- test log scope end --
test logs left over in: outputs.zip/logTestLogic_select_for_share2530558074
--- FAIL: TestLogic_select_for_share (47.37s)

Parameters:

• attempt=1
• run=1
• shard=12

Help

See also: How To Investigate a Go Test Failure (internal)

This test on roachdash | Improve this report!

Jira issue: CRDB-62906

[mw5h]

/investigate

[github-actions]

Investigation: TestLogic_select_for_share (fakedist-vec-off)

Investigated failure: #168339
Failure SHA: 0d45639df842f24573ea3224e532abe0b9338783
Confidence: moderate

What This Test Does

The select_for_share logic test validates shared lock acquisition behavior under SELECT ... FOR SHARE. It creates multiple sessions, acquires shared locks on table t, and verifies the lock state via crdb_internal.cluster_locks. The failing assertion at line 175 expects exactly 2 shared lock rows on test.public.t, with no other locks visible.

Where the Failure Occurs

The test fails at select_for_share:175 in the fakedist-vec-off config. The query:

SELECT database_name, schema_name, table_name, lock_key_pretty, lock_strength, durability, isolation_level, granted, contended
FROM crdb_internal.cluster_locks

Expected 2 rows (shared locks on test.public.t), but found 6 rows — the expected 2 plus 4 additional Intent/Replicated locks on system.role_members:

system  ·  role_members  /Table/23/1/"admin"/"root"/0    Intent  Replicated  SERIALIZABLE  true  false
system  ·  role_members  /Table/23/1/"admin"/"root"/3/1  Intent  Replicated  SERIALIZABLE  true  false
system  ·  role_members  /Table/23/1/"admin"/"root"/4/1  Intent  Replicated  SERIALIZABLE  true  false
system  ·  role_members  /Table/23/1/"admin"/"root"/5/1  Intent  Replicated  SERIALIZABLE  true  false

The retry option retried the query for ~45 seconds (test started at 16:31:19, failed at 16:33:06) but the extra intents persisted throughout.

Analysis

Hypothesis: Virtual Intent Resolution (VIR) prevents timely cleanup of bootstrap intents
Confidence: moderate

The extra locks are unresolved MVCC intents from the cluster bootstrap addRootToAdminRole() function in permanent_upgrades.go:132-159, which UPSERTs root into the admin role in system.role_members. The key /Table/23/1/"admin"/"root"/0 and its secondary index keys match this operation exactly.

The bootstrap transaction commits during server initialization, but its MVCC intents need asynchronous resolution. Normally, when a subsequent read encounters these intents, it pushes the (committed) transaction and triggers intent resolution, which removes them from both the MVCC layer and the in-memory lock table.

However, this test run had the metamorphic constant kv.concurrency.virtual_intent_resolution.enabled = true (see test.log line 34). VIR was re-enabled metamorphically in commit 65f1ec4c93f (March 18, 2026). With VIR enabled:

1. Reads bypass intents without physical resolution. When a read-only request encounters an intent from a committed transaction, VIR evaluates the intent resolution in an in-memory batch that is never committed (replica_read.go:94-171). The read proceeds, but the physical MVCC intent remains.

2. The lock table retains the entry. VIR calls AddReplicatedToResolveAndSignal (lock_table.go:1020-1031), which removes the reader from the lock's waiter list but does NOT remove the lock itself from the lock table.

3. QueryLocks still reports the intent. The crdb_internal.cluster_locks virtual table uses QueryLocksRequest → QueryLockTableState (cmd_query_locks.go:62) which iterates all locks in the in-memory lock table. Since VIR doesn't remove the lock entry, the intent continues to be reported.

4. No background cleanup is triggered. There is no mechanism for VIR to schedule asynchronous physical intent resolution after virtual resolution. The intents persist until some other mechanism (e.g., a write conflict, GC, or non-VIR read) triggers physical resolution.

5. The retry option cannot help. The test's retry option retries the query via testutils.SucceedsWithinError, but each retry also encounters the same unresolved intents in the lock table. The 45-second retry window is insufficient because nothing is actively driving their resolution.

Why this is specific to fakedist-vec-off: The fakedist configs use 3 nodes with a fake span resolver. The role_members range's leaseholder has the intents in its lock table. In a local (single-node) config, the bootstrap intents may be resolved more quickly because reads and writes are co-located, but in a multi-node config, there may be fewer incidental reads on the role_members range that would trigger resolution.

Recommended fix: The test should filter crdb_internal.cluster_locks to only show locks on the test database or specifically on table_name = 't', rather than querying all locks cluster-wide. This would make it immune to background intents on system tables. Alternatively, a WHERE database_name = 'test' filter could be added.

Related Issues and PRs

• #167896 (open): Similar select_for_share test failure in local-repeatable-read config, but with a different failure mode (mixed Replicated/Unreplicated durability due to connection pool race with write buffering session variables). Different root cause.
• #167458 / 3371b8c: Recent deflake of a different section of select_for_share (SKIP LOCKED section), fixed by forcing intent resolution with a SELECT before the SKIP LOCKED scan. Different test section, different root cause.
• #163943: VIR feature tracking issue; VIR was re-enabled metamorphically on March 18, 2026.
• 65f1ec4c93f: Commit that re-enabled VIR metamorphically in tests.

Timeline

• 2026-03-18: VIR re-enabled metamorphically in commit 65f1ec4c93f.
• 2026-04-06: Commit 3371b8c3285 deflakes the SKIP LOCKED section of select_for_share (different section).
• 2026-04-14: This failure occurs at SHA 0d45639df84 with kv.concurrency.virtual_intent_resolution.enabled = true.

Detailed evidence and investigation notes

Log Excerpts

From test.log (EngFlow shard 12), key metamorphic constants:

initialized metamorphic constant "kv.concurrency.virtual_intent_resolution.enabled" with value true
initialized metamorphic constant "kv.transaction.write_buffering.enabled" with value false
initialized metamorphic constant "max-intents-in-flight-per-caller" with value 153

Failure output:

select_for_share:175: SELECT database_name, schema_name, table_name, lock_key_pretty, lock_strength, durability, isolation_level, granted, contended FROM crdb_internal.cluster_locks
expected:
    database_name  schema_name  table_name  lock_key_pretty   lock_strength  durability    isolation_level  granted  contended
    test           public       t           /Table/106/1/2/0  Shared         Unreplicated  SERIALIZABLE     true     false
    test           public       t           /Table/106/1/2/0  Shared         Unreplicated  SERIALIZABLE     true     false

but found (query options: "colnames,retry,rowsort" -> ignore the following ordering of rows) :
    database_name  schema_name  table_name    lock_key_pretty                 lock_strength  durability    isolation_level  granted  contended
    system         ·            role_members  /Table/23/1/"admin"/"root"/0    Intent         Replicated    SERIALIZABLE     true     false
    system         ·            role_members  /Table/23/1/"admin"/"root"/3/1  Intent         Replicated    SERIALIZABLE     true     false
    system         ·            role_members  /Table/23/1/"admin"/"root"/4/1  Intent         Replicated    SERIALIZABLE     true     false
    system         ·            role_members  /Table/23/1/"admin"/"root"/5/1  Intent         Replicated    SERIALIZABLE     true     false
    test           public       t             /Table/106/1/2/0                Shared         Unreplicated  SERIALIZABLE     true     false
    test           public       t             /Table/106/1/2/0                Shared         Unreplicated  SERIALIZABLE     true     false

Code References

• Bootstrap UPSERT to role_members: permanent_upgrades.go:152-158 — UPSERT INTO system.role_members ("role", "member", "isAdmin", role_id, member_id) VALUES ($1, $2, true, $3, $4) with $1=admin, $2=root.
• VIR setting: concurrency_manager.go:172 — metamorphic.ConstantWithTestBool("kv.concurrency.virtual_intent_resolution.enabled", false).
• VIR virtual resolution in batch: replica_read.go:94-171 — creates an in-memory batch for intent resolution that is never committed.
• VIR does not remove lock from lock table: lock_table.go:1020-1031 — AddReplicatedToResolveAndSignal removes the reader from waiters but leaves the lock entry.
• QueryLocks reads lock table: cmd_query_locks.go:62 — concurrencyManager.QueryLockTableState(ctx, args.Span(), opts).
• Test file: select_for_share:174-180 — the failing query with colnames,retry,rowsort options.

Things Ruled Out

1. Test setup causing the intents: The test's GRANT ALL ON t TO testuser and CREATE USER testuser2 at lines 9-12 don't touch role_members for the admin/root key. They modify system.privileges or system.users.

2. Connection pool race (as in pkg/ccl/logictestccl/tests/local-repeatable-read/local-repeatable-read_test: TestRepeatableReadLogic_select_for_share_write_buffering failed #167896): That issue involves mixed Replicated/Unreplicated durability for the same lock key. This failure shows extra locks from a completely different table (system.role_members), which is a different failure mode.

3. max-intents-in-flight-per-caller serialization: The metamorphic value was 153 (not 1), so this is unlikely to significantly delay intent resolution in this case.

4. Write buffering: kv.transaction.write_buffering.enabled = false for this run, so write buffering is not a factor.

Other Failure Occurrences

This is the only occurrence of this specific failure mode (extra role_members intents). Previous select_for_share failures involved different root causes:

• SKIP LOCKED intent interaction (pkg/ccl/logictestccl/tests/local-repeatable-read/local-repeatable-read_test: TestRepeatableReadLogic_select_for_share failed #167458, fixed)
• Write buffering session variable connection pool race (pkg/ccl/logictestccl/tests/local-repeatable-read/local-repeatable-read_test: TestRepeatableReadLogic_select_for_share_write_buffering failed #167896, open)
• Data race in sqlstats (sqlstats: data race #121593, closed, different test)

Tooling Feedback

No issues with available tooling. EngFlow artifact download worked smoothly.

---

Investigation triggered by: https://github.com/cockroachdb/cockroach/actions/runs/24460962501

---

Was this investigation helpful? Leave a 👍 or 👎 on this comment.

[michae2]

Sounds like fallout from #163943, moving to KV.