ROLLBACK statement aborts the current transaction and all of its nested transactions, discarding all transactional updates made by statements included in the transaction.
There are two ways to use ROLLBACK:
-
The
ROLLBACKstatement rolls back the entire transaction. -
The
ROLLBACK TO SAVEPOINTstatement rolls back and restarts the nested transaction started at the correspondingSAVEPOINTstatement, for working with standard savepoints. This is in addition to the existing support for working with client-side transaction retries. For examples of each usage, see: - Rollback a nested transaction
- Retry a transaction
Rollbacks to savepoints over DDL statements are only supported if you’re rolling back to a savepoint created at the beginning of the transaction.
Synopsis
Required privileges
No privileges are required to rollback a transaction. However, privileges are required for each statement within a transaction.Parameters
Savepoints and row locks
CockroachDB supports exclusive row locks.- In PostgreSQL, row locks are released/cancelled upon
ROLLBACK TO SAVEPOINT. - In CockroachDB, row locks are preserved upon
ROLLBACK TO SAVEPOINT.
ROLLBACK TO SAVEPOINT to release row locks and allow a concurrent transaction touching the same rows to proceed, this behavior will not work with CockroachDB.
Savepoints and high priority transactions
ROLLBACK TO SAVEPOINT (for either regular savepoints or “restart savepoints” defined with cockroach_restart) causes a “feature not supported” error after a DDL statement in a HIGH PRIORITY transaction, in order to avoid a transaction deadlock.
Examples
Rollback a transaction
Typically, an application conditionally executes rollbacks, but we can see their behavior by usingROLLBACK instead of COMMIT directly through SQL:
Rollback a nested transaction
TheROLLBACK TO SAVEPOINT statement rolls back and restarts the nested transaction started at the corresponding SAVEPOINT statement.
For examples showing how to use ROLLBACK TO SAVEPOINT to rollback a nested transaction, see the SAVEPOINT documentation on nested savepoints.
Retry a transaction
When using advanced client-side transaction retries, useROLLBACK TO SAVEPOINT to handle a transaction that needs to be retried (identified via the 40001 error code or restart transaction string in the error message), and then re-execute the statements you want the transaction to contain.