> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Transactions

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

CockroachDB supports bundling multiple SQL statements into a single all-or-nothing transaction. Each transaction guarantees [ACID semantics](https://en.wikipedia.org/wiki/ACID) 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](#isolation-levels).

For a detailed discussion of CockroachDB transaction semantics, see [How CockroachDB Does Distributed Atomic Transactions](https://www.cockroachlabs.com/blog/how-cockroachdb-distributes-atomic-transactions/) and [Serializable, Lockless, Distributed: Isolation in CockroachDB](https://www.cockroachlabs.com/blog/serializable-lockless-distributed-isolation-cockroachdb/). The explanation of the transaction model described in this blog post is slightly out of date. See the [Transaction Retries](#transaction-retries) section for more details.

## SQL statements

The following SQL statements control transactions.

| Statement                                                                        | Description                                                                                                                                                                                          |
| -------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <InternalLink path="begin-transaction">`BEGIN`</InternalLink>                    | Initiate a transaction, as well as control its [priority](#transaction-priorities).                                                                                                                  |
| <InternalLink path="set-transaction">`SET TRANSACTION`</InternalLink>            | Control a transaction's [priority](#transaction-priorities).                                                                                                                                         |
| <InternalLink path="commit-transaction">`COMMIT`</InternalLink>                  | Commit a regular transaction, or clear the connection after committing a transaction using the <InternalLink path="advanced-client-side-transaction-retries">advanced retry protocol</InternalLink>. |
| <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink>              | Abort a transaction and roll the database back to its state before the transaction began.                                                                                                            |
| <InternalLink path="show-vars">`SHOW`</InternalLink>                             | Display the current transaction settings.                                                                                                                                                            |
| <InternalLink path="savepoint">`SAVEPOINT`</InternalLink>                        | Used for [nested transactions](#nested-transactions); also used to implement <InternalLink path="advanced-client-side-transaction-retries">advanced client-side transaction retries</InternalLink>.  |
| <InternalLink path="release-savepoint">`RELEASE SAVEPOINT`</InternalLink>        | Commit a [nested transaction](#nested-transactions); also used for <InternalLink path="advanced-client-side-transaction-retries">retryable transactions</InternalLink>.                              |
| <InternalLink path="rollback-transaction">`ROLLBACK TO SAVEPOINT`</InternalLink> | Roll back a [nested transaction](#nested-transactions); also used to handle <InternalLink path="advanced-client-side-transaction-retries">retryable transaction errors</InternalLink>.               |

<Note>
  If you are using a framework or library that does not have <InternalLink path="advanced-client-side-transaction-retries">advanced retry logic</InternalLink> built in, you should implement an application-level retry loop with exponential backoff. See <InternalLink path="transaction-retry-error-reference#client-side-retry-handling">Client-side retry handling</InternalLink>.
</Note>

## Syntax

In CockroachDB, a transaction is set up by surrounding SQL statements with the <InternalLink path="begin-transaction">`BEGIN`</InternalLink> and <InternalLink path="commit-transaction">`COMMIT`</InternalLink> statements.

To use <InternalLink path="advanced-client-side-transaction-retries">advanced client-side transaction retries</InternalLink>, you should also include the <InternalLink path="savepoint">`SAVEPOINT`</InternalLink>, <InternalLink path="rollback-transaction">`ROLLBACK TO SAVEPOINT`</InternalLink> and <InternalLink path="release-savepoint">`RELEASE SAVEPOINT`</InternalLink> statements.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink> statement.

Clients using transactions must also include logic to handle [retries](#transaction-retries).

## Error handling

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

| Type                         | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ---------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Transaction Retry Errors** | Errors with the code `40001` and string `restart transaction`, which indicate that a transaction failed because it could not be placed in a <InternalLink path="demo-serializable">serializable ordering</InternalLink> of transactions by CockroachDB. For details on transaction retry errors and how to resolve them, see the <InternalLink path="transaction-retry-error-reference#actions-to-take">Transaction Retry Error Reference</InternalLink>.                                                                                                          |
| **Ambiguous Errors**         | Errors with the code `40003` which indicate that the state of the transaction is ambiguous, i.e., you cannot assume it either committed or failed. How you handle these errors depends on how you want to resolve the ambiguity. For information about how to handle ambiguous errors, see <InternalLink path="common-errors#result-is-ambiguous">here</InternalLink>.                                                                                                                                                                                             |
| **SQL Errors**               | All other errors, which indicate that a statement in the transaction failed. For example, violating the `UNIQUE` constraint generates a `23505` error. After encountering these errors, you can either issue a <InternalLink path="commit-transaction">`COMMIT`</InternalLink> or <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink> to abort the transaction and revert the database to its state before the transaction began.<br /><br />If you want to attempt the same set of statements again, you must begin a completely new transaction. |

## Transaction retries

Transactions may require retries due to <InternalLink path="performance-best-practices-overview">contention</InternalLink> with another concurrent or recent transaction attempting to write to the same data.

There are two cases in which transaction retries can occur:

* [Automatic retries](#automatic-retries), which CockroachDB silently processes for you.
* <InternalLink path="transaction-retry-error-reference#client-side-retry-handling">Client-side retries</InternalLink>, which your application must handle after receiving a <InternalLink path="transaction-retry-error-reference">*transaction retry error*</InternalLink>.

To reduce the need for transaction retries, see <InternalLink path="performance-best-practices-overview#reduce-transaction-contention">Reduce transaction contention</InternalLink>.

### Automatic retries

CockroachDB automatically retries individual statements (implicit transactions) and [transactions sent from the client as a single batch](#batched-statements), 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.

You can increase the occurrence of automatic retries as a way to <InternalLink path="transaction-retry-error-reference#minimize-transaction-retry-errors">minimize transaction retry errors</InternalLink>:

* <InternalLink path="transactions#batched-statements">Send statements in transactions as a single batch</InternalLink>. Batching allows CockroachDB to <InternalLink path="transactions#automatic-retries">automatically retry</InternalLink> a transaction when <InternalLink path="architecture/transaction-layer#read-refreshing">previous reads are invalidated</InternalLink> at a <InternalLink path="architecture/transaction-layer#timestamp-cache">pushed timestamp</InternalLink>. When a multi-statement transaction is not batched, and takes more than a single round trip, CockroachDB cannot automatically retry the transaction. For an example showing how to break up large transactions in an application, see <InternalLink path="build-a-python-app-with-cockroachdb-sqlalchemy#break-up-large-transactions-into-smaller-units-of-work">Break up large transactions into smaller units of work</InternalLink>.

* Limit the size of the result sets of your transactions to under 16KB, so that CockroachDB is more likely to <InternalLink path="transactions#automatic-retries">automatically retry</InternalLink> when <InternalLink path="architecture/transaction-layer#read-refreshing">previous reads are invalidated</InternalLink> at a <InternalLink path="architecture/transaction-layer#timestamp-cache">pushed timestamp</InternalLink>. When a transaction returns a result set over 16KB, even if that transaction has been sent as a single batch, CockroachDB cannot automatically retry the transaction. You can change the results buffer size for all new sessions using the `sql.defaults.results_buffer.size` <InternalLink path="cluster-settings">cluster setting</InternalLink>, or for a specific session using the `results_buffer_size` <InternalLink path="connection-parameters#additional-connection-parameters">connection parameter</InternalLink>.

<Note>
  Use <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL SET {sessionvar} = {val}`</InternalLink> instead of the `sql.defaults.*` <InternalLink path="cluster-settings">cluster settings</InternalLink>. This allows you to set a default value for all users for any <InternalLink path="set-vars">session variable</InternalLink> that applies during login, making the `sql.defaults.*` cluster settings redundant.
</Note>

#### 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](https://www.postgresql.org/docs/10/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY), 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](https://www.postgresql.org/docs/10/static/protocol-flow.html#id-1.10.5.7.4), 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):

   ```go theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   db.Exec(
     "BEGIN;

     DELETE FROM customers WHERE id = 1;

     DELETE orders WHERE customer = 1;

     COMMIT;"
   )
   ```

Within a batch of statements, CockroachDB infers that the statements are not
conditional on the results of previous statements, so it can retry all of them.
Of course, if the transaction relies on conditional logic (e.g., statement 2 is
executed only for some results of statement 1), then the transaction cannot be
all sent to CockroachDB as a single batch. In these common cases, CockroachDB
cannot retry, say, statement 2 in isolation. Since results for statement 1 have
already been delivered to the client by the time statement 2 is forcing the
transaction to retry, the client needs to be involved in retrying the whole
transaction and so you should write your transactions to use
<InternalLink path="transaction-retry-error-reference#client-side-retry-handling">client-side retry handling</InternalLink>.

The <InternalLink path="set-vars">`enable_implicit_transaction_for_batch_statements` session variable</InternalLink> defaults to `true`. This means that any batch of statements is treated as an implicit transaction, so the `BEGIN`/`COMMIT` commands are not needed to group all the statements in one transaction.

#### Bounded staleness reads

In the event <InternalLink path="follower-reads#bounded-staleness-reads">bounded staleness reads</InternalLink> are used along with either the <InternalLink path="functions-and-operators">`with_min_timestamp` function or the `with_max_staleness` function</InternalLink> and the `nearest_only` parameter is set to `true`, the query will throw an error if it can't be served by a nearby replica.

## Nested transactions

CockroachDB supports the nesting of transactions using <InternalLink path="savepoint">savepoints</InternalLink>.  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 <InternalLink path="install-client-drivers">ORM</InternalLink>.  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 <InternalLink path="commit-transaction">`COMMIT`</InternalLink> and <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink> are used to commit and discard entire transactions, respectively, <InternalLink path="release-savepoint">`RELEASE SAVEPOINT`</InternalLink> and <InternalLink path="rollback-transaction#rollback-a-nested-transaction">`ROLLBACK TO SAVEPOINT`</InternalLink> are used to commit and discard nested transactions.  This relationship is shown in the following table:

| Statement                                                                                                      | Effect                                                |
| -------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- |
| <InternalLink path="commit-transaction">`COMMIT`</InternalLink>                                                | Commit an entire transaction.                         |
| <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink>                                            | Discard an entire transaction.                        |
| <InternalLink path="release-savepoint">`RELEASE SAVEPOINT`</InternalLink>                                      | Commit (really, forget) the named nested transaction. |
| <InternalLink path="rollback-transaction#rollback-a-nested-transaction">`ROLLBACK TO SAVEPOINT`</InternalLink> | Discard the changes in the named nested transaction.  |

For more information, including examples showing how to use savepoints to create nested transactions, see <InternalLink path="savepoint#examples">the savepoints documentation</InternalLink>.

## Transaction priorities

Every transaction in CockroachDB is assigned an initial **priority**. By default, the transaction priority is `NORMAL`.

### Set transaction priority

For transactions that should be given higher or lower preference in <InternalLink path="performance-best-practices-overview#transaction-contention">high-contention scenarios</InternalLink>, you can set the priority in the <InternalLink path="begin-transaction">`BEGIN`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BEGIN PRIORITY <LOW | NORMAL | HIGH>;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET TRANSACTION PRIORITY <LOW | NORMAL | HIGH>;
```

To set the default transaction priority for all transactions in a session, use the `default_transaction_priority` <InternalLink path="set-vars">session variable</InternalLink>. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET default_transaction_priority = 'high';
```

<Note>
  When two transactions contend for the same resources indirectly, they may create a dependency cycle leading to a deadlock situation, where both transactions are waiting on the other to finish. In these cases, CockroachDB allows the transaction with higher priority to abort the other, which must then retry. On retry, the transaction inherits the higher priority. This means that each retry makes a transaction more likely to succeed in the event it again experiences deadlock.
</Note>

### View transaction priority

`transaction_priority` is a read-only <InternalLink path="show-vars">session variable</InternalLink>.

To view the current priority of a transaction, use `SHOW transaction_priority` or <InternalLink path="show-vars">`SHOW TRANSACTION PRIORITY`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW transaction_priority;
```

```
  transaction_priority
------------------------
  high
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TRANSACTION PRIORITY;
```

```
  transaction_priority
------------------------
  high
```

## 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,
[ACIDRain: Concurrency-Related Attacks on Database-Backed Web Applications](http://www.bailis.org/papers/acidrain-sigmod2017.pdf) has demonstrated that the use
of weak isolation levels results in substantial vulnerability to concurrency-based attacks.

For a detailed discussion of isolation in CockroachDB transactions, see [Serializable, Lockless, Distributed: Isolation in CockroachDB](https://www.cockroachlabs.com/blog/serializable-lockless-distributed-isolation-cockroachdb/).

#### 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](https://wikipedia.org/wiki/Snapshot_isolation) anomalies, `SERIALIZABLE` isolation may require transaction restarts. For a demonstration of `SERIALIZABLE` preventing write skew, see <InternalLink path="demo-serializable">Serializable Transactions</InternalLink>.

### Comparison to ANSI SQL isolation levels

CockroachDB uses slightly different isolation levels than [ANSI SQL isolation levels](https://wikipedia.org/wiki/Isolation_\(database_systems\)#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](https://arxiv.org/ftp/cs/papers/0701/0701157.pdf).

## Limit the number of rows written or read in a transaction

You can limit the number of rows written or read in a transaction at the cluster or session level. This allows you configure CockroachDB to log or reject statements that could destabilize a cluster or violate application best practices.

Use the <InternalLink path="cluster-settings">cluster settings</InternalLink> `sql.defaults.transaction_rows_written_log`,
`sql.defaults.transaction_rows_written_err`, `sql.defaults.transaction_rows_read_log`, and
`sql.defaults.transaction_rows_read_err` and <InternalLink path="set-vars">session settings</InternalLink> `transaction_rows_written_log`,
`transaction_rows_written_err`, `transaction_rows_read_log`, and
`transaction_rows_read_err` to limit the number of rows written or read in a
transaction. When the `log` limit is reached, the transaction is logged to the `SQL_PERF` channel.
When the `err` limit is reached, the transaction is rejected. The limits are enforced after each
statement of a transaction has been fully executed.

The "write" limits apply to `INSERT`, `INSERT INTO SELECT FROM`, `INSERT ON CONFLICT`, `UPSERT`, `UPDATE`,
and `DELETE` SQL statements. The "read" limits apply to the `SELECT`
statement in addition to the statements subject to the "write" limits. The limits **do not**
apply to `CREATE TABLE AS`, `SELECT`, `IMPORT`, `TRUNCATE`, `DROP`, `ALTER TABLE`, `BACKUP`,
`RESTORE`, or `CREATE STATISTICS` statements.

<Note>
  Enabling `transaction_rows_read_err` disables a performance optimization for mutation statements in implicit transactions where CockroachDB can auto-commit without additional network round trips.
</Note>

Use <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL SET {sessionvar} = {val}`</InternalLink> instead of the `sql.defaults.*` <InternalLink path="cluster-settings">cluster settings</InternalLink>. This allows you to set a default value for all users for any <InternalLink path="set-vars">session variable</InternalLink> that applies during login, making the `sql.defaults.*` cluster settings redundant.

## See also

* <InternalLink path="begin-transaction">`BEGIN`</InternalLink>
* <InternalLink path="commit-transaction">`COMMIT`</InternalLink>
* <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink>
* <InternalLink path="savepoint">`SAVEPOINT`</InternalLink>
* <InternalLink path="release-savepoint">`RELEASE SAVEPOINT`</InternalLink>
* <InternalLink path="show-vars">`SHOW`</InternalLink>
* <InternalLink path="build-a-java-app-with-cockroachdb">Retryable transaction example code in Java using JDBC</InternalLink>
* <InternalLink path="ui-transactions-page">DB Console Transactions Page</InternalLink>
* <InternalLink path="architecture/transaction-layer">CockroachDB Architecture: Transaction Layer</InternalLink>
* <InternalLink path="transaction-retry-error-reference">Transaction retry error reference</InternalLink>