cockroachdb / cockroachdb/cockroach
sql: primary index corruption under READ COMMITTED via the DELETE range fast path on multi-column-family tables
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
A sibling of #173610. Same root cause — a best-effort unreplicated lock lost under weak isolation while a column family transitions from absent to present — but it corrupts the **primary** index rather than a secondary one, and needs no secondary index at all.
```sql
CREATE TABLE t (
id INT PRIMARY KEY,
biz STRING NOT NULL,
claim STRING,
FAMILY f0 (id),
FAMILY fbiz (biz),
FAMILY fclaim (claim)
);
INSERT INTO t (id, biz) VALUES (1, 'b1'); -- claim is NULL, so no KV exists for fclaim
```
The table has a single index (the primary), so `DELETE` uses the range fast path (a KV `DeleteRange`); see `canUseDeleteRange` in `pkg/sql/opt/exec/execbuilder/mutation.go`.
```
TxnA (READ COMMITTED): UPDATE t SET claim = 'c2' WHERE id = 1;
TxnB (any isolation): DELETE FROM t WHERE id = 1;
```
1. TxnA reads the row and derives a `Put` for the previously-absent `fclaim` family. That read is guarded only by a best-effort unreplicated lock.
2. The lock is dropped — a range split, lease transfer, or merge, none of which carry unreplicated locks unless `kv.lock_table.unreplicated_lock_reliability.*` is enabled.
3. TxnB deletes the row via the fast path. `DeleteRange` scans the *present* family KVs and deletes them (`f0`, `fbiz`); it writes nothing at the absent `fclaim` key.
4. TxnA's `Put(fclaim='c2')` evaluates at its original timestamp. Because `DeleteRange` left no version at the `fclaim` key, there is no `WriteTooOld`, and under READ COMMITTED there is no read refresh — so the `Put` survives the delete.
The row is left holding only `fclaim = 'c2'`; the sentinel (`f0`) and the `NOT NULL` `fbiz` family are gone. A later scan of the primary index (e.g. `AUTO CREATE STATS` / `ANALYZE`) then fails:
```
internal error: Non-nullable column "t:biz" with no value! Index scanned was "t_pkey" ...
```
This is specific to READ COMMITTED and REPEATABLE READ. Under SERIALIZABLE, TxnA's read refresh fails when TxnB deletes the row, forcing a retry, and no corruption occurs.
**Why the row-by-row DELETE path is not affected:** `Deleter.DeleteRow` (`pkg/sql/row/deleter.go`) issues a `Del` for *every* family key via `ForeachFamily`, regardless of whether the family currently holds a value. That tombstone at the `fclaim` key conflicts with TxnA's `Put`, serializing the two. The row-by-row path is used whenever the table has any secondary index; only the single-index fast path (`DeleteRange`) skips the absent family and exposes this bug.
The fix for #173610 (forcing conflict writes for secondary-index maintenance reads) does not cover this case: the fast path runs only on tables with no secondary index, so there is no index maintenance to force a write for.
Jira issue: CRDB-68435
Contributor guide
Research direction
Start with canUseDeleteRange in pkg/sql/opt/exec/execbuilder/mutation.go and compare it with Deleter.DeleteRow in pkg/sql/row/deleter.go. Run the provided READ COMMITTED UPDATE/DELETE sequence on the multi-column-family table, then trace the primary-index scan failure. Done means the delete and concurrent update no longer leave an incomplete primary row, with the regression covered by a test.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100