cockroachdb / cockroachdb/cockroach
sql: apply implicit FOR UPDATE locking for some INSERT ON CONFLICT DO UPDATE
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Currently, when `enable_implicit_select_for_update` is `true` (which is the default) we apply implicit SFU locking during the initial scan part of DELETEs, UPDATEs, and UPSERTs. I think we could also do the same thing for some INSERT ON CONFLICT DO UPDATE - namely, when the arbiter index is the one we're scanning (ignoring the index join).
Consider the following setup:
```sql
CREATE TABLE t139160 (
k INT PRIMARY KEY,
i INT,
u INT,
v INT,
INDEX (i) STORING (u),
UNIQUE INDEX (u) STORING (i),
FAMILY (k, i, u, v)
);
INSERT INTO t139160 VALUES (1, 2, 3, 4);
INSERT INTO t139160 VALUES (11, 12, 3, 14) ON CONFLICT (u) DO UPDATE SET k = 11, i = 12, v = 14;
```
I think it should be safe to apply SFU to the scan of `t139160_u_key` (as well as the index join into the PK) because we have an DO UPDATE action and `t139160_u_key` is the arbiter index. In other words, if we scanned `t139160_u_key` and found an entry, we have conflict on the arbiter, so we'll want to perform the UPDATE.
I could see a downside doing so: depending on the SET part we might not actually update the arbiter index itself (in the example above, if we only had `SET v = 14`, then only the PK would need to be modified, so we'd acquire the lock unnecessarily)
Jira issue: CRDB-48660
Contributor guide
Assessment
This issue has not been assessed yet.