Update to CockroachDB

{
  "description": "Guides application developers in designing correct and performant transaction patterns for CockroachDB, covering transaction lifetime, implicit vs explicit transactions, retry handling with exponential backoff, pushing invariants into SQL, selective pessimistic locking, set-based operations, connection pooling, prepared statements, keyset pagination, follower reads, and separating business logic from database logic. Use when building applications on CockroachDB, designing transaction workflows, handling retries, optimizing application-layer database interactions, or configuring connection pools.",
  "included_files": [
    {
      "relative_path": "references/monitoring-and-concurrency-testing.md",
      "size_in_bytes": 3675
    }
  ],
  "name": "design-application-txns",
  "skill_md_contents": "---\nname: design-application-txns\ndescription: Guides application developers in designing correct and performant transaction patterns for CockroachDB, covering transaction lifetime, implicit vs explicit transactions, retry handling with exponential backoff, pushing invariants into SQL, selective pessimistic locking, set-based operations, connection pooling, prepared statements, keyset pagination, follower reads, and separating business logic from database logic. Use when building applications on CockroachDB, designing transaction workflows, handling retries, optimizing application-layer database interactions, or configuring connection pools.\ncompatibility: \"CockroachDB >= 22.1. Works with or without a live database connection. With connection, requires appropriate privileges on target tables.\"\n---\n\n# Designing Application Transactions\n\nGuides 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.\n\n**Complement to SQL skills:** For SQL syntax, schema design, and query optimization, see [cockroachdb-sql](../cockroachdb-sql/SKILL.md). For benchmarking transaction formulations under contention, see [benchmarking-transaction-patterns](../benchmark-transactions/SKILL.md).\n\n## When to Use This Skill\n\n- Designing transaction boundaries for a CockroachDB application\n- Implementing client-side retry logic with exponential backoff\n- Deciding between implicit and explicit transactions\n- Choosing between optimistic and pessimistic concurrency control\n- Replacing read-modify-write loops with atomic SQL\n- Configuring connection pools (HikariCP, pgbouncer, etc.)\n- Implementing keyset pagination instead of OFFSET/LIMIT\n- Using follower reads for reporting and analytics queries\n- Separating business orchestration from database transactions\n- Using prepared statements for performance and security\n- Selecting explicit column projections instead of SELECT *\n- Testing application behavior under concurrency\n- Monitoring application-level database performance\n\n## Prerequisites\n\n- Familiarity with CockroachDB's SERIALIZABLE isolation level\n- Understanding of ACID transaction semantics\n- Access to application source code for transaction design changes\n- SQL connection to a CockroachDB cluster (for testing and validation)\n\n## Steps\n\n### 1. Keep Transactions Short-Lived\n\nTransactions 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.\n\nLong-lived transactions increase intent lifetime, contention, and retry probability in CockroachDB's distributed, optimistic-concurrency architecture.\n\n**Anti-pattern:**\n\n```java\n@Transactional\npublic void createOrder(Order order) {\n    orderRepository.save(order);\n    paymentGateway.charge(order); // external call inside TX\n}\n```\n\n**Correct approach — split the logic:**\n\n```java\n@Transactional\npublic void createOrderRecord(Order order) {\n    orderRepository.save(order);\n}\n\n// Outside the transaction\npaymentGateway.charge(order);\n```\n\n**Why it matters:**\n- Active intents block concurrent writers, reducing cluster throughput\n- Competing transactions are more likely to encounter `40001` retry errors\n- External work inside a retried transaction may run twice, causing duplicate side effects\n- Long transactions tie up connections and memory, reducing concurrency\n\n### 2. Use Implicit Transactions for Single Statements\n\nCockroachDB 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`.\n\n**Preferred:**\n\n```sql\nINSERT INTO orders (id, status)\nVALUES (gen_random_uuid(), 'open');\n```\n\n**Avoid:**\n\n```sql\nBEGIN;\nINSERT INTO orders (id, status)\nVALUES (gen_random_uuid(), 'open');\nCOMMIT;\n```\n\n**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.\n\n### 3. Use Explicit Transactions for Grouped Statements and Handle Retries\n\nWhen 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.\n\n```sql\nBEGIN;\n  UPDATE accounts SET balance = balance - 100 WHERE id = 1;\n  UPDATE accounts SET balance = balance + 100 WHERE id = 2;\nCOMMIT;\n```\n\n**Client-side retry loop with exponential backoff:**\n\n```python\nimport random\nimport time\n\ndef execute_with_retry(conn, txn_logic):\n    backoff = 0.1\n    while True:\n        try:\n            with conn.transaction() as txn:\n                txn_logic(txn)\n            return\n        except SerializationFailure:\n            time.sleep(backoff + random.uniform(0, 0.1))\n            backoff = min(backoff * 2, 2.0)\n```\n\n**Advanced retry with the cockroach_restart savepoint protocol:**\n\n```sql\nBEGIN;\nSAVEPOINT cockroach_restart;\n-- transactional work\nRELEASE SAVEPOINT cockroach_restart;\nCOMMIT;\n```\n\n**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.\n\n**SQLSTATE guidance:**\n\n| Code            | Meaning                                 | Action                                                |\n|-----------------|-----------------------------------------|-------------------------------------------------------|\n| `40001`         | Serialization / retryable               | Retry the entire unit of work with backoff and jitter |\n| `40003`         | Ambiguous result / indeterminate commit | Do not blindly replay non-idempotent work             |\n| `08xx` / `57xx` | Network or server transient issues      | Retry carefully, account for ambiguous commits        |\n| `23xxx`         | Constraint and application errors       | Usually should not be retried                         |\n\n### 4. Mark Read-Only Transactions Where Applicable\n\nRead-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.\n\n```sql\nBEGIN;\nSET TRANSACTION READ ONLY;\nSELECT * FROM customers WHERE region = 'US-East';\nCOMMIT;\n```\n\n### 5. Push Invariants into SQL — Avoid Read-Modify-Write Loops\n\nDo 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.\n\n**Anti-pattern:**\n\n```python\nbalance = db.fetch(\"SELECT balance FROM accounts WHERE id = 123\")\nbalance += 100\ndb.execute(\"UPDATE accounts SET balance = %s WHERE id = 123\", (balance,))\n```\n\n**Preferred atomic SQL:**\n\n```sql\nUPDATE accounts\nSET balance = balance + 100\nWHERE id = 123;\n```\n\n**Guarded write with invariant enforcement:**\n\n```sql\nUPDATE customer_daily_limits\nSET used_total = used_total + $2\nWHERE customer_id = $1\n  AND day = current_date\n  AND used_total + $2 <= daily_limit;\n```\n\n**Atomic CTE pattern:**\n\n```sql\nWITH limit_row AS (\n  SELECT customer_id, day\n  FROM customer_daily_limits\n  WHERE customer_id = $1 AND day = current_date\n  FOR UPDATE\n), spend AS (\n  UPDATE customer_daily_limits AS l\n  SET remaining_limit = l.remaining_limit - $2,\n      used_total = l.used_total + $2\n  FROM limit_row\n  WHERE l.customer_id = limit_row.customer_id\n    AND l.day = limit_row.day\n    AND l.remaining_limit >= $2\n  RETURNING l.customer_id, l.day\n), ins AS (\n  INSERT INTO transfers (customer_id, amount, direction, created_at)\n  SELECT $1, $2, 'debit', now()\n  FROM spend\n  RETURNING id AS transfer_id\n)\nSELECT transfer_id FROM ins;\n```\n\n**Key approaches:**\n- Use atomic updates: `UPDATE ... SET col = col + 1`\n- Use version or timestamp checks in WHERE clauses for optimistic concurrency\n- Enforce business rules with `UNIQUE`, `CHECK`, `NOT NULL`, and `FOREIGN KEY` constraints\n- Use `UPSERT` or `INSERT ... ON CONFLICT` instead of read-before-write existence checks\n- Use CTEs to keep multi-step logic atomic\n\n### 6. Use SELECT ... FOR UPDATE Selectively\n\nCockroachDB defaults to optimistic concurrency, which works well for most workloads. For hot rows or contention-heavy read-before-write paths, `SELECT ... FOR UPDATE` reduces retry churn by making contenders wait instead of race.\n\n```sql\nBEGIN;\nSELECT balance FROM accounts WHERE id = 1 FOR UPDATE;\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\nCOMMIT;\n```\n\n**Use when:**\n- The same rows are updated frequently by many concurrent transactions\n- Optimistic retries are causing thrashing\n- Consistency before write is required (inventory, financial transfers)\n\n**Counterintuitive contention insight:** Adding more application pods or threads targeting the same hot rows does NOT increase throughput -- it decreases it. With N concurrent writers on the same row, only 1 can commit per round; the other N-1 are aborted with `40001` and must retry. More concurrency on hot data means more wasted work and lower TPS. Solutions: use `SELECT ... FOR UPDATE` to serialize access, use atomic `UPDATE SET balance = balance + amount` to eliminate the read-modify-write cycle, or distribute writes across multiple rows.\n\n**Trade-off:** Overusing pessimistic locks can introduce waiting chains or deadlocks. Reserve for hot paths and contention-heavy workloads.\n\n### 7. Use Set-Based Operations Over Row-by-Row Loops\n\nCockroachDB performs best with set-oriented SQL rather than many small client-driven statements. This reduces round trips, shortens contention windows, and improves throughput.\n\n**Row-by-row anti-pattern:**\n\n```python\nfor row in rows:\n    db.execute(\n        \"UPDATE accounts SET balance = balance + 10 WHERE id = %s\",\n        (row.id,)\n    )\n```\n\n**Set-based preferred:**\n\n```sql\nUPDATE accounts\nSET balance = balance + 10\nWHERE region = 'US-East';\n```\n\n**Batch INSERT:**\n\n```sql\nINSERT INTO trades (id, symbol, price)\nVALUES\n  (1, 'AAPL', 180),\n  (2, 'GOOG', 125),\n  (3, 'AMZN', 140);\n```\n\n**Batch UPDATE with UNNEST:**\n\n```sql\nWITH incoming AS (\n  SELECT *\n  FROM UNNEST(\n    ARRAY['u1', 'u2', 'u3']::STRING[],\n    ARRAY['active', 'inactive', 'active']::STRING[]\n  ) AS t(id, new_status)\n)\nUPDATE users AS u\nSET status = incoming.new_status,\n    updated_at = now()\nFROM incoming\nWHERE u.id = incoming.id;\n```\n\n**Maintenance batching with LIMIT:**\n\n```sql\nDELETE FROM sessions\nWHERE expires_at < now()\nORDER BY expires_at\nLIMIT 10000;\n```\n\n`ORDER BY` keeps the batch deterministic so successive runs make forward progress; without it, CockroachDB may pick a different subset each iteration.\n\n**JDBC batching (Java):** Use `addBatch`/`executeBatch` instead of per-row `executeUpdate`. This sends all rows in a single network round trip rather than N individual round trips, eliminating idle time that can account for ~50% of transaction latency in chatty workloads.\n\n**Declarative TTL:**\n\n```sql\n-- created_at must be TIMESTAMPTZ; the expression's result type must also be TIMESTAMPTZ.\n-- Cast if the source column is plain TIMESTAMP.\nALTER TABLE events\nSET (ttl_expiration_expression = '(created_at + INTERVAL ''7 DAY'')::TIMESTAMPTZ');\n```\n\n### 8. Use Follower Reads for Non-Critical Queries\n\nMany analytics, dashboard, and display-oriented queries do not need the absolute latest value. CockroachDB supports follower reads and bounded-staleness reads from follower replicas with lower latency.\n\n**Basic follower read:**\n\n```sql\nSELECT * FROM orders\nAS OF SYSTEM TIME '-5s';\n```\n\n**Bounded staleness:**\n\n```sql\nSELECT * FROM inventory\nAS OF SYSTEM TIME with_max_staleness(INTERVAL '10s');\n```\n\n**Read-write split pattern for heavy reads:** When a workflow reads a large payload (e.g., KYC JSON document) and then updates a status field, split it into three phases: (1) read outside the transaction with `AS OF SYSTEM TIME` for a conflict-free snapshot, (2) process in the application layer, (3) start a short write-only transaction. This avoids holding write intents during the heavy read.\n\n**Use when:** Dashboards, analytics, ETL, display-only reads, or large-payload workflows where the read and write can be separated.\n\n**Avoid when:** The workflow requires the latest transactional state for a subsequent write decision.\n\n### 9. Use Keyset Pagination Instead of OFFSET/LIMIT\n\nAs the OFFSET grows, CockroachDB must scan and discard more rows. Keyset pagination uses the last row's ordered key values to jump directly to the next page.\n\n**OFFSET/LIMIT (inefficient at depth):**\n\n```sql\nSELECT id, order_date, customer_id\nFROM orders\nORDER BY id\nLIMIT 100 OFFSET 5000;\n```\n\n**Keyset pagination (preferred):**\n\n```sql\nSELECT id, order_date, customer_id\nFROM orders\nWHERE id > 5000\nORDER BY id\nLIMIT 100;\n```\n\n**Multi-column keyset:**\n\n```sql\nSELECT id, created_at, customer_id\nFROM orders\nWHERE (created_at, id) > ('2025-01-01 00:00:00', 5000)\nORDER BY created_at, id\nLIMIT 100;\n```\n\n**Trade-off:** Keyset pagination is ideal for next/previous navigation but not for arbitrary \"jump to page 73\" UX.\n\n### 10. Use Prepared Statements for Performance and Security\n\nPrepared statements reuse query structure and bind new values, improving performance through plan reuse and protecting against SQL injection.\n\n**Unsafe dynamic string concatenation:**\n\n```python\nquery = f\"SELECT * FROM users WHERE username = '{user_input}'\"\ncursor.execute(query)\n```\n\n**Prepared / parameterized execution:**\n\n```python\ncursor.execute(\"SELECT * FROM users WHERE username = %s;\", (user_input,))\n```\n\n**Plan reuse:**\n\n```sql\nPREPARE get_balance AS\nSELECT balance FROM accounts WHERE id = $1;\n\nEXECUTE get_balance(1001);\nEXECUTE get_balance(2002);\n```\n\n### 11. Use Column Projections Instead of SELECT *\n\nSelect only the columns you need. `SELECT *` increases network payload, memory usage, CPU cost, and prevents narrower index-only scans.\n\n```sql\n-- Avoid\nSELECT * FROM users WHERE id = 101;\n\n-- Preferred\nSELECT name, email FROM users WHERE id = 101;\n```\n\n**Schema evolution impact:** If a later schema change adds `profile_picture BYTEA`, queries using `SELECT *` automatically pull that extra data. Explicit projections avoid this hidden performance regression.\n\n### 12. Design Keys and Indexes to Distribute Load\n\nSequential or monotonically increasing primary keys create write hotspots. Keys and indexes should distribute reads and writes across ranges evenly.\n\n**Hotspot anti-pattern:**\n\n```sql\nCREATE TABLE orders (\n  id SERIAL PRIMARY KEY,\n  customer_id UUID,\n  region STRING\n);\n```\n\n**Randomized key:**\n\n```sql\nCREATE TABLE orders (\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  customer_id UUID,\n  region STRING\n);\n```\n\n**Hash-sharded index:**\n\n```sql\nCREATE INDEX orders_by_id_hash\nON orders (id)\nUSING HASH SHARDED WITH BUCKET_COUNT = 16;\n```\n\n**Composite key for natural distribution:**\n\n```sql\nCREATE TABLE sales (\n  region_id STRING,\n  order_id UUID DEFAULT gen_random_uuid(),\n  PRIMARY KEY (region_id, order_id)\n);\n```\n\n**Enforce explicit PKs cluster-wide:**\n\n```sql\nSET CLUSTER SETTING sql.defaults.require_explicit_primary_keys.enabled = true;\n```\n\n### 13. Configure Connection Pooling\n\nOpening new database connections is expensive. Pooling reuses live connections to improve performance and prevent overload.\n\n**HikariCP guidance:**\n\n```yaml\nmaximumPoolSize: (vCPUs * 4) / number_of_pool_instances\nminimumIdle: equal to maximumPoolSize\nmaxLifetime: 30 min (add jitter +/- 5 min)\nidleTimeout: 5-10 min typical\nkeepaliveTime: slightly shorter than infrastructure timeout (~5 min)\nconnectionTimeout: 10-30 s typical\nautoCommit: true unless using explicit transactions only\n```\n\n**Example stable configuration:**\n\n```yaml\nmaximum-pool-size: 12\nminimum-idle: 12\nmax-lifetime: 1800000\nidle-timeout: 600000\nkeepalive-time: 300000\nconnection-timeout: 10000\nauto-commit: true\npool-name: ingestionPool\n```\n\n### 14. Separate Business Logic from Database Logic\n\nCockroachDB should manage ACID reads, writes, and schema-level integrity. The application layer should orchestrate workflows, external services, queues, and long-running work.\n\n**Inside the transaction:**\n- Reads, writes, constraints, short guarded state transitions\n\n**Outside the transaction:**\n- HTTP calls, RPC/service calls, email, payment providers, queue publishing\n\n**Asynchronous workflow pattern:**\n\n```python\ndef handle_order(order):\n    db.execute(\"INSERT INTO orders (id, status) VALUES (%s, %s)\", (order.id, 'PENDING'))\n    publish_event('process_order', {'order_id': order.id})\n```\n\n### 15. Respect the 16MB Transaction Payload Limit\n\nCockroachDB has a practical limit of ~16MB per transaction payload. This limit applies to the TOTAL data written in a single transaction, not just individual rows.\n\n**Two ways to hit the limit:**\n- One large row (e.g., a 15MB JSON document)\n- Many moderate rows in one transaction (e.g., 25 INSERTs of 500KB each = 12.5MB)\n\n**Guidelines:**\n- Keep individual rows under 1MB\n- Keep total transaction payload under 4MB\n- Limit transactions to <10 statements\n- Chunk large documents into 64-256KB pieces\n- Store blobs >1MB in object storage (S3/GCS) with a database reference\n- Break multi-statement transactions into smaller batches (commit every 5-10 statements)\n\n**Exceeding the limit causes `split failed while applying backpressure to Put` errors:** large Raft proposals block consensus, range splits stall, and the system applies backpressure.\n\n### 16. Use Session Guardrails\n\nSet session-level guardrails to catch runaway queries and missing WHERE clauses during development and testing:\n\n```sql\nSET transaction_rows_read_err = 10000;\nSET transaction_rows_written_err = 1000;\n```\n\nThese cause transactions that exceed the thresholds to fail with an explicit error rather than silently consuming cluster resources.\n\n### 17. Test and Optimize Under Concurrency\n\nSingle-user correctness is not sufficient. Test with realistic concurrency to surface retries, hotspots, contention, and workload-specific bottlenecks.\n\n**Quick start:**\n\n```bash\ncockroach workload init bank 'postgresql://root@localhost:26257?sslmode=disable'\ncockroach workload run bank --concurrency=64 --duration=10m\n```\n\nSee [monitoring-and-concurrency-testing](references/monitoring-and-concurrency-testing.md) for detailed contention queries, validation checklists, and Prometheus metrics.\n\n### 18. Monitor for Performance and Contention\n\nActively monitor query latency, contention, retries, and data distribution using `EXPLAIN ANALYZE`, `crdb_internal.transaction_contention_events`, DB Console SQL Activity, and Key Visualizer.\n\nSee [monitoring-and-concurrency-testing](references/monitoring-and-concurrency-testing.md) for live contention queries, Prometheus metrics, and external monitoring integration.\n\n## Decision Guide\n\n| Scenario                                    | Recommended Pattern                  |\n|---------------------------------------------|--------------------------------------|\n| Single SQL statement                        | Implicit transaction (autocommit)    |\n| Multiple statements, all-or-nothing         | Explicit transaction with retry loop |\n| Read current state before write on hot rows | `SELECT ... FOR UPDATE`              |\n| Historical, display, or reporting read      | `AS OF SYSTEM TIME` / follower reads |\n| Batch of records in memory                  | `UNNEST` / `VALUES` / batch SQL      |\n| Multi-step business rule in one operation   | Single-statement CTE                 |\n\n## Safety Considerations\n\n- Always implement retry logic for `40001` serialization errors\n- Make operations idempotent so retries do not cause duplicate side effects (use `INSERT ... ON CONFLICT DO NOTHING`)\n- Do not use stale snapshot reads as authoritative preconditions for writes\n- Do not run `EXPLAIN ANALYZE` on production queries that modify data\n- Be cautious adding indexes to high-traffic tables during peak hours\n\n## References\n\n- [CockroachDB Transactions Documentation](https://www.cockroachlabs.com/docs/stable/transactions)\n- [Advanced Client-Side Transaction Retries](https://www.cockroachlabs.com/docs/stable/advanced-client-side-transaction-retries)\n- [SQL Performance Best Practices](https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview)\n- [Follower Reads and Bounded Staleness](https://www.cockroachlabs.com/docs/stable/follower-reads)\n- [Optimize Statement Performance](https://www.cockroachlabs.com/docs/stable/make-queries-fast)\n- [Row-Level TTL](https://www.cockroachlabs.com/docs/stable/row-level-ttl)\n- [Schema Design and Indexes](https://www.cockroachlabs.com/docs/stable/schema-design-indexes)\n- [SQL Injection Prevention](https://www.cockroachlabs.com/docs/stable/sql-injection-prevention)\n- [Architecture: Transaction Layer](https://www.cockroachlabs.com/docs/stable/architecture/transaction-layer)\n- [JPA Best Practices: Explicit and Implicit Transactions](https://blog.cloudneutral.se/jpa-best-practices-explicit-and-implicit-transactions)\n- [Deep Dive into Transaction Retry Failures](https://www.mindfulchase.com/explore/troubleshooting-tips/databases/deep-dive-into-transaction-retry-failures-in-cockroachdb-root-causes-and-fixes.html)\n- [Comparing Multi-Statement vs Single-Statement Transactions](https://andrewdeally.medium.com/comparing-multi-statement-vs-single-statement-transactions-for-account-transfers-in-sql-09190b116e64)\n- [Set-Based Operations with CockroachDB](https://andrewdeally.medium.com/set-based-operations-with-cockroachdb-c9f371992dc7)\n- [Bulk Rewrites with the CockroachDB JDBC Driver](https://blog.cloudneutral.se/cockroachdb-jdbc-driver-part-iii-bulk-rewrites)\n- [What is a Database Hotspot?](https://www.cockroachlabs.com/blog/the-hot-content-problem-metadata-storage-for-media-streaming/)\n- [CockroachDB Transaction Demo](https://github.com/cockroachdb/cockroach-transaction-demo)\n- [CockroachDB Best Practices & Anti-Patterns Demo](https://github.com/viragtripathi/cockroachdb-best-practices-demo) -- 10 runnable Java demos covering retries, batching, PK hotspots, guardrails, chunking, and multi-region\n- [CockroachDB JDBC Wrapper](https://github.com/viragtripathi/cockroachdb-jdbc-wrapper) -- automatic retry library for Java/JDBC applications\n"
}