RELEASE SAVEPOINT

The RELEASE SAVEPOINT statement commits the 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 .

Synopsis

Required privileges

No 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. default to using the name cockroach_restart, but this can be customized using a session variable. For more information, see .

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:

• to roll back to the previous savepoint.
• to roll back the entire surrounding transaction.
• to commit the entire surrounding transaction. In case of error, COMMIT is synonymous with 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 immediately followed by RELEASE.

Examples

Commit a nested transaction by releasing a savepoint

This example uses the .

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

> 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 . For more information, see .

After declaring a retry savepoint, commit the transaction with RELEASE SAVEPOINT and then prepare the connection for the next transaction with :

> 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 .

Note that you can to something other than cockroach_restart with a session variable if you need to.

See also