designing-application-transactions

What this skill does

# Designing Application Transactions

Guides application developers through the design principles and implementation patterns needed to build correct, performant, and resilient applications on CockroachDB. Covers the full spectrum from transaction scoping and retry logic to connection pooling and observability.

**Complement to SQL skills:** For SQL syntax, schema design, and query optimization, see [cockroachdb-sql](../../cockroachdb-query-and-schema-design/cockroachdb-sql/SKILL.md). For benchmarking transaction formulations under contention, see [benchmarking-transaction-patterns](../benchmarking-transaction-patterns/SKILL.md).

## When to Use This Skill

- Designing transaction boundaries for a CockroachDB application
- Implementing client-side retry logic with exponential backoff
- Deciding between implicit and explicit transactions
- Choosing between optimistic and pessimistic concurrency control
- Replacing read-modify-write loops with atomic SQL
- Configuring connection pools (HikariCP, pgbouncer, etc.)
- Implementing keyset pagination instead of OFFSET/LIMIT
- Using follower reads for reporting and analytics queries
- Separating business orchestration from database transactions
- Using prepared statements for performance and security
- Selecting explicit column projections instead of SELECT *
- Testing application behavior under concurrency
- Monitoring application-level database performance

## Prerequisites

- Familiarity with CockroachDB's SERIALIZABLE isolation level
- Understanding of ACID transaction semantics
- Access to application source code for transaction design changes
- SQL connection to a CockroachDB cluster (for testing and validation)

## Steps

### 1. Keep Transactions Short-Lived

Transactions must include only the minimal set of SQL operations needed for one atomic state change. Do not place remote API calls, service-to-service requests, loops, expensive computation, or artificial waits inside a CockroachDB transaction.

Long-lived transactions increase intent lifetime, contention, and retry probability in CockroachDB's distributed, optimistic-concurrency architecture.

**Anti-pattern:**

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

**Correct approach — split the logic:**

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

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

**Why it matters:**
- Active intents block concurrent writers, reducing cluster throughput
- Competing transactions are more likely to encounter `40001` retry errors
- External work inside a retried transaction may run twice, causing duplicate side effects
- Long transactions tie up connections and memory, reducing concurrency

### 2. Use Implicit Transactions for Single Statements

CockroachDB automatically wraps each individual SQL statement as a transaction in autocommit mode. For single `INSERT`, `UPDATE`, `DELETE`, or `SELECT` statements, do not wrap in explicit `BEGIN`/`COMMIT`.

**Preferred:**

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

**Avoid:**

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

**Benefits:** Simpler code paths, lower latency (fewer round trips), less resource usage, and fewer retry concerns since single-statement transactions are easier for CockroachDB to retry automatically.

### 3. Use Explicit Transactions for Grouped Statements and Handle Retries

When multiple SQL operations must succeed or fail together, use explicit transactions with `BEGIN`/`COMMIT`. Because CockroachDB defaults to SERIALIZABLE isolation, transaction retries are a normal part of correct execution under contention.

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

**Client-side retry loop with exponential backoff:**

```python
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)
```

**Advanced retry with the cockroach_restart savepoint protocol:**

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

**WARNING: Generic savepoints do NOT work as retry mechanisms.** CockroachDB aborts the entire transaction on a `40001` serialization failure. Using `ROLLBACK TO SAVEPOINT` on a regular savepoint cannot recover -- the transaction remains in an aborted state. Only the special `SAVEPOINT cockroach_restart` protocol (where the client catches the error, rolls back to the savepoint, and re-executes the work) is supported. For most applications, a full-transaction retry loop is simpler and recommended.

**SQLSTATE guidance:**

| Code            | Meaning                                 | Action                                                |
|-----------------|-----------------------------------------|-------------------------------------------------------|
| `40001`         | Serialization / retryable               | Retry the entire unit of work with backoff and jitter |
| `40003`         | Ambiguous result / indeterminate commit | Do not blindly replay non-idempotent work             |
| `08xx` / `57xx` | Network or server transient issues      | Retry carefully, account for ambiguous commits        |
| `23xxx`         | Constraint and application errors       | Usually should not be retried                         |

### 4. Mark Read-Only Transactions Where Applicable

Read-only transactions perform retrieval only and make no writes. Marking them as read-only allows CockroachDB to avoid unnecessary write intents, reduce contention with writers, and enable follower or bounded-staleness reads.

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

### 5. Push Invariants into SQL — Avoid Read-Modify-Write Loops

Do not fetch state into application code, modify it in memory, and write it back. Prefer atomic SQL, constraints, guarded UPDATEs, UPSERT, INSERT ... ON CONFLICT, and CTE-based mutations.

**Anti-pattern:**

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

**Preferred atomic SQL:**

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

**Guarded write with invariant enforcement:**

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

**Atomic CTE pattern:**

```sql
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;
```

**Key approaches:**
- Use atomic updates: `UPDATE ... SET col = col + 1`
- Use version or timestamp checks in WHERE clauses for optimistic concurrency
- Enforce business rules with `UNIQUE`, `CHECK`, `NOT NULL`, and `FOREIGN KEY` constraints
- Use `UPSERT` or `INSERT ... ON CONFLICT` instead of read-before-write existence checks
- Use CTEs to keep multi-step logic atomic

### 6. Use SEL