sql/opt: implicit SFU locking on Update's row fetch disabled with locality-optimized search

[nvanbenschoten]

Consider a YCSB usertable that's configured as REGIONAL BY ROW. An update statement on this table looks like:

UPDATE usertable SET field9 = $1 WHERE ycsb_key = $1

Today, this UPDATE has a plan that looks like:

• update
│ table: usertable
│ set
│ auto commit
│
└── • render
    │
    └── • union all
        │
        ├── • scan
        │     table: usertable@usertable_pkey
        │     spans: 1+ spans
        │
        └── • scan
              table: usertable@usertable_pkey
              spans: 1+ spans

Notice that the initial row fetch of the UPDATE uses locality-optimized search. That's good!

However, notice also that SFU locking is not pushed into the scans. That's because the pattern matching in shouldApplyImplicitLockingToUpdateInput which we use to power implicit SFU for UPDATES does not properly handle locality-optimized search. We should be pushing the SFU locking into each branch of the locality-optimized search.

Jira issue: CRDB-22000

Epic CRDB-25322

[mgartner]

@michae2 will this be a priority for 24.2?

[michae2]

It would be nice but probably not a priority.