CockroachDB事务因并发查询重试失败问题求助

Hey there, let's break down what's going on here and walk through practical fixes tailored to your scenario.

First, the root cause: CockroachDB uses Serializable isolation by default (the strictest ANSI standard level). When you run a long-running transaction that drops and recreates a table, any concurrent SELECT queries on that table create a conflict. The database tries to retry your write transaction to maintain serializability, but if there's a steady stream of reads, eventually the transaction hits its retry limit and gets aborted with ABORT_REASON_PUSHER_ABORTED—this happens because ongoing read operations keep "pushing" the write transaction until it can't retry anymore.

Since you're okay with reading "stale" data, here are targeted solutions to eliminate these errors:

1. Use Atomic Table Replacement (Best Practice) #

Instead of deleting and reinserting data in a single transaction, create a new table with fresh data, then atomically swap it with the existing table. This avoids long-running write transactions entirely, so there's no conflict with reads.

Steps to implement:

• Create a temporary table matching your target table's schema:

  CREATE TABLE new_target_table (LIKE target_table INCLUDING ALL);
  

• Insert your rebuilt data into new_target_table (this can be done outside a transaction, or in a short-lived one)
• Atomically swap the tables:

  ALTER TABLE target_table RENAME TO old_target_table;
  ALTER TABLE new_target_table RENAME TO target_table;
  

• (Optional) Asynchronously drop the old table once you confirm no reads are still accessing it:

  DROP TABLE old_target_table;
  

This swap is instant and atomic—readers will either see the old table or the new one, no partial data, and zero transaction conflicts.

2. Lower the Transaction Isolation Level #

Since you accept stale reads, you can drop the isolation level from SERIALIZABLE to READ COMMITTED for your rebuild transaction. This reduces conflict checking strictness, so concurrent reads won't trigger retries or aborts.

Set this for your rebuild transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
TRUNCATE TABLE target_table; -- Or DROP + recreate, whichever you use
INSERT INTO target_table SELECT ... FROM external_data_source;
COMMIT;

Note: READ COMMITTED ensures you only read committed data, but allows non-repeatable reads (a reader might see different results if they query twice in the same transaction). Since your rebuild is a one-time overwrite, this is totally safe for your use case.

3. Have Reads Use Historical Data (AS OF SYSTEM TIME) #

If you can't modify the rebuild logic, adjust your SELECT queries to read from a consistent historical snapshot instead of the latest data. This decouples reads from your ongoing write transaction entirely.

Modify your read queries like this:

SELECT * FROM target_table AS OF SYSTEM TIME '-15s';

The -15s means you're reading data as it existed 15 seconds ago. Adjust the time window based on how long your rebuild takes—make sure it's longer than the typical duration of your rebuild transaction.

CockroachDB's time-travel feature makes this efficient, and it eliminates all conflict between reads and your rebuild process.

4. (Last Resort) Adjust Retry Limits #

If you want to stick with the original approach temporarily, you can increase the maximum retry count for transactions. This buys more time for reads to clear, but it's a band-aid, not a long-term fix.

Set the cluster-wide retry limit (requires admin privileges):

SET CLUSTER SETTING kv.transaction.max_retries = 20; -- Default is 5

This lets your transaction retry more times before aborting, but it won't eliminate the root conflict—so you might still hit aborts if reads are constant.

For your use case, atomic table replacement is the cleanest, most reliable solution. It eliminates transaction conflicts entirely while keeping data consistent for readers. If that's not feasible, adjusting the isolation level or using historical reads are great alternatives.

内容的提问来源于stack exchange，提问作者Nikola