cockroachdb / cockroachdb/cockroach

Partial index corruption under READ COMMITTED on multi-column-family tables

Open
#173,610 1 comment 0 reactions 1 assignee Claimed by @michae2 View on GitHub
A-read-committed branch-release-24.1 branch-release-24.2 branch-release-24.3 branch-release-25.2 branch-release-25.3 branch-release-25.4 branch-release-26.1 branch-release-26.2 branch-release-26.3 branch-release-26.4 C-bug P-1 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Consider the following table, which has the shape of `system.jobs`: the partial index predicate column (`status`) and the indexed column (`claim`) live in different column families.

```sql
CREATE TABLE t (
id INT PRIMARY KEY,
status STRING NOT NULL,
claim STRING,
FAMILY f_status (id, status),
FAMILY f_claim (claim),
INDEX idx (claim, status) WHERE status = 'running'
);

INSERT INTO t VALUES (1, 'paused', NULL);
```

Row `1` occupies one KV, `/Table/t/1/1/f_status`. The `f_claim` family is entirely NULL, so no KV exists for it.

And the following two transactions, each a single implicit statement:

```
TxnA (READ COMMITTED): UPDATE t SET status = 'running' WHERE id = 1;
TxnB (any isolation): UPDATE t SET claim = 's2' WHERE id = 1;
```

Neither writes a column family the other writes. TxnA writes only
`f_status`, TxnB only `f_claim`.

But TxnA must *read* `claim` since it is moving the row into the index predicate, and `claim` is the leading index column, so TxnA cannot build the index entry without it. That read is guarded by neither an intent nor a durable lock. As evidence, see the trace from TxnA:

```
[0] Scan(Exclusive,Unreplicated) [/Table/104/1/1,/Table/104/1/2), [txn: 07db8b82]
[1] Put [/Table/104/1/1/0], Put [/Table/104/2/NULL/"running"/1/0], EndTxn(commit)
```

As a result, these may execute as follows:

1. TxnA opens at read timestamp 10. Its mutation scan reads
`(id=1, status='paused', claim=NULL)` and takes a best-effort
unreplicated exclusive lock on `/Table/t/1/1/f_status`.

2. The range splits at row 1. Unreplicated locks are not carried
across a split unless
`kv.lock_table.unreplicated_lock_reliability.split.enabled` is
true. TxnA's lock is gone. A lease transfer or merge does the same
thing.

3. TxnB opens at read timestamp 20 and reads the row. It is not blocked,
because TxnA holds nothing. It sees `status='paused'`. The predicate is
false before its write and false after, so TxnB emits no index
operations at all. It writes `/Table/t/1/1/f_claim = 's2'` and commits.

4. TxnA's write batch evaluates, still holding its t1 read. It writes
`status='running'` to `/Table/t/1/1/f_status`, and inserts the index entry
keyed on the claim it read at t1:

/Table/t/idx/NULL/'running'/1

5. TxnA commits. Its write timestamp has been pushed above 20 by the
timestamp cache, because TxnB read `/Table/t/1/1/f_status`
at 20. Under SERIALIZABLE this is fatal: the write timestamp no
longer equals the read timestamp, so the transaction must refresh,
and the refresh fails on the changed claim. Under READ COMMITTED no
read refresh occurs. TxnA's writes also overlap none of TxnB's, so
no WriteTooOld fires.

The end result:

```
SELECT status, claim FROM t WHERE id = 1; --> 'running', 's2'
SELECT id, claim FROM t@idx WHERE status='running'; --> 1, NULL
```

The primary index and the partial index now disagree.

Jira issue: CRDB-66923

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.