cockroachdb / cockroachdb/cockroach

sql: UPDATE ... RETURNING can report a stale value under READ COMMITTED

Offen
#175,497 1 Kommentar 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
A-read-committed branch-master C-bug O-agent T-sql-queries
Vorherrschende Sprache
Go
Sterne
32.5k
Forks
4.1k
PR-Merge-Kennzahlen
PR-Kennzahlen ausstehend

Beschreibung

**Describe the problem**

Under READ COMMITTED, `UPDATE ... RETURNING` can return a value that was already stale at the statement's commit timestamp, describing a row version that never existed.

This happens when the returned column is in a different column family than the columns being written. The row updater writes only the families holding updated columns, so a concurrent write to another family causes no conflict, the statement is never forced to re-read, and `RETURNING` reports the value it read at the start.

**To Reproduce**

```sql
CREATE TABLE t (
id INT PRIMARY KEY,
a STRING,
b STRING,
FAMILY f0 (id, a),
FAMILY fb (b)
);
INSERT INTO t VALUES (1, 'A', 'x1');
```

Session 1. The `pg_sleep` widens the window between the read and the write; disabling implicit `FOR UPDATE` stands in for losing the mutation's best-effort unreplicated lock.

```sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET LOCAL enable_implicit_select_for_update = off;
UPDATE t SET a = 'B' WHERE id = 1 AND pg_sleep(2) RETURNING b;
COMMIT;
```

Session 2, during session 1's sleep. It writes only family `fb`, so it is not blocked.

```sql
UPDATE t SET b = 'x2' WHERE id = 1;
```

Session 1 returns `b = x1`. The committed row is `a = 'B', b = 'x2'`. The row went `(A, x1)` -> `(A, x2)` -> `(B, x2)`; the version session 1 reported was never committed.

**Expected behavior**

`RETURNING` should describe the row as the statement left it. PostgreSQL re-reads and re-evaluates under READ COMMITTED (EvalPlanQual) and returns `x2`. CockroachDB returns the right value whenever the two statements conflict and the statement retries — the anomaly is specific to the concurrent write landing in a family the statement does not touch.

**Additional context**

`enable_implicit_select_for_update = off` only makes the window reliable. The exposure exists with implicit locking on, because the lock guarding the read is unreplicated and best-effort: a range split, lease transfer, range merge, or lock-table memory pressure can drop it.

Applications that use `RETURNING` to learn the post-update row state — optimistic concurrency tokens, audit trails — can persist a value that disagrees with a subsequent `SELECT`, with no error raised.

Same underlying mechanism as #173610, which reports it corrupting secondary indexes.

Jira issue: CRDB-68416

Beitragsleitfaden

Beitragsleitfaden öffnen

Bewertung

Dieses Issue wurde noch nicht bewertet.

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.