cockroachdb / cockroachdb/cockroach

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

Abierto
#175,497 1 comentario 0 reacciones 0 asignados Ver en GitHub
A-read-committed branch-master C-bug O-agent T-sql-queries
Lenguaje dominante
Go
Estrellas
32.5k
Forks
4.1k
Métricas de merge de PR
Métricas de PR pendientes

Descripción

**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

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.