RELEASE SAVEPOINT

The RELEASE SAVEPOINT statement commits the nested transaction starting at the corresponding SAVEPOINT statement using the same savepoint name, including all its nested sub-transactions. This is in addition to continued support for working with retry savepoints.

Synopsis

Required privileges

No privileges are required to release a savepoint. However, privileges are required for each statement within a transaction.

Parameters

Parameter	Description
name	The name of the savepoint. Retry savepoints default to using the name `cockroach_restart`, but this can be customized using a session variable. For more information, see Customizing the retry savepoint name.

Handling errors

The RELEASE SAVEPOINT statement is invalid after the nested transaction has encountered an error. After an error, the following statements can be used:

ROLLBACK TO SAVEPOINT to roll back to the previous savepoint.
ROLLBACK or ABORT to roll back the entire surrounding transaction.
COMMIT to commit the entire surrounding transaction. In case of error, COMMIT is synonymous with ROLLBACK/ABORT and also rolls back the entire transaction.

When a (sub-)transaction encounters a retry error, the client should repeat ROLLBACK TO SAVEPOINT and the statements in the transaction until the statements complete without error, then issue RELEASE. To completely remove the marker of a nested transaction after it encounters an error and begin other work in the outer transaction, use ROLLBACK TO SAVEPOINT immediately followed by RELEASE.

Examples

Commit a nested transaction by releasing a savepoint

This example uses the MovR data set.

In the example below, we roll back the inner nested transaction (marked by the savepoint lower) and release (commit) the outer savepoint higher, which raises the promo code discount to 15% using CockroachDB’s JSONB functions.

> BEGIN;
SAVEPOINT higher;
UPDATE promo_codes SET rules = jsonb_set(rules, '{value}', '"15%"') WHERE rules @> '{"type": "percent_discount"}';
SAVEPOINT lower;
UPDATE promo_codes SET rules = jsonb_set(rules, '{value}', '"7.5%"') WHERE rules @> '{"type": "percent_discount"}';
ROLLBACK TO SAVEPOINT lower;
RELEASE SAVEPOINT higher;
COMMIT;

COMMIT

Commit a transaction by releasing a retry savepoint

A savepoint defined with the name cockroach_restart is a “retry savepoint” and is used to implement advanced client-side transaction retries. For more information, see Retry savepoints. After declaring a retry savepoint, commit the transaction with RELEASE SAVEPOINT and then prepare the connection for the next transaction with COMMIT:

> BEGIN;
SAVEPOINT cockroach_restart;
UPDATE products SET inventory = 0 WHERE sku = '8675309';
INSERT INTO orders (customer, sku, status) VALUES (1001, '8675309', 'new');
RELEASE SAVEPOINT cockroach_restart;
COMMIT;

Applications using SAVEPOINT for client-side transaction retries must also include functions to execute retries with ROLLBACK TO SAVEPOINT. Note that you can customize the retry savepoint name to something other than cockroach_restart with a session variable if you need to.

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Required privileges

Parameters

Handling errors

Examples

Commit a nested transaction by releasing a savepoint

Commit a transaction by releasing a retry savepoint

See also

​Synopsis

​Required privileges

​Parameters

​Handling errors

​Examples

​Commit a nested transaction by releasing a savepoint

​Commit a transaction by releasing a retry savepoint

​See also

Synopsis

Required privileges

Parameters

Handling errors

Examples

Commit a nested transaction by releasing a savepoint

Commit a transaction by releasing a retry savepoint

See also