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.
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.|
||Commit a retryable transaction.|
||Commit a non-retryable transaction or clear the connection after committing a retryable transaction.|
||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.|
In CockroachDB, a transaction is set up by surrounding SQL statements with the
To use client-side transaction retries, you should also include the
ROLLBACK TO SAVEPOINT cockroach_restart 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
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:
Retryable Errors: Errors with the code
retry transaction, which indicate the transaction failed because another concurrent or recent transaction accessed the same values. To handle these errors, you should retry the transaction.
Ambiguous Errors: Errors with the code
40003that are returned in response to
COMMITwhen 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.
For example, you might want to read values from the database to see if the transaction successfully wrote values before attempting to write the values again or, alternatively, you might write the data again without seeing if the first write attempt succeeded.
Ambiguous errors are the result of inter-node communication failures which prevent a caller from knowing with certainty whether a transaction commit succeeded. Most applications will choose to simply retry the transaction.
- SQL Errors: All other errors, which indicate that a statement in the transaction failed. For example, violating the Unique constraint generates an
23505error. After encountering these errors, you can either issue a
ROLLBACKto 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. In the event that 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. CockroachDB implements a distributed deadlock detection algorithm to discover dependency cycles. Deadlocks are resolved by allowing transactions with higher priority to abort their dependencies. Transactions which are aborted to avoid deadlock must be retried.
Transactions executed with the serializable isolation level may also require retries if they experience read/write contention with other concurrent transactions. Note that these types of transaction retries do not occur for transactions executing with the snapshot isolation level. Consider using snapshot isolation if your use case has high contention and your clients are retrying frequently. See Isolation Levels for more details.
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 any of the following types of transactions:
- Individual statements (which are treated as implicit transactions), such as:
> DELETE FROM customers WHERE id = 1;
- Transactions sent from the client as a single batch. Batching is controlled by your driver or client's behavior, but means that CockroachDB receives all of the statements as a single unit, instead of a number of requests.
From the perspective of CockroachDB, a transaction sent as a batch looks like this:
> BEGIN; DELETE FROM customers WHERE id = 1; DELETE orders WHERE customer = 1; COMMIT;
However, in your application's code, batched transactions are often just multiple statements sent at once. For example, in Go, this transaction would be sent as a single batch (and automatically retried):
db.Exec( "BEGIN; DELETE FROM customers WHERE id = 1; DELETE orders WHERE customer = 1; COMMIT;" )
In these cases, CockroachDB infers there is nothing conditional about these values, so it can continue to retry the transaction with the same values it originally received.
However, if the transaction relies on conditional logic, you should instead write your transactions to use client-side intervention. This provides an opportunity for the client to check the transaction's conditions before deciding whether or not to retry the transaction, as well as update any values.
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 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
To improve the performance of transactions that fail due to contention, 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
COMMIT afterward to clear the connection for the next transaction.
You can find examples of this in the Syntax section of this page or in our Build an App with CockroachDB tutorials.
- 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 supports two transaction isolation levels:
SNAPSHOT. By default, transactions use the
SERIALIZABLE isolation level, but the client 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. However, due to CockroachDB's transaction model,
SERIALIZABLE isolation may require more transaction restarts, especially in the presence of high contention between concurrent transactions. Consider using
SNAPSHOT isolation for high contention workloads.
SNAPSHOT isolation, 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, but in cases where write skew conditions are unlikely, this isolation level can be highly performant.
Comparison to ANSI SQL Isolation Levels
CockroachDB uses slightly different isolation levels than ANSI SQL isolation levels.
REPEATABLE READis an alias for
READ COMMITTEDare aliases for
REPEATABLE READdoes not equate to
SNAPSHOTin CockroachDB. We made this choice to avoid potential confusion between them and the anomalies they can introduce.
REPEATABLE READpermits the phantom read anomaly, while
SNAPSHOTpermits the write skew anomaly.
- The CockroachDB
SERIALIZABLElevel is stronger than the ANSI SQL
REPEATABLE READlevel and equivalent to the ANSI SQL
- The CockroachDB
SNAPSHOTlevel is stronger than the ANSI SQL
For more information about the relationship between these levels, see A Critique of ANSI SQL Isolation Levels.