cockroachdb / cockroachdb/cockroach
sql: UPDATE ... RETURNING can report a stale value under READ COMMITTED
- 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
Bewertung
Dieses Issue wurde noch nicht bewertet.