Transactions

On this page

CockroachDB supports bundling multiple SQL statements into a single all-or-nothing transaction. Each transaction guarantees ACID semantics spanning arbitrary tables and rows, even when data is distributed. If a transaction succeeds, all mutations are applied together with virtual simultaneity. If any part of a transaction fails, the entire transaction is aborted, and the database is left unchanged. CockroachDB guarantees that while a transaction is pending, it is isolated from other concurrent transactions with serializable isolation.

SQL statements

Each of the following SQL statements control transactions in some way.

Syntax

In CockroachDB, a transaction is set up by surrounding SQL statements with the BEGIN and COMMIT statements.

To use advanced client-side transaction retries, you should also include the SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT statements.

> BEGIN;

> SAVEPOINT cockroach_restart;

<transaction statements>

> RELEASE SAVEPOINT cockroach_restart;

> COMMIT;

At any time before it's committed, you can abort the transaction by executing the ROLLBACK statement.

Clients using transactions must also include logic to handle retries.

Error handling

To handle errors in transactions, you should check for the following types of server-side errors:

Transaction retries

Transactions may require retries if they experience deadlock or read/write contention with other concurrent transactions which cannot be resolved without allowing potential serializable anomalies.

There are two cases in which transaction retries occur:

1. Automatic retries, which CockroachDB processes for you.
2. Client-side intervention, which your application must handle.

Automatic retries

CockroachDB automatically retries individual statements (implicit transactions) and transactions sent from the client as a single batch, as long as the size of the results being produced for the client, including protocol overhead, is less than 16KiB by default. Once that buffer overflows, CockroachDB starts streaming results back to the client, at which point automatic retries cannot be performed any more. As long as the results of a single statement or batch of statements are known to stay clear of this limit, the client does not need to worry about transaction retries.

In future versions of CockroachDB, we plan on providing stronger guarantees for read-only queries that return at most one row, regardless of the size of that row.

Individual statements

Individual statements are treated as implicit transactions, and so they fall under the rules described above. If the results are small enough, they will be automatically retried. In particular, INSERT/UPDATE/DELETE statements without a RETURNING clause are guaranteed to have minuscule result sizes. For example, the following statement would be automatically retried by CockroachDB:

> DELETE FROM customers WHERE id = 1;

Batched statements

Transactions can be sent from the client as a single batch. Batching implies that CockroachDB receives multiple statements without being asked to return results in between them; instead, CockroachDB returns results after executing all of the statements, except when the accumulated results overflow the buffer mentioned above, in which case they are returned sooner and automatic retries can no longer be performed.

Batching is generally controlled by your driver or client's behavior. Technically, it can be achieved in two ways, both supporting automatic retries:

1. When the client/driver is using the PostgreSQL Extended Query protocol, a batch is made up of all queries sent in between two Sync messages. Many drivers support such batches through explicit batching constructs.

2. When the client/driver is using the PostgreSQL Simple Query protocol, a batch is made up of semicolon-separated strings sent as a unit to CockroachDB. For example, in Go, this code would send a single batch (which would be automatically retried):

   db.Exec(
     "BEGIN;
   
     DELETE FROM customers WHERE id = 1;
   
     DELETE orders WHERE customer = 1;
   
     COMMIT;"
   )
   

Client-side intervention

Your application should include client-side retry handling when the statements are sent individually, such as:

> BEGIN;

> UPDATE products SET inventory = 0 WHERE sku = '8675309';

> INSERT INTO orders (customer, status) VALUES (1, 'new');

> COMMIT;

To indicate that a transaction must be retried, CockroachDB signals an error with the code 40001 and an error message that begins with the string "retry transaction".

To handle these types of errors you have the following options:

1. If your database library or framework provides a method for retryable transactions (it will often be documented as a tool for handling deadlocks), use it. If you're building an application in the following languages, we have code to make client-side retries simpler:
   • Go developers can use the github.com/cockroachdb/cockroach-go/crdb package, which handles retries automatically. For an example, see Build a Go App with CockroachDB.
   • Python developers can use SQLAlchemy with the sqlalchemy-cockroachdb adapter. For an example, see Build a Python App with CockroachDB.
   • Java developers accessing the database with JDBC can re-use the example code implementing retry logic shown in Build a Java app with CockroachDB.
2. Most users, such as application authors: Abort the transaction using the ROLLBACK statement, and then reissue all of the statements in the transaction. For an example, see the Client-side intervention example.
3. Advanced users, such as library authors: See Advanced Client-Side Transaction Retries.

Client-side intervention example

The Python-like pseudocode below shows how to implement an application-level retry loop; it does not require your driver or ORM to implement advanced retry handling logic, so it can be used from any programming language or environment. In particular, your retry loop must:

• Raise an error if the max_retries limit is reached
• Retry on 40001 error codes
• COMMIT at the end of the try block
• Implement exponential backoff logic as shown below for best performance

while true:
    n++
    if n == max_retries:
        throw Error("did not succeed within N retries")
    try:
        # add logic here to run all your statements
        conn.exec('COMMIT')
        break
    catch error:
        if error.code != "40001":
            throw error
        else:
            # This is a retry error, so we roll back the current transaction
            # and sleep for a bit before retrying. The sleep time increases
            # for each failed transaction.  Adapted from
            # https://colintemple.com/2017/03/java-exponential-backoff/
            conn.exec('ROLLBACK');
            sleep_ms = int(((2**n) * 100) + rand( 100 - 1 ) + 1)
            sleep(sleep_ms) # Assumes your sleep() takes milliseconds

Transaction contention

Transactions in CockroachDB lock data resources that are written during their execution. When a pending write from one transaction conflicts with a write of a concurrent transaction, the concurrent transaction must wait for the earlier transaction to complete before proceeding. When a dependency cycle is detected between transactions, the transaction with the higher priority aborts the dependent transaction to avoid deadlock, which must be retried.

For more details about transaction contention and best practices for avoiding contention, see Understanding and Avoiding Transaction Contention.

Nested transactions

New in v20.1: CockroachDB supports the nesting of transactions using savepoints. These nested transactions are also known as sub-transactions. Nested transactions can be rolled back without discarding the state of the entire surrounding transaction.

This can be useful in applications that abstract database access using an application development framework or ORM. Different components of the application can operate on different sub-transactions without having to know about each others' internal operations, while trusting that the database will maintain isolation between sub-transactions and preserve data integrity.

Just as COMMIT and ROLLBACK are used to commit and discard entire transactions, respectively, RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT are used to commit and discard nested transactions. This relationship is shown in the table below:

For more information, including examples showing how to use savepoints to create nested transactions, see the savepoints documentation.

Transaction priorities

Every transaction in CockroachDB is assigned an initial priority. By default, the transaction priority is NORMAL, but for transactions that should be given higher (or lower) preference in high-contention scenarios, you can set the priority in the BEGIN statement:

> BEGIN PRIORITY <LOW | NORMAL | HIGH>;

You can also set the priority immediately after a transaction is started:

> SET TRANSACTION PRIORITY <LOW | NORMAL | HIGH>;

To see the current priority of a transaction, use SHOW TRANSACTION PRIORITY or SHOW transaction_priority:

> SHOW transaction_priority;

  transaction_priority
------------------------
  high

> SHOW TRANSACTION PRIORITY;

  transaction_priority
------------------------
  high

Note that transaction_priority is a read-only session variable that cannot be set directly.

Isolation levels

CockroachDB executes all transactions at the strongest ANSI transaction isolation level: SERIALIZABLE. All other ANSI transaction isolation levels (e.g., SNAPSHOT, READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ) are automatically upgraded to SERIALIZABLE. Weaker isolation levels have historically been used to maximize transaction throughput. However, recent research has demonstrated that the use of weak isolation levels results in substantial vulnerability to concurrency-based attacks.

Serializable isolation

With SERIALIZABLE isolation, a transaction behaves as though it has the entire database all to itself for the duration of its execution. This means that no concurrent writers can affect the transaction unless they commit before it starts, and no concurrent readers can be affected by the transaction until it has successfully committed. This is the strongest level of isolation provided by CockroachDB and it's the default.

SERIALIZABLE isolation permits no anomalies. To prevent write skew anomalies, SERIALIZABLE isolation may require transaction restarts. For a demonstration of SERIALIZABLE preventing write skew, see Serializable Transactions.

Comparison to ANSI SQL isolation levels

CockroachDB uses slightly different isolation levels than ANSI SQL isolation levels.

Aliases

SNAPSHOT, READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ are aliases for SERIALIZABLE.

Comparison

The CockroachDB SERIALIZABLE level is stronger than the ANSI SQL READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ levels and equivalent to the ANSI SQL SERIALIZABLE level.

For more information about the relationship between these levels, see A Critique of ANSI SQL Isolation Levels.

See also

• BEGIN
• COMMIT
• ROLLBACK
• SAVEPOINT
• RELEASE SAVEPOINT
• SHOW
• Retryable transaction example code in Java using JDBC
• CockroachDB Architecture: Transaction Layer

Was this helpful?