SAVEPOINT

On this page Carat arrow pointing down
Warning:
CockroachDB v19.2 is no longer supported as of May 12, 2021. For more details, refer to the Release Support Policy.

The SAVEPOINT statement defines the intent to retry transactions using the CockroachDB-provided function for client-side transaction retries. For more information, see Transaction Retries.

Warning:

CockroachDB's SAVEPOINT implementation does not support nested transactions (i.e., subtransactions). It is only used to handle transaction retries.

Synopsis

SAVEPOINT name

Required privileges

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

Parameters

Parameter Description
name The name of the savepoint. Defaults to cockroach_restart, but may be customized. For more information, see Customizing the savepoint name.

Customizing the savepoint name

Set the force_savepoint_restart session variable to true to enable using a custom name for the restart savepoint (for example, because you are using an ORM that wants to use its own names for savepoints).

Once this variable is set, the SAVEPOINT statement will accept any name for the savepoint, not just cockroach_restart. This allows compatibility with existing code that uses a single savepoint per transaction as long as that savepoint occurs before any statements that access data stored in non-virtual tables.

Warning:

The force_savepoint_restart variable changes the semantics of CockroachDB savepoints so that RELEASE SAVEPOINT <your-custom-name> functions as a real commit. Note that the existence of this variable and its behavior does not change the fact that CockroachDB savepoints can only be used as a part of the transaction retry protocol.

Example

After you BEGIN the transaction, you must create the savepoint to identify that if the transaction contends with another transaction for resources and "loses", you intend to use client-side transaction retries.

Applications using SAVEPOINT must also include functions to execute retries with ROLLBACK TO SAVEPOINT.

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

See also


Yes No
On this page

Yes No