sql: explicit SELECT FOR UPDATE only locks index scanned by query

[nvanbenschoten]

Describe the problem

SELECT FOR UPDATE currently only places an unreplicated lock on the index being scanned by the query. This was an important optimization that allowed implicit SELECT FOR UPDATE (#45511, #50180, #50181) on a mutation's initial row scan to come for free. Without it, grabbing unreplicated locks during a mutation would have been too expensive.

However, for explicit SELECT ... FOR UPDATE statements, this is surprising and divergent from PG, which acquires a lock on all indexes. We should expand explicit SELECT ... FOR UPDATE's implementation to acquire an unreplicated lock on all of a table's indexes.

To Reproduce

Setup

create table tb02(a int,b int,c int);
create unique index ON tb02(a);
create unique index ON tb02(b);
insert into tb02 values (1,2,3);

Example

tx1                                            tx2
begin transaction;
                                               begin transaction;
select * from 
tb02@{FORCE_INDEX=tb02_a_key}  
where b=2 for update;
                                               select * from 
                                               tb02@{FORCE_INDEX=tb02_b_key}  
                                               where b=2 for update;(didnt block)
update tb02 set c=3 where b=2;
                                                update tb02 set c=3 where b=2;
                                                (blocked and  return
                                                 RETRY_WRITE_TOO_OLD when tx1 commited)
commit
                                                commit

Expected behavior

txn2's SELECT query should block, because txn1 should have acquired an UPGRADE lock on all of tb02's indexes.

Jira issue: CRDB-2879

Epic CRDB-25322

[cpporhair]

Thank you for your reply on slack and the issue, and good luck with cockroachdb

[nvanbenschoten]

We should expand explicit SELECT ... FOR UPDATE's implementation to acquire an unreplicated lock on all of a table's indexes.

Alternatively, we could change the behavior to only acquire unreplicated locks on a table's primary index. In most cases, this would be free. However, it would lead to some index-only scans now requiring index joins onto the table's primary key.

It may also undermine some of the benefit of SELECT ... FOR UPDATE, as it would mean that transactions would only queue up during an index join on a PK and not the initial index scan. Delaying the time when txns queue may cause more failed transaction refreshes.

[yuzefovich]

@nvanbenschoten what's the urgency on this issue?

[rytaft]

Moving this to the backlog for now, @nvanbenschoten please let us know if there is urgency on this. Thanks!

[nvanbenschoten]

I'm not aware of any strong urgency, but this is a bug that users have noticed which can lead to surprising behavior. I think #75457 is higher priority.

[michae2]

This is fixed in 23.2 when using the new SFU implementation (optimizer_use_lock_op_for_serializable).