Advanced Client-side Transaction Retries

This page has instructions for authors of who would like to implement client-side retries in their database driver or ORM for maximum efficiency and ease of use by application developers.

If you are an application developer who needs to implement an application-level retry loop, see the .

Overview

To improve the performance of transactions that fail due to , CockroachDB includes a set of statements (listed below) that let you retry those transactions. Retrying transactions using these statements has the benefit that when you use savepoints, you “hold your place in line” between attempts. Without savepoints, you’re starting from scratch every time.

How transaction retries work

A retryable transaction goes through the process described below, which maps to the following SQL statements:

> BEGIN;                                  -- #1
> SAVEPOINT cockroach_restart;            -- #2
-- ... various transaction statements ... -- #3
> RELEASE SAVEPOINT cockroach_restart;    -- #5 (Or #4, ROLLBACK, in case of retry error)
> COMMIT;

1. The transaction starts with the statement.

2. The statement shown here is a retry savepoint; that is, it declares the intention to retry the transaction in the case of contention errors. It must be executed after , but before the first statement that manipulates a database. Although are supported in versions of CockroachDB 20.1 and later, a retry savepoint must be the outermost savepoint in a transaction.

3. The statements in the transaction are executed.

4. If a statement returns a retry error (identified via the 40001 error code or "restart transaction" string at the start of the error message), you can issue the statement to restart the transaction. Alternately, the original statement 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 issue at 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.

5. Once the transaction executes all statements without encountering contention errors, execute to 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 statement itself can fail with a retry error, mainly because transactions in CockroachDB only realize that they need to be restarted when they attempt to commit. If this happens, the retry error is handled as described in step 4.

Retry savepoints

A savepoint defined with the name cockroach_restart is a “retry savepoint” and is used to implement advanced client-side transaction retries. A retry savepoint differs from a as follows:

• It must be the outermost savepoint in the transaction.
• After a successful , a retry savepoint does not allow further use of the transaction. The next statement must be a .
• It cannot be nested. Issuing SAVEPOINT cockroach_restart two times in a row only creates a single savepoint marker (this can be verified with ). Issuing SAVEPOINT cockroach_restart after ROLLBACK TO SAVEPOINT cockroach_restart reuses the marker instead of creating a new one.

Note that you can customize the retry savepoint name to something other than cockroach_restart with a session variable if you need to.

Customizing the retry savepoint name

Set the force_savepoint_restart to true to enable using a custom name for the .

Once this variable is set, the statement will accept any name for the retry savepoint, not just cockroach_restart. In addition, it causes every savepoint name to be equivalent to cockroach_restart, therefore disallowing the use of .

This feature exists to support applications that want to use the , but cannot customize the name of savepoints to be cockroach_restart. For example, this may be necessary because you are using an ORM that requires its own names for savepoints.

Examples

For examples showing how to use and the other statements described on this page to implement library support for a programming language, see the following:

• , in particular the logic in the runSQL method.
• The source code of the sqlalchemy-cockroachdb adapter for SQLAlchemy.

See also