RELEASE SAVEPOINT

On this page Carat arrow pointing down

New in v20.1: 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

RELEASE SAVEPOINT name

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:

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

Note:

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.

icon/buttons/copy
> 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:

icon/buttons/copy
> 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.

See also


Yes No
On this page

Yes No