Cockroach: sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`

[rafiss]

6583 and #40206 added support for SELECT FOR UPDATE. There is additional syntax to support: SKIP LOCKED and NOWAIT. See an example of the syntax here: https://www.postgresql.org/docs/current/sql-select.html

[nvanbenschoten]

This rest of this was addressed by #43868.

[rafiss]

@nvanbenschoten thanks for adding this to the parser! It looks like #43868 adds code that references this issue (i.e. unimplementedWithIssueDetailf(40476, ...)) , so I'm going to leave this open. Though if you would prefer, we could also create a new issue and refer to that instead.

[nvanbenschoten]

Good point, thanks for re-opening. I was thinking this was now covered by #40205, but that's not the case.

[KoryNunn]

Has there been any activity towards adding NOWAIT recently?

[nvanbenschoten]

Hi @KoryNunn, unfortunately, NOWAIT is not currently on our short term roadmap. Do you mind elaborating on your use case for the functionality?

[KoryNunn]

Sure, we would like to use it to check if processing should be done on a record. If any other process has a lock on a row, it should ignore it, as another process is working on it.

Currently without NOWAIT all processes that look up a row have to wait untill the lock is released, see that processing is complete, and then skip the row.

If you have any suggestions to get equivalent functionality without NOWAIT, I'd be very happy to learn.

[nvanbenschoten]

@KoryNunn https://github.com/cockroachdb/cockroach/pull/52522 just landed, so this will make it into the next release of CockroachDB, version 20.2.

[dilyevsky]

@nvanbenschoten thanks, great to see progress with NOWAIT! Is SKIP LOCKED still in the works?

[nvanbenschoten]

@dilyevsky SKIP LOCKED is not yet planned. We're pretty hesitant to introduce it because, unlike NOWAIT, SKIP LOCKED is a direct violation of serializable isolation. This complicates the implementation and leaves a lot of ambiguity around the interaction that the option should have in relation to other functionality.