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.
Each of the following SQL statements control transactions in some way.
|Initiate a transaction, as well as control its priority and isolation level.
|Control a transaction's priority and isolation level.
|Declare the transaction as retryable. This lets you retry the transaction if it doesn't succeed because a higher priority transaction concurrently or recently accessed the same values.
RELEASE SAVEPOINT cockroach_restart
|Commit a retryable transaction.
|Commit a non-retryable transaction or clear the connection after committing a retryable transaction.
ROLLBACK TO SAVEPOINT cockroach_restart
|Handle retryable errors by rolling back a transaction's changes and increasing its priority.
|Abort a transaction and roll the database back to its state before the transaction began.
|Display the current transaction settings.
To use client-side transaction retries, you should also include the
ROLLBACK TO SAVEPOINT cockroach_restart and
RELEASE SAVEPOINT statements.
> SAVEPOINT cockroach_restart;
> RELEASE SAVEPOINT cockroach_restart;
At any time before it's committed, you can abort the transaction by executing the
Clients using transactions must also include logic to handle retries.
To handle errors in transactions, you should check for the following types of server-side errors:
|Errors with the code
40001 or string
retry transaction, which indicate that a transaction failed because it conflicted with another concurrent or recent transaction accessing the same data. The transaction needs to be retried by the client. See client-side transaction retries for more details.
|Errors with the code
40003 that are returned in response to
RELEASE SAVEPOINT (or
COMMIT when not using
SAVEPOINT), 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. See here for more about this kind of error.
|All other errors, which indicate that a statement in the transaction failed. For example, violating the Unique constraint generates an
23505 error. After encountering these errors, you can either issue a
ROLLBACK to abort the transaction and revert the database to its state before the transaction began.
If you want to attempt the same set of statements again, you must begin a completely new transaction.
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.
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. (However, it's possible to mitigate read-write conflicts by performing reads using
AS OF SYSTEM TIME.)
There are two cases for handling transaction retries:
- Automatic retries, which CockroachDB processes for you.
- Client-side intervention, which your application must handle.
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. 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 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
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;
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 if the accumulated results overflow an internal buffer, 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:
When the client/driver is using the PostgreSQL Extended Query protocol, a batch is made up of all queries sent in between two
Syncmessages. Many drivers support such batches through explicit batching constructs.
The the client/driver is using the PostgreSQL Simple Query protocol, a batch is made up 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;" )
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 client-side intervention.
Your application should include client-side retry handling when the statements are sent individually, such as:
> UPDATE products SET inventory = 0 WHERE sku = '8675309';
> INSERT INTO orders (customer, status) VALUES (1, 'new');
To indicate a transaction must be retried, CockroachDB surfaces an error with the code
40001 and an error message that begins with the string
To handle these types of errors you have two options:
Recommended: Use the
SAVEPOINT cockroach_restartfunctions to create retryable transactions. Retryable transactions can improve performance because their priority's increased each time they are retried, making them more likely to succeed the longer they're in your system.
For more information, see Client-Side Transaction Retries.
Abort the transaction using
ROLLBACK, and then reissue all of the statements in the transaction. This does not automatically increase the transaction's priority, so it's possible in high-contention workloads for transactions to take an incredibly long time to succeed.
Client-Side Transaction Retries
As one way to improve the performance of contended transactions, CockroachDB includes a set of statements that let you retry those transactions. Retrying transactions has the benefit of increasing their priority each time they're retried, increasing their likelihood to succeed.
Retried transactions are also issued at a later timestamp, so the transaction now operates on a later snapshot of the database, so the reads might return updated data.
Implementing client-side retries requires three statements:
SAVEPOINT cockroach_restartdeclares the client's intent to retry the transaction if there are contention errors. It must be executed after
BEGINbut before the first statement that manipulates a database.
ROLLBACK TO SAVEPOINT cockroach_restartis used when your application detects
retry transactionerrors. It provides you a chance to "retry" the transaction by rolling the database's state back to the beginning of the transaction and increasing the transaction's priority.
ROLLBACK TO SAVEPOINT cockroach_restart, you must issue any statements you want the transaction to contain. Typically, this means recalculating values and reissuing a similar set of statements to the previous attempt.
RELEASE SAVEPOINT cockroach_restartcommits the transaction. At this point, CockroachDB checks to see if the transaction contends with others for access to the same values; the highest priority transaction succeeds, and the others return
You must also execute
COMMITafterward to clear the connection for the next transaction.
- Go developers can use the
crdbpackage of the CockroachDB Go client. For more information, see Build a Go App with CockroachDB.
- Python developers can use the
sqlalchemypackage. For more information, see our blog post Building an Application With CockroachDB and SQLAlchemy.
It's also important to note that retried transactions are restarted at a later timestamp. This means that the transaction operates on a later snapshot of the database and related reads might retrieve updated data.
For greater detail, here's the process a retryable transaction goes through.
The transaction starts with the
SAVEPOINT cockroach_restartstatement declares the intention to retry the transaction in the case of contention errors. Note that CockroachDB's savepoint implementation does not support all savepoint functionality, such as nested transactions.
The statements in the transaction are executed.
If a statement returns a retryable error (identified via the
40001error code or
retry transactionstring at the start of the error message), you can issue the
ROLLBACK TO SAVEPOINT cockroach_restartstatement to restart the transaction. Alternately, the original
SAVEPOINT cockroach_restartstatement can be reissued to restart the transaction.
You must now issue the statements in the transaction again.
In cases where you do not want the application to retry the transaction, you can simply issue
ROLLBACKat this point. Any other statements will be rejected by the server, as is generally the case after an error has been encountered and the transaction has not been closed.
Once the transaction executes all statements without encountering contention errors, execute
RELEASE SAVEPOINT cockroach_restartto commit the changes. If this succeeds, all changes made by the transaction become visible to subsequent transactions and are guaranteed to be durable if a crash occurs.
In some cases, the
RELEASE SAVEPOINTstatement itself can fail with a retryable error, mainly because transactions in CockroachDB only realize that they need to be restarted when they attempt to commit. If this happens, the retryable error is handled as described in step 4.
Each transaction is controlled by two parameters: its priority and its isolation level. The following two sections detail these further.
Every transaction in CockroachDB is assigned an initial priority. By default, that priority is
NORMAL, but for transactions that should be given preference in high-contention scenarios, the client can set the priority within the
> BEGIN PRIORITY <LOW | NORMAL | HIGH>;
Alternately, the client can set the priority immediately after the transaction is started as follows:
> SET TRANSACTION PRIORITY <LOW | NORMAL | HIGH>;
The client can also display the current priority of the transaction with
SHOW TRANSACTION PRIORITY.
CockroachDB efficiently supports the strongest ANSI transaction isolation level:
SERIALIZABLE. All other ANSI transaction isolaton levels (e.g.,
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. CockroachDB continues to support an additional non-ANSI isolation level,
SNAPSHOT, although it is deprecated. Clients can explicitly set a transaction's isolation when starting the transaction:
> BEGIN ISOLATION LEVEL <SERIALIZABLE | SNAPSHOT>;
Alternately, the client can set the isolation level immediately after the transaction is started:
> SET TRANSACTION ISOLATION LEVEL <SERIALIZABLE | SNAPSHOT>;
The client can also display the current isolation level of the transaction with
SHOW TRANSACTION ISOLATION LEVEL.
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. In order to prevent write skew anomalies,
SERIALIZABLE isolation may require transaction restarts.
SNAPSHOT isolation (deprecated), a transaction behaves as if it were reading the state of the database consistently at a fixed point in time. Unlike the
SNAPSHOT isolation permits the write skew anomaly. This isolation level is still supported for backwards compatibility, but you should avoid using it. It provides little benefit in terms of performance and can result in inconsistent state under certain complex workloads. Concurrency-based attacks can coerce inconsistencies into meaningfully adverse effects to system state. For this same reason, CockroachDB upgrades all requests for the much weaker ANSI
READ COMMITTED, and
REPEATABLE READ isolation levels into
Comparison to ANSI SQL Isolation Levels
CockroachDB uses slightly different isolation levels than ANSI SQL isolation levels.
READ COMMITTED, and
REPEATABLE READare aliases for
- The CockroachDB
SERIALIZABLElevel is stronger than the ANSI SQL
READ COMMITTED, and
REPEATABLE READlevels and equivalent to the ANSI SQL
- The CockroachDB
SNAPSHOTlevel (deprecated) is stronger than the ANSI SQL
For more information about the relationship between these levels, see A Critique of ANSI SQL Isolation Levels.