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
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 SAVEPOINTto roll back to the previous savepoint.ROLLBACKorABORTto roll back the entire surrounding transaction.COMMITto commit the entire surrounding transaction. In case of error,COMMITis synonymous withROLLBACK/ABORTand 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.
See also
Was this helpful?
