benchmarking-transaction-patterns

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.

Core Concept

Under contention, the transaction formulation itself is a primary performance lever. The explicit model (multi-statement BEGIN/COMMIT) keeps the transaction open across round trips, widening the contention window. The CTE model (single-statement) collapses the same logic into one atomic statement, reducing transaction duration and retries.

Explicit Transaction Model

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;

CTE Transaction Model

CockroachDB rejects multiple mutations of the same table in a single statement by default (sql.multiple_modifications_of_table.enabled), so the debit and credit are folded into one UPDATE using CASE.

WITH funded AS (
  SELECT 1 FROM accounts WHERE id = $1 AND balance >= $2
), upd AS (
  UPDATE accounts
  SET balance = CASE WHEN id = $1 THEN balance - $2 ELSE balance + $2 END
  WHERE id IN ($1, $3) AND EXISTS (SELECT 1 FROM funded)
  RETURNING id
), ins AS (
  INSERT INTO transfers (from_acct, to_acct, amount, created_at)
  SELECT $1, $3, $2, now() WHERE (SELECT count(*) FROM upd) = 2
  RETURNING id
)
SELECT id FROM ins;