sql/opt: row-level locking and wait policy modes not propagated to index or lookup joins

[Cynthia-Lee97]

Describe the problem
I tried to use select for update nowait, but this statement got stuck. There was no result returned and no error was reported.

To Reproduce

1. Set up CockroachDB Node
2. CREATE TABLE t(a INT, b INT, c INT, INDEX id (c) STORING (b)); INSERT INTO t VALUES(1, 1, 1), (2, 2, 2),(3, 3, 3);

4. See error

Expected behavior
You should report an error instead of being stuck

Environment:

• CockroachDB version 20.2
• Server OS Linux
• Client app cockroach sql

Jira issue: CRDB-2890

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

cc @nvanbenschoten i think you might be familiar with the SFU locking behavior

[nvanbenschoten]

Thanks @rafiss. I'm taking a look.

For me and others:

CREATE TABLE t(a INT, b INT, c INT, INDEX id (c) STORING (b)); 
INSERT INTO t VALUES(1, 1, 1), (2, 2, 2),(3, 3, 3);

# terminal 1
BEGIN;
SELECT * FROM t WHERE c % 2 != 0 FOR UPDATE NOWAIT;

# terminal 2
SELECT * FROM t@id WHERE c = 2 FOR UPDATE NOWAIT;

[nvanbenschoten]

I'm able to reproduce. The issue appears to be that the index join present in the second query is not using the correct wait policy (or locking mode). So we're scanning the secondary index correctly, but not the primary index.

> EXPLAIN SELECT * FROM t@id WHERE c = 2 FOR UPDATE NOWAIT;

                 info
--------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ table: t@primary
  │
  └── • scan
        estimated row count: 1
        table: t@id
        spans: [/2 - /2]
        locking strength: for update
        locking wait policy: nowait

[nvanbenschoten]

I pushed a WIP fix for this to #60719, which likely won't land for the next release. This issue reveals an interesting change in expectations for SELECT FOR UPDATE. Before the addition of NOWAIT, it was mostly fine if locking policy propagation was best-effort because locking modes were just a performance optimization, so we didn't bother supporting it in all cases. Now that users have access to NOWAIT, they'll expect the wait policy to be propagated correctly in all cases.