cockroachdb / cockroachdb/cockroach

sql: apply implicit FOR UPDATE locking for some INSERT ON CONFLICT DO UPDATE

Open
#143,111 0 comments 0 reactions 0 assignees View on GitHub
A-buffered-writes C-enhancement T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.