sql/opt/exec: perform implicit SELECT FOR UPDATE on UPDATE with index join

[nvanbenschoten]

We should be able to use implicit row-level locking on UPDATEs with an index-join in its initial row scan like the following, which perform no gateway-side filtering:

--- Schema
CREATE TABLE t (
       id INT8 NOT NULL DEFAULT unique_rowid(),
       instance_id INT8 NULL,
       is_active INT8 NULL,
       created_at TIMESTAMP NOT NULL,
       updated_at TIMESTAMP NOT NULL,
       CONSTRAINT "primary" PRIMARY KEY (id ASC),
       INDEX idx (instance_id ASC, is_active ASC)
);

--- Update
EXPLAIN UPDATE t SET is_active = 0, updated_at = now() WHERE (is_active = 1) AND (instance_id = 15);

            tree            |    field    |      description
----------------------------+-------------+------------------------
                            | distributed | false
                            | vectorized  | false
  count                     |             |
   └── update               |             |
        │                   | table       | t
        │                   | set         | is_active, updated_at
        │                   | strategy    | updater
        │                   | auto commit |
        └── render          |             |
             └── index-join |             |
                  │         | table       | t@primary
                  │         | key columns | id
                  └── scan  |             |
                            | table       | t@idx
                            | spans       | /15/1-/15/2

Currently, we only match on the following patterns:

cockroach/pkg/sql/opt/exec/execbuilder/mutation.go

Lines 793 to 812 in 9c72e72

	// Try to match the pattern:
	//
	// (Update
	// $input:(Scan $scanPrivate:*)
	// $checks:*
	// $mutationPrivate:*
	// )
	//
	// Or
	//
	// (Update
	// $input:(Project
	// (Scan $scanPrivate:*)
	// $projections:*
	// $passthrough:*
	// )
	// $checks:*
	// $mutationPrivate:*
	// )
	//

[andy-kimball]

If we want to match increasingly complex patterns, we'll probably want to fold the matching and analysis into the explorer rather than execbuilder. When I first thought about doing this, I was worried about the cost of recreating the Update subtree just to set the locking bit. Opt trees are immutable, so setting a locking flag on an input Scan operator requires a new Scan operator to be created.

A different idea which lets us integrate with the explorer but avoids the cost of recreating subtrees is to put a locking flag in the mutation operator. This flag would mean, "lock every scan in the scope of this subtree". When the execbuilder sees this flag, it'd set forceForUpdateLocking like it does today. The only difference would be that the code to decide when to set that flag would live in the optimizer rather than in execbuilder, which makes for better packaging, since we'd rather not do complex analysis in execbuilder.

[andy-kimball]

All that said, I'm not opposed to adding this new pattern directly to execbuilder for 20.1.