sql: explicit SELECT FOR UPDATE only locks index scanned by query #57031
Description
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
Metadata
Metadata
Assignees
Labels
Type
Projects
Status