designing-application-transactions

设计应用事务

指导应用开发者了解在 CockroachDB 上构建正确、高性能且有弹性的应用所需的设计原则和实现模式。内容涵盖从事务范围和重试逻辑到连接池与可观测性的各个方面。

SQL 技能的补充： 有关 SQL 语法、架构设计和查询优化，请参阅 cockroachdb-sql。有关在竞争条件下对事务写法进行基准测试，请参阅 benchmarking-transaction-patterns。

何时使用此技能

• 为 CockroachDB 应用设计事务边界
• 实现带指数退避的客户端重试逻辑
• 决定使用隐式事务还是显式事务
• 选择乐观并发控制还是悲观并发控制
• 用原子 SQL 替代读取-修改-写入循环
• 配置连接池（HikariCP、pgbouncer 等）
• 使用键集分页替代 OFFSET/LIMIT
• 在报告和分析查询中使用跟随者读取
• 将业务编排与数据库事务分离
• 使用预处理语句提升性能和安全性
• 选择显式列投影，而非 SELECT *
• 测试并发情况下的应用行为
• 监控应用级数据库性能

前置条件

• 熟悉 CockroachDB 的 SERIALIZABLE 隔离级别
• 理解 ACID 事务语义
• 能够访问应用源代码以修改事务设计
• 可连接 CockroachDB 集群的 SQL 连接（用于测试和验证）

步骤

1. 保持事务生命周期短

事务只应包含完成一次原子状态变更所需的最少 SQL 操作。不要在 CockroachDB 事务中加入远程 API 调用、服务间请求、循环、开销较大的计算或人为等待。

在 CockroachDB 的分布式乐观并发架构中，长生命周期事务会延长意向锁的持有时间，增加竞争和重试概率。

反模式：

@Transactional
public void createOrder(Order order) {
    orderRepository.save(order);
    paymentGateway.charge(order); // external call inside TX
}

正确做法——拆分逻辑：

@Transactional
public void createOrderRecord(Order order) {
    orderRepository.save(order);
}

// Outside the transaction
paymentGateway.charge(order);

原因：

• 活跃的意向锁会阻塞并发写入者，降低集群吞吐量
• 竞争事务更可能遇到 40001 重试错误
• 重试的事务中执行的外部操作可能运行两次，导致副作用重复
• 长事务会占用连接和内存，降低并发能力

2. 单条语句使用隐式事务

CockroachDB 会在自动提交模式下自动将每条 SQL 语句包装为一个事务。对于单条 INSERT、UPDATE、DELETE 或 SELECT 语句，不要使用显式 BEGIN/COMMIT 包装。

推荐：

INSERT INTO orders (id, status)
VALUES (gen_random_uuid(), 'open');

避免：

BEGIN;
INSERT INTO orders (id, status)
VALUES (gen_random_uuid(), 'open');
COMMIT;

优点： 代码路径更简单、延迟更低（往返次数更少）、资源占用更少，而且由于 CockroachDB 更容易自动重试单语句事务，因此需要处理的重试问题也更少。

3. 对成组语句使用显式事务并处理重试

当多个 SQL 操作必须同时成功或失败时，请使用 BEGIN/COMMIT 显式开启事务。由于 CockroachDB 默认采用 SERIALIZABLE 隔离级别，在发生争用时，事务重试是正确执行过程中的常见情况。

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

使用指数退避的客户端重试循环：

import random
import time

def execute_with_retry(conn, txn_logic):
    backoff = 0.1
    while True:
        try:
            with conn.transaction() as txn:
                txn_logic(txn)
            return
        except SerializationFailure:
            time.sleep(backoff + random.uniform(0, 0.1))
            backoff = min(backoff * 2, 2.0)

使用 cockroach_restart 保存点协议进行高级重试：

BEGIN;
SAVEPOINT cockroach_restart;
-- transactional work
RELEASE SAVEPOINT cockroach_restart;
COMMIT;

警告：通用保存点不能用作重试机制。 CockroachDB 会在发生 40001 序列化失败时中止整个事务。对常规保存点使用 ROLLBACK TO SAVEPOINT 无法恢复事务——事务仍会处于中止状态。仅支持特殊的 SAVEPOINT cockroach_restart 协议（客户端捕获错误、回滚到保存点并重新执行操作）。对于大多数应用，重试整个事务的循环更简单，也更值得推荐。

SQLSTATE 指南：

代码	含义	操作
40001	序列化失败／可重试	使用退避和抖动重试整个工作单元
40003	结果不明确／提交状态不确定	不要盲目重放非幂等操作
08xx / 57xx	网络或服务器暂时性问题	谨慎重试，并考虑提交状态不明确的情况
23xxx	约束和应用错误	通常不应重试

4. 在适用时将事务标记为只读

只读事务仅执行检索，不进行写入。将事务标记为只读，可让 CockroachDB 避免不必要的写入意向、减少与写入操作的争用，并启用跟随者读取或有界陈旧读取。

BEGIN;
SET TRANSACTION READ ONLY;
SELECT * FROM customers WHERE region = 'US-East';
COMMIT;

5. 将不变量下推到 SQL 中——避免读-改-写循环

不要将状态提取到应用代码中，在内存中修改后再写回。应优先使用原子 SQL、约束、带条件的 UPDATE、UPSERT、INSERT ... ON CONFLICT 和基于 CTE 的变更。

反面模式：

balance = db.fetch("SELECT balance FROM accounts WHERE id = 123")
balance += 100
db.execute("UPDATE accounts SET balance = %s WHERE id = 123", (balance,))

推荐的原子 SQL：

UPDATE accounts
SET balance = balance + 100
WHERE id = 123;

强制执行不变量的条件写入：

UPDATE customer_daily_limits
SET used_total = used_total + $2
WHERE customer_id = $1
  AND day = current_date
  AND used_total + $2 <= daily_limit;

原子 CTE 模式：

WITH limit_row AS (
  SELECT customer_id, day
  FROM customer_daily_limits
  WHERE customer_id = $1 AND day = current_date
  FOR UPDATE
), spend AS (
  UPDATE customer_daily_limits AS l
  SET remaining_limit = l.remaining_limit - $2,
      used_total = l.used_total + $2
  FROM limit_row
  WHERE l.customer_id = limit_row.customer_id
    AND l.day = limit_row.day
    AND l.remaining_limit >= $2
  RETURNING l.customer_id, l.day
), ins AS (
  INSERT INTO transfers (customer_id, amount, direction, created_at)
  SELECT $1, $2, 'debit', now()
  FROM spend
  RETURNING id AS transfer_id
)
SELECT transfer_id FROM ins;

主要方法：

• 使用原子更新：UPDATE ... SET col = col + 1
• 在 WHERE 子句中使用版本或时间戳检查，实现乐观并发控制
• 使用 UNIQUE、CHECK、NOT NULL 和 FOREIGN KEY 约束强制执行业务规则
• 使用 UPSERT 或 INSERT ... ON CONFLICT，而不是先读取再检查是否存在
• 使用 CTE 保持多步骤逻辑的原子性

6. 有选择地使用 SELECT ... FOR UPDATE

CockroachDB 默认采用乐观并发控制，适用于大多数工作负载。对于热点行或争用严重的先读后写路径，SELECT ... FOR UPDATE 可让竞争事务等待而不是相互争抢，从而减少重试带来的开销。

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

适用于以下情况：

• 多个并发事务频繁更新相同的行
• 乐观重试导致反复争抢
• 写入前必须保证一致性（库存、金融转账）

反直觉的争用洞察： 增加针对相同热点行的应用 Pod 或线程数量，并不会提高吞吐量，反而会降低吞吐量。当 N 个并发写入者针对同一行时，每轮只有 1 个能提交；其余 N-1 个都会因 40001 而中止，并且必须重试。针对热点数据增加并发会造成更多无效工作，降低 TPS。解决方案：使用 SELECT ... FOR UPDATE 串行化访问，使用原子 UPDATE SET balance = balance + amount 消除读-改-写循环，或将写入分散到多个行。

权衡： 过度使用悲观锁可能导致等待链或死锁。应仅在热点路径和争用严重的工作负载中使用。

7. 使用基于集合的操作，而非逐行循环

与许多由客户端驱动的小型语句相比，CockroachDB 在面向集合的 SQL 操作下性能更佳。这可以减少往返次数、缩短争用窗口并提高吞吐量。

逐行处理的反模式：

for row in rows:
    db.execute(
        "UPDATE accounts SET balance = balance + 10 WHERE id = %s",
        (row.id,)
    )

推荐使用基于集合的操作：

UPDATE accounts
SET balance = balance + 10
WHERE region = 'US-East';

批量 INSERT：

INSERT INTO trades (id, symbol, price)
VALUES
  (1, 'AAPL', 180),
  (2, 'GOOG', 125),
  (3, 'AMZN', 140);

使用 UNNEST 批量 UPDATE：

WITH incoming AS (
  SELECT *
  FROM UNNEST(
    ARRAY['u1', 'u2', 'u3']::STRING[],
    ARRAY['active', 'inactive', 'active']::STRING[]
  ) AS t(id, new_status)
)
UPDATE users AS u
SET status = incoming.new_status,
    updated_at = now()
FROM incoming
WHERE u.id = incoming.id;

使用 LIMIT 进行维护批处理：

DELETE FROM sessions
WHERE expires_at < now()
ORDER BY expires_at
LIMIT 10000;

ORDER BY 可确保批次结果具有确定性，使后续运行不断推进；如果没有它，CockroachDB 在每次迭代时可能会选择不同的子集。

JDBC 批处理（Java）： 使用 addBatch/executeBatch，而不是逐行调用 executeUpdate。这样会在单次网络往返中发送所有行，而不是进行 N 次单独往返，从而消除空闲时间；在频繁交互的工作负载中，这类空闲时间可能占事务延迟的约 50%。

声明式 TTL：

-- created_at must be TIMESTAMPTZ; the expression's result type must also be TIMESTAMPTZ.
-- Cast if the source column is plain TIMESTAMP.
ALTER TABLE events
SET (ttl_expiration_expression = '(created_at + INTERVAL ''7 DAY'')::TIMESTAMPTZ');

8. 对非关键查询使用追随者读取

许多分析、仪表板和展示类查询不需要绝对最新的值。CockroachDB 支持从追随者副本进行追随者读取和有界陈旧读取，延迟更低。

基本追随者读取：

SELECT * FROM orders
AS OF SYSTEM TIME '-5s';

有界陈旧读取：

SELECT * FROM inventory
AS OF SYSTEM TIME with_max_staleness(INTERVAL '10s');

适用于大量读取的读写拆分模式： 当工作流读取较大的负载（例如 KYC JSON 文档），然后更新状态字段时，将其拆分为三个阶段：（1）在事务外使用 AS OF SYSTEM TIME 读取无冲突快照；（2）在应用层处理；（3）启动一个仅执行写入的短事务。这样可以避免在大量读取期间持有写入意向。

适用场景： 仪表板、分析、ETL、仅用于展示的读取，或读写可分离的大负载工作流。

避免用于： 后续写入决策要求使用最新事务状态的工作流。

9. 使用键集分页，而不是 OFFSET/LIMIT

随着 OFFSET 增大，CockroachDB 必须扫描并丢弃更多行。键集分页使用最后一行的排序键值，直接跳转到下一页。

OFFSET/LIMIT（深分页时效率低）：

SELECT id, order_date, customer_id
FROM orders
ORDER BY id
LIMIT 100 OFFSET 5000;

键集分页（推荐）：

SELECT id, order_date, customer_id
FROM orders
WHERE id > 5000
ORDER BY id
LIMIT 100;

多列键集分页：

SELECT id, created_at, customer_id
FROM orders
WHERE (created_at, id) > ('2025-01-01 00:00:00', 5000)
ORDER BY created_at, id
LIMIT 100;

权衡： 键集分页适合上一页/下一页导航，但不适合任意“跳转到第 73 页”的用户体验。

10. 使用预处理语句提升性能和安全性

预处理语句可复用查询结构并绑定新值，通过复用查询计划提升性能，同时防止 SQL 注入。

不安全的动态字符串拼接：

query = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(query)

预处理/参数化执行：

cursor.execute("SELECT * FROM users WHERE username = %s;", (user_input,))

复用查询计划：

PREPARE get_balance AS
SELECT balance FROM accounts WHERE id = $1;

EXECUTE get_balance(1001);
EXECUTE get_balance(2002);

11. 使用列投影，而不是 SELECT *

只选择所需的列。SELECT * 会增加网络负载、内存使用和 CPU 开销，并阻碍使用更精简的仅索引扫描。

-- Avoid
SELECT * FROM users WHERE id = 101;

-- Preferred
SELECT name, email FROM users WHERE id = 101;

对架构演进的影响： 如果后续架构变更添加了 profile_picture BYTEA，使用 SELECT * 的查询会自动读取这些额外数据。显式指定列可避免这种隐性的性能退化。

12. 设计键和索引以分散负载

连续递增或单调递增的主键会造成写入热点。键和索引应使读取和写入均匀分布到各个范围。

热点反模式：

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id UUID,
  region STRING
);

随机化键：

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID,
  region STRING
);

哈希分片索引：

CREATE INDEX orders_by_id_hash
ON orders (id)
USING HASH SHARDED WITH BUCKET_COUNT = 16;

用于自然分布的复合键：

CREATE TABLE sales (
  region_id STRING,
  order_id UUID DEFAULT gen_random_uuid(),
  PRIMARY KEY (region_id, order_id)
);

在整个集群中强制使用显式 PK：

SET CLUSTER SETTING sql.defaults.require_explicit_primary_keys.enabled = true;

13. 配置连接池

建立新的数据库连接成本较高。连接池可复用现有连接，以提升性能并防止过载。

HikariCP 指南：

maximumPoolSize: (vCPUs * 4) / number_of_pool_instances
minimumIdle: equal to maximumPoolSize
maxLifetime: 30 min (add jitter +/- 5 min)
idleTimeout: 5-10 min typical
keepaliveTime: slightly shorter than infrastructure timeout (~5 min)
connectionTimeout: 10-30 s typical
autoCommit: true unless using explicit transactions only

稳定配置示例：

maximum-pool-size: 12
minimum-idle: 12
max-lifetime: 1800000
idle-timeout: 600000
keepalive-time: 300000
connection-timeout: 10000
auto-commit: true
pool-name: ingestionPool

14. 将业务逻辑与数据库逻辑分离

CockroachDB 应负责管理 ACID 读写和架构级完整性。应用层应编排工作流、外部服务、队列和长时间运行的工作。

在事务内：

• 读取、写入、约束、简短且受保护的状态转换

在事务外：

• HTTP 调用、RPC／服务调用、电子邮件、支付服务提供商、发布到队列

异步工作流模式：

def handle_order(order):
    db.execute("INSERT INTO orders (id, status) VALUES (%s, %s)", (order.id, 'PENDING'))
    publish_event('process_order', {'order_id': order.id})

15. 遵守 16MB 事务负载限制

CockroachDB 的单个事务负载实际限制约为 16MB。此限制针对单个事务中写入的总数据量，而不仅是单行数据。

触及限制的两种方式：

• 单个大行（例如，15MB 的 JSON 文档）
• 在一个事务中写入许多中等大小的行（例如，25 条各 500KB 的 INSERT，共计 12.5MB）

指南：

• 将单行大小控制在 1MB 以下
• 将事务总负载控制在 4MB 以下
• 将事务限制在少于 10 条语句
• 将大型文档拆分为 64-256KB 的片段
• 将超过 1MB 的二进制对象存储在对象存储（S3/GCS）中，并在数据库中保存引用
• 将多语句事务拆分为更小的批次（每 5-10 条语句提交一次）

超过限制会导致 split failed while applying backpressure to Put 错误： 大型 Raft 提案会阻塞共识，范围拆分会停滞，系统会施加背压。

16. 使用会话护栏

设置会话级护栏，以便在开发和测试期间捕获失控查询和缺少 WHERE 子句的情况：

SET transaction_rows_read_err = 10000;
SET transaction_rows_written_err = 1000;

超过阈值的事务会因此失败并返回明确错误，而不是默默消耗集群资源。

17. 在并发环境下测试和优化

仅保证单用户场景下正确并不足够。应使用符合实际情况的并发负载进行测试，以发现重试、热点、竞争和特定工作负载的瓶颈。

快速开始：

cockroach workload init bank 'postgresql://root@localhost:26257?sslmode=disable'
cockroach workload run bank --concurrency=64 --duration=10m

请参阅 monitoring-and-concurrency-testing，了解详细的竞争查询、验证检查清单和 Prometheus 指标。

18. 监控性能和竞争

使用 EXPLAIN ANALYZE、crdb_internal.transaction_contention_events、DB Console SQL Activity 和 Key Visualizer，主动监控查询延迟、竞争、重试和数据分布。

请参阅 monitoring-and-concurrency-testing，了解实时竞争查询、Prometheus 指标和外部监控集成。

决策指南

场景	推荐模式
单条 SQL 语句	隐式事务（自动提交）
多条语句，必须全部成功或全部失败	使用重试循环的显式事务
写入热点行前读取当前状态	SELECT ... FOR UPDATE
历史、展示或报表读取	AS OF SYSTEM TIME / 跟随者读取
内存中的一批记录	UNNEST / VALUES / 批量 SQL
在一次操作中执行多步骤业务规则	单语句 CTE

安全注意事项

• 始终为 40001 序列化错误实现重试逻辑
• 确保操作具备幂等性，避免重试导致副作用重复发生（使用 INSERT ... ON CONFLICT DO NOTHING）
• 不要将过时的快照读取用作写入的权威前置条件
• 不要对会修改数据的生产查询运行 EXPLAIN ANALYZE
• 在高峰时段为高流量表添加索引时务必谨慎

参考资料

• CockroachDB 事务文档
• 高级客户端事务重试
• SQL 性能最佳实践
• Follower Reads 与有界陈旧读
• 优化语句性能
• 行级 TTL
• 架构设计与索引
• 防止 SQL 注入
• 架构：事务层
• JPA 最佳实践：显式事务与隐式事务
• 深入探究事务重试失败
• 比较多语句事务与单语句事务
• 使用 CockroachDB 执行基于集合的操作
• 使用 CockroachDB JDBC 驱动程序批量重写
• 什么是数据库热点？
• CockroachDB 事务演示
• CockroachDB 最佳实践与反模式演示 -- 10 个可运行的 Java 演示，涵盖重试、批处理、主键热点、护栏、分块和多区域
• CockroachDB JDBC 封装库 -- 面向 Java/JDBC 应用的自动重试库