cockroachdb / cockroachdb/cockroach

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

Đang mở
#175,497 1 bình luận 0 reaction 0 người được giao Xem trên GitHub
A-read-committed branch-master C-bug O-agent T-sql-queries
Ngôn ngữ chính
Go
Star
32.5k
Fork
4.1k
Chỉ số merge pull request
Chỉ số pull request đang chờ

Mô tả

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

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Đánh giá

Issue này chưa được đánh giá.

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.