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
orABORT
to roll back the entire surrounding transaction.COMMIT
to commit the entire surrounding transaction. In case of error,COMMIT
is synonymous withROLLBACK
/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.