Back to Details

benchmarking-transaction-patterns

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Benchmarking Transaction Patterns

事务模式基准测试

Guides users through benchmarking, explaining, and comparing two formulations of the same transactional business workflow in CockroachDB: explicit multi-statement transactions versus single-statement CTE transactions. Focuses on performance under contention, fair test methodology, and result interpretation.
Complement to design skills: For general transaction design principles, see designing-application-transactions. For SQL syntax and query patterns, see cockroachdb-sql.
本指南引导用户在CockroachDB中对同一事务业务流程的两种实现方式——显式多语句事务与单语句CTE事务——进行基准测试、原理讲解和对比分析，重点关注高竞争场景下的性能表现、公平测试方法以及结果解读。
设计技能补充说明： 如需了解通用事务设计原则，请查看designing-application-transactions。如需了解SQL语法和查询模式，请查看cockroachdb-sql。

When to Use This Skill

适用场景

  • Comparing explicit multi-statement transactions versus CTE-based single-statement transactions
  • Benchmarking CockroachDB workloads under high concurrency or hot-key contention
  • Investigating retry pressure, p95/p99 latency, or throughput differences between transaction formulations
  • Deciding whether to rewrite a multi-step application flow into a single SQL statement
  • Setting up a fair side-by-side benchmark with proper reset discipline
  • Interpreting benchmark results (throughput, retries, tail latency, failures)
  • Explaining why SQL Activity still shows waiting even with CTE transactions
  • 对比显式多语句事务与基于CTE的单语句事务
  • 在高并发或热键竞争场景下对CockroachDB工作负载进行基准测试
  • 排查不同事务实现方式之间的重试压力、p95/p99延迟或吞吐量差异
  • 决定是否将多步骤应用流程重写为单条SQL语句
  • 设置具备合理重置规则的公平对比基准测试
  • 解读基准测试结果（吞吐量、重试次数、尾部延迟、失败次数）
  • 解释为何使用CTE事务时SQL Activity仍显示等待状态

Prerequisites

前置条件

  • CockroachDB test cluster (do not benchmark on production)
  • SQL client or JDBC driver for benchmark execution
  • Understanding of CockroachDB SERIALIZABLE isolation and retry behavior
  • Familiarity with basic concurrency testing concepts
  • CockroachDB测试集群（请勿在生产环境进行基准测试）
  • 用于执行基准测试的SQL客户端或JDBC driver
  • 了解CockroachDB的SERIALIZABLE隔离级别和重试机制
  • 熟悉基础并发测试概念

Core Concept

核心概念

When two implementations perform the same business behavior, the transaction formulation itself can be a primary performance lever under contention.
当两种实现方式执行相同业务逻辑时，事务的编写方式本身可能是高竞争场景下的主要性能影响因素。

Explicit Transaction Model

显式事务模型

The application orchestrates the workflow as separate SQL statements inside a transaction: read state, apply logic, write changes, commit.
sql
BEGIN;

SELECT balance FROM accounts WHERE id = $1;

-- Application decides whether transfer is allowed

UPDATE accounts SET balance = balance - $2 WHERE id = $1;
UPDATE accounts SET balance = balance + $2 WHERE id = $3;

INSERT INTO transfers (from_acct, to_acct, amount, created_at)
VALUES ($1, $3, $2, now());

COMMIT;
This keeps the transaction open across multiple statements and often includes application-side decision logic between steps.
应用将工作流编排为事务内的多条独立SQL语句：读取状态、执行逻辑、写入变更、提交事务。
sql
BEGIN;

SELECT balance FROM accounts WHERE id = $1;

-- 应用判断转账是否允许

UPDATE accounts SET balance = balance - $2 WHERE id = $1;
UPDATE accounts SET balance = balance + $2 WHERE id = $3;

INSERT INTO transfers (from_acct, to_acct, amount, created_at)
VALUES ($1, $3, $2, now());

COMMIT;
这种方式会在多条语句之间保持事务开启状态，且步骤之间通常包含应用端的决策逻辑。

CTE Transaction Model

CTE事务模型

The same read/decision/write logic is expressed as a single SQL statement, so the database evaluates and applies the business operation atomically without intermediate client orchestration.
sql
WITH debit AS (
  UPDATE accounts
  SET balance = balance - $2
  WHERE id = $1
    AND balance >= $2
  RETURNING id
), credit AS (
  UPDATE accounts
  SET balance = balance + $2
  WHERE id = $3
    AND EXISTS (SELECT 1 FROM debit)
  RETURNING id
), ins AS (
  INSERT INTO transfers (from_acct, to_acct, amount, created_at)
  SELECT $1, $3, $2, now()
  WHERE EXISTS (SELECT 1 FROM debit)
    AND EXISTS (SELECT 1 FROM credit)
  RETURNING id
)
SELECT id FROM ins;
将相同的读取/决策/写入逻辑整合为单条SQL语句，由数据库原子性地完成业务操作，无需客户端进行中间编排。
sql
WITH debit AS (
  UPDATE accounts
  SET balance = balance - $2
  WHERE id = $1
    AND balance >= $2
  RETURNING id
), credit AS (
  UPDATE accounts
  SET balance = balance + $2
  WHERE id = $3
    AND EXISTS (SELECT 1 FROM debit)
  RETURNING id
), ins AS (
  INSERT INTO transfers (from_acct, to_acct, amount, created_at)
  SELECT $1, $3, $2, now()
  WHERE EXISTS (SELECT 1 FROM debit)
    AND EXISTS (SELECT 1 FROM credit)
  RETURNING id
)
SELECT id FROM ins;

Why CTE Tends to Win Under Contention

为何CTE模式在高竞争场景下更具优势

The explicit version keeps the transaction open across multiple statements, increasing the time window for write conflicts, timestamp pushes, and retries. Under high concurrency, each retry repeats the read and write work and continues contending for the same hot data.
The CTE version collapses the same business logic into a single atomic statement, reducing transaction duration and sharply narrowing the contention window.
显式事务会在多条语句之间保持开启状态，增加了写入冲突、时间戳推进和重试的时间窗口。在高并发场景下，每次重试都会重复读取和写入操作，并持续竞争相同的热点数据。
CTE模式将相同业务逻辑整合为单条原子语句，缩短了事务持续时间，大幅缩小了竞争窗口。

Steps

操作步骤

1. Prepare the Benchmark Environment

1. 准备基准测试环境

Set up a dedicated test database and schema. Do not mix benchmark workloads with other traffic.
sql
CREATE DATABASE IF NOT EXISTS bankbench;
USE bankbench;

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(18,2) NOT NULL DEFAULT 0
);

CREATE TABLE transfers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_acct INT NOT NULL,
  to_acct INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
创建专用的测试数据库和 schema。请勿将基准测试工作负载与其他流量混合。
sql
CREATE DATABASE IF NOT EXISTS bankbench;
USE bankbench;

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(18,2) NOT NULL DEFAULT 0
);

CREATE TABLE transfers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_acct INT NOT NULL,
  to_acct INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

2. Seed the Test Data

2. 生成测试数据

Use multi-row UPSERT for efficient seeding. Single-row inserts distort setup cost.
sql
INSERT INTO accounts (id, balance)
SELECT generate_series(1, 10000), 1000.00
ON CONFLICT (id) DO UPDATE SET balance = 1000.00;
使用多行UPSERT高效生成数据。单行插入会扭曲初始化成本。
sql
INSERT INTO accounts (id, balance)
SELECT generate_series(1, 10000), 1000.00
ON CONFLICT (id) DO UPDATE SET balance = 1000.00;

3. Run the Explicit Transaction Benchmark

3. 运行显式事务基准测试

Execute with realistic concurrency (e.g., 64-128 workers) and a fixed duration or iteration count. Record throughput, retries, p50/p95/p99 latency, max latency, and failures.
使用真实并发数（如64-128个工作线程）执行测试，设置固定时长或迭代次数。记录吞吐量、重试次数、p50/p95/p99延迟、最大延迟和失败次数。

4. Reset Between Runs for Fair Comparison

4. 测试间重置以保证公平对比

For a fair benchmark, reset account balances between explicit and CTE runs so table size, index size, and account state remain comparable.
sql
UPDATE accounts SET balance = 1000.00;
为保证基准测试的公平性，在显式事务和CTE事务测试之间重置账户余额，确保表大小、索引大小和账户状态保持一致。
sql
UPDATE accounts SET balance = 1000.00;

5. Run the CTE Transaction Benchmark

5. 运行CTE事务基准测试

Execute with the same concurrency, duration, and parameters as the explicit run.
使用与显式事务测试相同的并发数、时长和参数执行测试。

6. Compare Results

6. 对比测试结果

Always compare these metrics side by side:
MetricWhat to Look For
Throughput (txn/s)Higher is better; CTE typically sustains better under contention
Total retriesCTE often reduces to near-zero
p50 latencyMedian transaction time
p95 latencyTail latency under moderate contention
p99 latencyWorst-case tail; explicit model often shows spikes
Max latencyOutlier behavior
FailuresNon-retryable errors
务必从以下维度进行对比：
指标关注要点
吞吐量（事务/秒）数值越高越好；CTE模式在高竞争场景下通常表现更优
总重试次数CTE模式通常可将重试次数降至接近零
p50延迟事务平均耗时
p95延迟中等竞争场景下的尾部延迟
p99延迟最坏情况尾部延迟；显式模式常出现峰值
最大延迟异常值表现
失败次数不可重试的错误次数

Benchmark Reference Results

基准测试参考结果

In a reported high-contention run comparing the two models:
MetricExplicitCTEChange
Throughput591.1 txn/s1,035.1 txn/s+75.1%
Wall time216.5s123.7s-42.9%
Average latency202.2 ms111.3 ms-45.0%
Total retries2,270,9770-100%
Extended runs preserved the same directional result at higher total volume, with the explicit model continuing to accumulate retries and occasional failures while the CTE model stayed at zero retries and zero failures.
在某高竞争场景的测试中，两种模式的对比结果如下：
指标显式事务CTE事务变化率
吞吐量591.1 事务/秒1,035.1 事务/秒+75.1%
耗时216.5秒123.7秒-42.9%
平均延迟202.2 毫秒111.3 毫秒-45.0%
总重试次数2,270,9770-100%
在更高数据量的扩展测试中，结果趋势保持一致：显式模式持续累积重试次数并偶尔出现失败，而CTE模式始终保持零重试、零失败。

Impact Summary

影响总结

DimensionExplicit Multi-StatementSingle-Statement CTE
Round tripsMultiple client/server interactionsSingle request
Transaction lifetimeLongerShorter
Client retry complexityHigherLower
Atomic invariant enforcementSpread across statements/app logicContained in SQL
Expected throughputLower under contentionHigher under contention
Client-visible retriesMore likelyOften reduced
维度显式多语句事务单语句CTE事务
往返次数多次客户端/服务器交互单次请求
事务生命周期更长更短
客户端重试复杂度更高更低
原子性约束执行分散在多条语句/应用逻辑中完全由SQL实现
预期吞吐量高竞争场景下更低高竞争场景下更高
客户端可见重试更易发生通常大幅减少

Decision Guidance

决策指导

Prefer the Explicit Pattern When

优先选择显式模式的场景

  • The business workflow truly cannot be expressed cleanly in one SQL statement
  • Readability or staged business logic matters more than peak throughput
  • The contention level is low enough that retry amplification is not the dominant cost
  • 业务工作流确实无法用单条SQL语句清晰表达
  • 可读性或分步业务逻辑比峰值吞吐量更重要
  • 竞争程度较低，重试放大不是主要成本

Prefer the CTE Pattern When

优先选择CTE模式的场景

  • The workflow is contention-heavy
  • The operation is naturally atomic
  • The application currently performs read-decide-write across multiple statements
  • The main goal is higher throughput, lower retries, and more stable p95/p99 latency
  • 工作流存在高竞争
  • 操作天然具备原子性
  • 应用当前通过多条语句执行读取-决策-写入操作
  • 主要目标是提升吞吐量、减少重试次数并获得更稳定的p95/p99延迟

Fair Benchmark Rules

公平基准测试规则

  1. Reset between runs for fair comparison so balances, table size, and index size stay consistent
  2. Treat no-reset runs as a demo, not an apples-to-apples benchmark
  3. Use 
    --batch-size=1
     when you want one business unit of work at a time for clean comparison
  4. Compare the right metrics — always include throughput, retries, p50, p95, p99, max latency, and failures
  5. Use multi-row UPSERT for seeding — single-row seeding distorts setup cost
  1. 测试间重置数据：保证余额、表大小和索引大小一致，确保公平对比
  2. 无重置测试仅作演示：不能作为严格的同类对比基准
  3. 使用
    --batch-size=1
    ：如需每次执行一个业务单元以保证对比清晰
  4. 对比正确的指标：务必包含吞吐量、重试次数、p50、p95、p99、最大延迟和失败次数
  5. 使用多行UPSERT生成数据：单行生成数据会扭曲初始化成本

Common Misconceptions

常见误区

"CTE always wins in every workload" — No. The claim is narrower: when the same business workflow can be expressed as a single atomic statement and the workload is contention-sensitive, collapsing the transaction shape can materially improve performance and stability.
"SQL Activity showing waiting means CTE failed" — Single-statement CTE execution does not eliminate contention. Statements can still wait on row conflicts, write intents, latches, or scheduling. The right comparison is overall throughput, tail latency, and retry profile.
"Single-statement means no contention" — A CTE can still wait under contention. The benefit is a narrower contention window, not the elimination of contention.
"CTE模式在所有工作负载中都更优"——错误。正确结论是：当相同业务工作流可表达为单条原子语句且工作负载对竞争敏感时，优化事务结构可显著提升性能和稳定性。
"SQL Activity显示等待意味着CTE模式失效"——单语句CTE执行并不能消除竞争。语句仍可能因行冲突、写入意向、锁或调度等待。正确的对比维度是整体吞吐量、尾部延迟和重试情况。
"单语句意味着无竞争"——CTE模式在高竞争场景下仍可能出现等待。其优势在于缩小了竞争窗口，而非消除竞争。

Safety Considerations

注意事项

  • Run benchmarks on dedicated test clusters, not production
  • Reset data between runs for fair comparison
  • Monitor cluster health during benchmark execution
  • Use realistic but not destructive concurrency levels
  • Validate that benchmark results transfer to your specific workload before making production changes
  • 在专用测试集群运行基准测试，请勿在生产环境执行
  • 测试间重置数据以保证公平对比
  • 基准测试执行期间监控集群健康状态
  • 使用真实但无破坏性的并发级别
  • 在生产环境做出变更前，验证基准测试结果是否适用于您的特定工作负载

References

参考资料