sql: provide optimized single-request, CAS-style UPDATE

[ajwerner]

Is your feature request related to a problem? Please describe.

In some distributed storage systems like HashiCorp Consul, there is support for CAS operations (see https://www.consul.io/commands/kv/put#cas). In that system, the operation is atomic and happens in a single command.

In cockroach, one can express a CAS put as an UPDATE where all of the old and new columns are implied and constrained to a single row. If the table had no unique secondary indexes, one could imagine turning the operation into a single batch containing CPut operations and commit in that same batch. If there is a failure, the execution engine could interpret it to mean that the update affected zero rows.

In today's cockroach, this operation is a SELECT FOR UPDATE followed by a the operations to update the row. This is ultimately two round-trips instead of one.

> create table kv (k string primary key, val jsonb);
CREATE TABLE
> insert into kv values ('a', '{"foo": "bar"}');
INSERT 0 1
> set tracing = kv;
SET TRACING
> update kv set val = '{"foo": "baz"}' where k = 'a' and val = '{"foo": "bar"}';
UPDATE 1
> select message from [ show kv trace for session] where operation like  '%send%' and message like '%batch%';
                      message
---------------------------------------------------
  r82: sending batch 1 Get to (n1,s1):1
  r82: sending batch 1 Put, 1 EndTxn to (n1,s1):1

Describe the solution you'd like

It'd be cool to have a way to do CAS updates as a single round-trip when all of the conditions properly align. Ideally we'd just have a single batch with a single r82: sending batch 1 CPut, 1 EndTxn to (n1,s1):1.

Jira issue: CRDB-18191

[ajwerner]

h/t to @vilterp for the suggestion/feature request.

[yuzefovich]

We're already doing this with the UPSERT - how is this different? Is that the difference in behavior when the row is not present?

[ajwerner]

If I understand correctly, the problem with our UPSERT is it doesn't give the client the ability to check the expectation. UPSERT is a blind write. Maybe I'm missing something. We want a mechanism that exposes a compare-and-swap operation in a single KV round-trip.

[michae2]

We also talked about a CAS-like operation when exploring ways to optimize update statements under read committed isolation in this document.