cockroachdb / cockroachdb/cockroach

sql: savepoint rollback in swap mutations prevents using swap mutations in some cases

Open
#160,579 7 comments 0 reactions 0 assignees View on GitHub
branch-release-25.4 C-bug T-kv
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Swap mutations optimistically modify all indexes in a single round trip, without reading first to check if the row exists. If the row did not exist, the CPut on the primary index will fail, but writes to other indexes might have succeeded. The swap mutations currently use savepoint rollback to undo these writes to other indexes.

As @jeffswenson figured out, however, using savepoint rollbacks is a problem if the transaction tries another read at that same txn sequence point. For example:

```sql
CREATE TABLE abc (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL,
c INT NOT NULL,
UNIQUE INDEX (c)
);

INSERT INTO abc VALUES (1, 1, 1), (2, 2, 2);

SET use_swap_mutations = on;

WITH u AS (UPDATE abc SET c = 3 WHERE a = 1 AND b = 2 AND c = 1 RETURNING 1) SELECT * FROM abc;
```

This fails with:

```
ERROR: internal error: read sequence number 0 but sequence number is ignored [{0 3}] after savepoint rollback
SQLSTATE: XX000
DETAIL: stack trace:
pkg/kv/kvclient/kvcoord/txn_interceptor_seq_num_allocator.go:185: checkReadSeqNotIgnoredLocked()
pkg/kv/kvclient/kvcoord/txn_interceptor_seq_num_allocator.go:82: SendLocked()
pkg/kv/kvclient/kvcoord/txn_interceptor_heartbeater.go:266: SendLocked()
pkg/kv/kvclient/kvcoord/txn_coord_sender.go:570: Send()
pkg/kv/db.go:1189: sendUsingSender()
pkg/kv/txn.go:1374: Send()
pkg/sql/row/kv_batch_fetcher.go:286: func2()
pkg/sql/row/kv_batch_fetcher.go:662: fetch()
pkg/sql/row/kv_batch_fetcher.go:950: nextBatch()
pkg/sql/row/kv_batch_fetcher.go:817: NextBatch()
pkg/sql/row/kv_fetcher.go:321: nextKV()
pkg/sql/row/kv_fetcher.go:342: NextKV()
pkg/sql/colfetcher/cfetcher.go:776: NextBatch()
pkg/sql/colfetcher/colbatch_scan.go:240: Next()
pkg/sql/colexec/colexecutils/cancel_checker.go:54: Next()
pkg/sql/colflow/stats.go:120: next()
pkg/sql/colexecerror/error.go:162: CatchVectorizedRuntimeError()
pkg/sql/colflow/stats.go:128: Next()
pkg/sql/colflow/flow_coordinator.go:241: nextAdapter()
pkg/sql/colexecerror/error.go:162: CatchVectorizedRuntimeError()
pkg/sql/colflow/flow_coordinator.go:245: next()
pkg/sql/colflow/flow_coordinator.go:277: Run()
pkg/sql/colflow/vectorized_flow.go:317: Run()
pkg/sql/distsql_running.go:1078: Run()
pkg/sql/distsql_running.go:2344: PlanAndRun()
pkg/sql/distsql_running.go:2043: func3()
pkg/sql/distsql_running.go:2046: PlanAndRunAll()
pkg/sql/conn_executor_exec.go:3444: execWithDistSQLEngine()
pkg/sql/conn_executor_exec.go:2943: dispatchToExecutionEngine()
pkg/sql/conn_executor_exec.go:1084: execStmtInOpenState()
pkg/sql/conn_executor_exec.go:178: func2()
pkg/sql/conn_executor_exec.go:4499: execWithProfiling()
```

Jira issue: CRDB-58423

Contributor guide

Open the contributing guide

Research direction

Start with the failing sequence-number path in pkg/kv/kvclient/kvcoord/txn_interceptor_seq_num_allocator.go, especially checkReadSeqNotIgnoredLocked(), and trace how the SQL example reaches it through the listed transaction and row-fetcher files. Reproduce the CTE with use_swap_mutations enabled, then verify the query no longer returns the internal error after the swap mutation rolls back.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.