cockroachdb / cockroachdb/cockroach
Partial index corruption under READ COMMITTED on multi-column-family tables
- 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
Assessment
This issue has not been assessed yet.