This page has instructions for authors of database drivers and ORMs 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 Client-side intervention example.
To improve the performance of transactions that fail due to contention, CockroachDB includes a set of statements (listed below) that let you retry those transactions. Retrying transactions using these statements has the following benefits:
- When you use savepoints, you "hold your place in line" between attempts. Without savepoints, you're starting from scratch every time.
- Transactions increase their priority each time they're retried, increasing the likelihood they will succeed. This has a lesser effect than #1.
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;
The transaction starts with the
SAVEPOINTstatement 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. It must be executed after
BEGINbut before the first statement that manipulates a database.
The statements in the transaction are executed.
If a statement returns a retry error (identified via the
40001error code or
"retry transaction"string at the start of the error message), you can issue the
ROLLBACK TO SAVEPOINTstatement to restart the transaction and increase the transaction's priority. Alternately, the original
SAVEPOINTstatement 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 SAVEPOINTto 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 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.
Customizing the savepoint name
New in v19.1: Set the
force_savepoint_restart session variable to
true to enable using a custom name for the restart savepoint (for example, because you are using an ORM that wants to use its own names for savepoints).
Once this variable is set, the
SAVEPOINT statement will accept any name for the savepoint, not just
cockroach_restart. This allows compatibility with existing code that uses a single savepoint per transaction as long as that savepoint occurs before any statements that access data stored in non-virtual tables.
force_savepoint_restart variable changes the semantics of CockroachDB savepoints so that
RELEASE SAVEPOINT <your-custom-name> functions as a real commit. Note that the existence of this variable and its behavior does not change the fact that CockroachDB savepoints can only be used as a part of the transaction retry protocol.
For examples showing how to use
SAVEPOINT and the other statements described on this page to implement library support for a programming language, see the following:
- Build a Java app with CockroachDB, in particular the logic in the
- The source code of the sqlalchemy-cockroachdb adapter for SQLAlchemy.