kv: skip locked does not work correctly with shared locks

[arulajmani]

Describe the problem

To reproduce:

-- Session 1
demo@localhost:26257/demoapp/defaultdb> CREATE TABLE t(a INT PRIMARY KEY);
CREATE TABLE

Time: 6ms total (execution 6ms / network 0ms)

demo@localhost:26257/demoapp/defaultdb> INSERT INTO t VALUES(1);
INSERT 0 1

Time: 8ms total (execution 8ms / network 0ms)

demo@localhost:26257/demoapp/defaultdb> BEGIN;
BEGIN

Time: 0ms total (execution 0ms / network 0ms)

demo@localhost:26257/demoapp/defaultdb  OPEN> SELECT * FROM t FOR SHARE;
  a
-----
  1
(1 row)

Time: 3ms total (execution 3ms / network 0ms)

demo@localhost:26257/demoapp/defaultdb  OPEN>

--- Session 2

demo@127.0.0.1:26257/demoapp/defaultdb> BEGIN;
BEGIN

Time: 0ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/demoapp/defaultdb  OPEN> SELECT * FROM t FOR SHARE SKIP LOCKED;
  a
-----
(0 rows)

Time: 1ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/demoapp/defaultdb  OPEN>

The SKIP LOCKED select should return (and lock) the only row in the table; it does not.

This is because we're not plumbing lock strengths in pebbleMVCCScanner -- we're instead deriving the lock strength from the failOnMoreRecent option; we infer it to be lock.Exclusive.

Jira issue: CRDB-31588

Epic CRDB-34172

[nvanbenschoten]

https://github.com/nvanbenschoten/cockroach/commits/nvanbenschoten/skipLockedSharedRepl contains a prototype of the fix for this issue.

[michae2]

After talking with @arulajmani and @nvanbenschoten, removing the GA-blocker label.

[michae2]

The limitation to document here is that FOR SHARE SKIP LOCKED is not yet supported (because it will incorrectly skip over shared locks that could be acquired).