ROLLBACK

The ROLLBACK aborts the current and all of its , discarding all transactional updates made by statements included in the transaction.

There are two ways to use ROLLBACK:

• The ROLLBACK statement rolls back the entire transaction.

• The ROLLBACK TO SAVEPOINT statement rolls back and restarts the nested transaction started at the corresponding SAVEPOINT statement, for working with . This is in addition to the existing support for working with . 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 are required to rollback a transaction. However, privileges are required for each statement within a transaction.

Parameters

Parameter	Description
TO SAVEPOINT cockroach_restart	If using , retry the transaction. You should execute this statement when a transaction returns a 40001 / retry transaction error.
TO SAVEPOINT <name>	If using , roll back and restart the started at the corresponding SAVEPOINT statement.

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.

This is an architectural difference that may or may not be lifted in a later CockroachDB version.

The code of client applications that rely on row locks must be reviewed and possibly modified to account for this difference. In particular, if an application is relying on 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

(for either regular savepoints or “restart savepoints” defined with cockroach_restart) causes a “feature not supported” error after a DDL statement in a , in order to avoid a transaction deadlock.

Examples

Rollback a transaction

Typically, an application conditionally executes rollbacks, but we can see their behavior by using ROLLBACK instead of COMMIT directly through SQL:

> SELECT * FROM accounts;

+----------+---------+
|   name   | balance |
+----------+---------+
| Marciela |    1000 |
+----------+---------+

> BEGIN;

> UPDATE accounts SET balance = 2500 WHERE name = 'Marciela';

> ROLLBACK;

> SELECT * FROM accounts;

+----------+---------+
|   name   | balance |
+----------+---------+
| Marciela |    1000 |
+----------+---------+

Rollback a nested transaction

The ROLLBACK TO SAVEPOINT statement rolls back and restarts the started at the corresponding SAVEPOINT statement.

For examples showing how to use ROLLBACK TO SAVEPOINT to rollback a nested transaction, see .

Retry a transaction

When using , use ROLLBACK 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.

> ROLLBACK TO SAVEPOINT cockroach_restart;

For examples of retrying transactions in an application, check out the transaction code samples in our tutorials.

See also