Skip to main content
A savepoint is a marker that defines the beginning of a nested transaction. This marker can be later used to commit or roll back just the effects of the nested transaction without affecting the progress of the enclosing transaction. CockroachDB supports general purpose savepoints for nested transactions, in addition to continued support for special-purpose retry savepoints.

​
Required privileges

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

​
Syntax

SAVEPOINT savepoint_name

​
Parameters

​
savepoint_name
identifier
The name of the savepoint. Nested transactions can use any name for the savepoint. Retry savepoints default to using the name cockroach_restart, but this can be customized using a session variable.

​
Examples

The examples below use the following table: 
CREATE TABLE kv (k INT PRIMARY KEY, v INT);

​
Basic usage

To establish a savepoint inside a transaction: 
SAVEPOINT foo;
Due to the rules for identifiers in the SQL grammar, SAVEPOINT foo and SAVEPOINT Foo define the same savepoint, whereas SAVEPOINT "Foo" defines another.
To roll back a transaction partially to a previously established savepoint: 
ROLLBACK TO SAVEPOINT foo;
To forget a savepoint, and keep the effects of statements executed after the savepoint was established, use RELEASE SAVEPOINT: 
RELEASE SAVEPOINT foo;
For example, the transaction below will insert the values (1,1) and (3,3) into the table, but not (2,2): 
BEGIN;
INSERT INTO kv VALUES (1,1);
SAVEPOINT my_savepoint;
INSERT INTO kv VALUES (2,2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO kv VALUES (3,3);
COMMIT;

​
Savepoints for nested transactions

Transactions can be nested using named savepoints. RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT can both refer to a savepoint “higher” in the nesting hierarchy. When this occurs, all of the savepoints “under” the nesting are automatically released / rolled back too. Specifically:
  • When a previous savepoint is rolled back, the statements entered after that savepoint are also rolled back.
  • When a previous savepoint is released, it commits; the statements entered after that savepoint are also committed.

​
Multi-level rollback with ROLLBACK TO SAVEPOINT

Savepoints can be arbitrarily nested, and rolled back to the outermost level so that every subsequent statement is rolled back. For example, this transaction does not insert anything into the table. Both INSERTs are rolled back: 
BEGIN;
SAVEPOINT foo;
INSERT INTO kv VALUES (5,5);
SAVEPOINT bar;
INSERT INTO kv VALUES (6,6);
ROLLBACK TO SAVEPOINT foo;
COMMIT;

​
Multi-level commit with RELEASE SAVEPOINT

Changes committed by releasing a savepoint commit all of the statements entered after that savepoint. For example, the following transaction inserts both (2,2) and (4,4) into the table when it releases the outermost savepoint: 
BEGIN;
SAVEPOINT foo;
INSERT INTO kv VALUES (2,2);
SAVEPOINT bar;
INSERT INTO kv VALUES (4,4);
RELEASE SAVEPOINT foo;
COMMIT;

​
Multi-level rollback and commit in the same transaction

Changes partially committed by a savepoint release can be rolled back by an outer savepoint. For example, the following transaction inserts only value (5, 5). The values (6,6) and (7,7) are rolled back. 
BEGIN;
INSERT INTO kv VALUES (5,5);
SAVEPOINT foo;
INSERT INTO kv VALUES (6,6);
SAVEPOINT bar;
INSERT INTO kv VALUES (7,7);
RELEASE SAVEPOINT bar;
ROLLBACK TO SAVEPOINT foo;
COMMIT;

​
Error recovery in nested transactions

If ROLLBACK TO SAVEPOINT is used after a database error, it can also cancel the error state of the transaction. Database errors move a transaction (or nested transaction) into an “Aborted” state. In this state, the transaction will not execute any further SQL statements. You can use ROLLBACK TO SAVEPOINT to recover from a logical error in a nested transaction. Logical errors include:
  • Unique index error (duplicate row)
  • Failed foreign key constraint check (row does not exist in referenced table)
  • Mistakes in queries (reference a column that does not exist)
In addition, you can check the status of a nested transaction using the SHOW TRANSACTION STATUS statement. For example: 
BEGIN;
SAVEPOINT error1;
INSERT INTO kv VALUES (5,5); -- Duplicate key error

ERROR: duplicate key value (k)=(5) violates unique constraint "primary"
SQLSTATE: 23505

SHOW TRANSACTION STATUS;

  TRANSACTION STATUS
----------------------
  Aborted

ROLLBACK TO SAVEPOINT error1;
INSERT INTO kv VALUES (6,6);
COMMIT;

​
Savepoint name visibility

The name of a savepoint that was rolled back over is no longer visible afterward. For example: 
BEGIN;
SAVEPOINT foo;
SAVEPOINT bar;
ROLLBACK TO SAVEPOINT foo;
RELEASE SAVEPOINT bar; -- error: savepoint does not exist

​
See also

  • RELEASE SAVEPOINT
  • ROLLBACK
  • BEGIN
  • COMMIT
  • SHOW SAVEPOINT STATUS
  • Transactions

Build docs developers (and LLMs) love

Get started for freeTalk to us