Transactions

Transactions ensure that a group of SQL statements execute atomically - either all succeed or all fail together. CockroachDB provides ACID-compliant transactions with multiple isolation levels.

Transaction Basics

A transaction groups multiple SQL statements into a single atomic unit of work.

BEGIN;

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

COMMIT;

Transaction Syntax

BEGIN Transaction

Basic BEGIN
With Isolation Level
With Priority
Read Only

BEGIN;
-- or
BEGIN TRANSACTION;
-- or
START TRANSACTION;

BEGIN ISOLATION LEVEL SERIALIZABLE;

BEGIN ISOLATION LEVEL READ COMMITTED;

BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN PRIORITY HIGH;

BEGIN PRIORITY NORMAL;

BEGIN PRIORITY LOW;

-- Combine isolation and priority
BEGIN ISOLATION LEVEL SERIALIZABLE PRIORITY HIGH;

BEGIN READ ONLY;

-- Read-only transactions can be more efficient
SELECT * FROM users WHERE status = 'active';

COMMIT;

COMMIT Transaction

-- Standard commit
COMMIT;

-- Explicit syntax (same as COMMIT)
COMMIT TRANSACTION;
COMMIT WORK;

ROLLBACK Transaction

-- Full rollback
ROLLBACK;

-- Explicit syntax (same as ROLLBACK)
ROLLBACK TRANSACTION;
ROLLBACK WORK;

CockroachDB does not support SAVEPOINT or partial rollbacks. You must roll back the entire transaction.

Isolation Levels

CockroachDB supports three SQL isolation levels that map to internal isolation implementations.

SERIALIZABLE
REPEATABLE READ
READ COMMITTED

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Strongest isolation
-- Transactions appear to execute serially
-- May retry on conflicts

UPDATE inventory SET quantity = quantity - 1 
WHERE product_id = 5 AND quantity > 0;

INSERT INTO orders (product_id, quantity) 
VALUES (5, 1);

COMMIT;

Characteristics:

Strongest consistency guarantees
Prevents all anomalies (dirty reads, non-repeatable reads, phantoms)
Default isolation level
May experience transaction retries under contention

BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Also called SNAPSHOT isolation
-- Reads see consistent snapshot

SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Will see same count if queried again in this transaction

COMMIT;

Characteristics:

Reads operate on a consistent snapshot
Prevents dirty reads and non-repeatable reads
Better performance than SERIALIZABLE
Mapped to Snapshot isolation internally

BEGIN ISOLATION LEVEL READ COMMITTED;

-- Each statement sees latest committed data
-- Different statements may see different snapshots

SELECT balance FROM accounts WHERE id = 1;
-- Another transaction commits here
SELECT balance FROM accounts WHERE id = 1;
-- May see different value

COMMIT;

Characteristics:

Each statement reads latest committed data
Prevents dirty reads only
Best performance for high-contention workloads
Must be enabled via cluster setting

Isolation Level Mapping

CockroachDB maps SQL isolation levels to internal implementations:

SERIALIZABLE → Serializable isolation
REPEATABLE READ / SNAPSHOT → Snapshot isolation
READ COMMITTED / READ UNCOMMITTED → Read Committed isolation

READ UNCOMMITTED is upgraded to READ COMMITTED (PostgreSQL does this too).

Setting Default Isolation

-- Session-level setting
SET default_transaction_isolation = 'SERIALIZABLE';
SET default_transaction_isolation = 'REPEATABLE READ';
SET default_transaction_isolation = 'READ COMMITTED';

-- Check current setting
SHOW default_transaction_isolation;

Transaction Priority

Priority affects which transactions win during conflicts.

Priority Levels
Use Cases
Setting Priority

-- HIGH priority (wins conflicts with NORMAL/LOW)
BEGIN PRIORITY HIGH;
UPDATE critical_data SET value = value + 1;
COMMIT;

-- NORMAL priority (default)
BEGIN PRIORITY NORMAL;
UPDATE data SET value = value + 1;
COMMIT;

-- LOW priority (yields to NORMAL/HIGH)
BEGIN PRIORITY LOW;
SELECT * FROM logs;
COMMIT;

-- At transaction start
BEGIN PRIORITY HIGH;

-- During transaction (before any statements)
BEGIN;
SET TRANSACTION PRIORITY HIGH;
UPDATE accounts SET balance = balance + 100;
COMMIT;

-- Session default
SET default_transaction_priority = 'high';

Transaction Retries

CockroachDB may automatically retry transactions that encounter conflicts.

Automatic retries only work for implicit transactions (single statements) or when using client-side retry logic.

Client-Side Retries

BEGIN;
SAVEPOINT cockroach_restart;

-- Your transaction logic
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;

-- Check for retry
RELEASE SAVEPOINT cockroach_restart;
COMMIT;

-- On retry error (40001), rollback to savepoint and retry

Retry Errors

40001 - serialization_failure - Transaction conflict, retry possible
40003 - statement_completion_unknown - Uncertain if statement committed

Transaction State

Check Transaction Status

-- Show current transaction status
SHOW TRANSACTION STATUS;

-- Show transaction isolation level
SHOW TRANSACTION ISOLATION LEVEL;

-- Show transaction priority
SHOW TRANSACTION PRIORITY;

-- Check if in transaction
SELECT current_setting('transaction_isolation');

Transaction Timestamps

BEGIN;

-- Timestamp at transaction start
SELECT TRANSACTION_TIMESTAMP();

-- Same value throughout transaction
SELECT TRANSACTION_TIMESTAMP();

-- Statement timestamp (changes per statement)
SELECT STATEMENT_TIMESTAMP();

COMMIT;

AS OF SYSTEM TIME (Historical Reads)

Query data as it existed at a specific point in time.

Timestamp Query
Transaction Time Travel
Follower Reads

-- Read data from 1 hour ago
SELECT * FROM users 
AS OF SYSTEM TIME '-1h';

-- Specific timestamp
SELECT * FROM orders 
AS OF SYSTEM TIME '2026-03-03 10:00:00';

-- Interval syntax
SELECT * FROM products 
AS OF SYSTEM TIME INTERVAL '-30m';

BEGIN AS OF SYSTEM TIME '-1h';

-- All queries in this transaction see data from 1 hour ago
SELECT COUNT(*) FROM orders;
SELECT * FROM order_items WHERE order_id = 100;

COMMIT;

-- Read from closest replica (reduced latency)
SELECT * FROM users 
AS OF SYSTEM TIME follower_read_timestamp();

-- Slightly stale but much faster
-- Useful for read-heavy workloads

Historical reads are subject to the garbage collection window (default 25 hours). Data older than this window may not be available.

Transaction Settings

Session Settings

-- Default isolation level
SET default_transaction_isolation = 'SERIALIZABLE';

-- Default priority
SET default_transaction_priority = 'normal';

-- Statement timeout
SET statement_timeout = '30s';

-- Idle in transaction timeout
SET idle_in_transaction_session_timeout = '60s';

Transaction-Specific Settings

BEGIN;

-- Set for current transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION PRIORITY HIGH;
SET TRANSACTION READ ONLY;

-- Your queries here

COMMIT;

Distributed Transactions

CockroachDB transactions can span multiple nodes and even regions.

Multi-Region Considerations

-- Transaction in a specific region
BEGIN;

-- Queries automatically route to appropriate regions
UPDATE users SET last_login = now() WHERE id = 1;
INSERT INTO login_log (user_id, timestamp) VALUES (1, now());

COMMIT;

Transaction Contention

-- View lock contention
SELECT * FROM crdb_internal.cluster_locks;

-- View transaction statistics
SELECT * FROM crdb_internal.node_transaction_statistics;

-- Identify slow transactions
SELECT * FROM crdb_internal.cluster_queries 
WHERE start_time < now() - INTERVAL '30s';

Best Practices

Transaction Best Practices:

Keep transactions short - Reduces contention and lock duration
Use appropriate isolation - SERIALIZABLE for strong consistency, READ COMMITTED for high throughput
Set transaction priority - HIGH for critical operations, LOW for background jobs
Implement retry logic - Handle serialization failures in application code
Batch operations - Combine multiple statements in single transaction when atomic
Avoid long-running transactions - Set statement_timeout and idle_in_transaction_session_timeout
Use AS OF SYSTEM TIME - For analytics and non-critical reads
Read-only when possible - Declare READ ONLY transactions for better performance

Common Patterns

Bank Transfer

BEGIN;

-- Verify balance
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Transfer money
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Record transaction
INSERT INTO transfers (from_account, to_account, amount) 
VALUES (1, 2, 100);

COMMIT;

Inventory Management

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Check and decrement inventory
UPDATE inventory 
SET quantity = quantity - 1 
WHERE product_id = 5 AND quantity > 0
RETURNING quantity;

-- Create order if inventory was available
INSERT INTO orders (product_id, quantity) 
VALUES (5, 1);

COMMIT;

Audit Trail

BEGIN;

-- Make change
UPDATE users SET status = 'inactive' WHERE id = 1
RETURNING id, status;

-- Record audit entry
INSERT INTO audit_log (table_name, record_id, action, changed_at)
VALUES ('users', 1, 'status_change', CURRENT_TIMESTAMP);

COMMIT;

Troubleshooting

Transaction Deadlocks

-- View current transactions
SHOW TRANSACTIONS;

-- Kill a long-running transaction
CANCEL QUERY '<query_id>';

Serialization Failures

-- Use READ COMMITTED for high-contention workloads
BEGIN ISOLATION LEVEL READ COMMITTED;

Monitor Transaction Performance

-- Active transactions
SELECT * FROM crdb_internal.cluster_transactions;

-- Transaction statistics
SELECT * FROM crdb_internal.node_transaction_statistics
ORDER BY count DESC
LIMIT 10;

Getting Started

Architecture

SQL Reference

Administration

Operations

Performance

Transaction Basics

Transaction Syntax

BEGIN Transaction

COMMIT Transaction

ROLLBACK Transaction

Isolation Levels

Isolation Level Mapping

Setting Default Isolation

Transaction Priority

Transaction Retries

Client-Side Retries

Retry Errors

Transaction State

Check Transaction Status

Transaction Timestamps

AS OF SYSTEM TIME (Historical Reads)

Transaction Settings

Session Settings

Transaction-Specific Settings

Distributed Transactions

Multi-Region Considerations

Transaction Contention

Best Practices

Common Patterns

Bank Transfer

Inventory Management

Audit Trail

Troubleshooting

Transaction Deadlocks

Serialization Failures

Monitor Transaction Performance

See Also

Build docs developers (and LLMs) love

Getting Started

Architecture

SQL Reference

Administration

Operations

Performance

​Transaction Basics

​Transaction Syntax

​BEGIN Transaction

​COMMIT Transaction

​ROLLBACK Transaction

​Isolation Levels

​Isolation Level Mapping

​Setting Default Isolation

​Transaction Priority

​Transaction Retries

​Client-Side Retries

​Retry Errors

​Transaction State

​Check Transaction Status

​Transaction Timestamps

​AS OF SYSTEM TIME (Historical Reads)

​Transaction Settings

​Session Settings

​Transaction-Specific Settings

​Distributed Transactions

​Multi-Region Considerations

​Transaction Contention

​Best Practices

​Common Patterns

​Bank Transfer

​Inventory Management

​Audit Trail

​Troubleshooting

​Transaction Deadlocks

​Serialization Failures

​Monitor Transaction Performance

​See Also

Build docs developers (and LLMs) love

Transaction Basics

Transaction Syntax

BEGIN Transaction

COMMIT Transaction

ROLLBACK Transaction

Isolation Levels

Isolation Level Mapping

Setting Default Isolation

Transaction Priority

Transaction Retries

Client-Side Retries

Retry Errors

Transaction State

Check Transaction Status

Transaction Timestamps

AS OF SYSTEM TIME (Historical Reads)

Transaction Settings

Session Settings

Transaction-Specific Settings

Distributed Transactions

Multi-Region Considerations

Transaction Contention

Best Practices

Common Patterns

Bank Transfer

Inventory Management

Audit Trail

Troubleshooting

Transaction Deadlocks

Serialization Failures

Monitor Transaction Performance

See Also