• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. SAVEPOINT

SAVEPOINT

Function

SAVEPOINT establishes a new savepoint within the current transaction.

A savepoint is a special mark inside a transaction that allows all commands that are executed after it was rolled back, restoring the transaction state to what it was at the time of the savepoint.

Precautions

Use ROLLBACK TO SAVEPOINT to roll back to a savepoint. Use RELEASE SAVEPOINT to destroy a savepoint but keep the effects of the commands executed after the savepoint was established.

Savepoints can only be established when inside a transaction block. There can be multiple savepoints defined within a transaction.

SAVEPOINT cannot be used for functions, anonymous blocks, or stored procedures.

In the case of an error caused by unexpected termination of a distributed thread or process due to node fault or loss of connectivity, the transaction cannot be rolled back to the established savepoint. Instead, the entire transaction will be rolled back.

According to the SQL standard, a savepoint is destroyed automatically when another savepoint with the same name is established. In DWS, old savepoints are kept, though only the most recent one will be used for rollback or release. Releasing the newer savepoint with RELEASE SAVEPOINT will cause the older one to again become accessible to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT. Except for this, SAVEPOINT is fully SQL conforming.

Syntax

SAVEPOINT savepoint_name;

Parameter Description

savepoint_name

The name of a new savepoint

Example

-- Create a table:
CREATE TABLE table1(a int);

-- Start a transaction:
START TRANSACTION;

-- Insert data:
INSERT INTO table1 VALUES (1);

-- Establish a savepoint:
SAVEPOINT my_savepoint;

-- Insert data:
INSERT INTO table1 VALUES (2);

-- Roll back to the savepoint:
ROLLBACK TO SAVEPOINT my_savepoint;

-- Insert data:
INSERT INTO table1 VALUES (3);

-- Commit the transaction:
COMMIT;

-- Query the table content, which should contain 1 and 3 but not 2, because 2 has been rolled back.
SELECT * FROM table1;

-- Delete the table:
DROP TABLE table1;

-- Create a table:
CREATE TABLE table2(a int);

-- Start a transaction:
START TRANSACTION;

-- Insert data:
INSERT INTO table2 VALUES (3);

-- Establish a savepoint:
SAVEPOINT my_savepoint;

-- Insert data:
INSERT INTO table2 VALUES (4);

-- Roll back to the savepoint:
RELEASE SAVEPOINT my_savepoint;

-- Commit the transaction:
COMMIT;

-- Query the table content, which should contain both 3 and 4.
SELECT * FROM table2;

-- Delete the table:
DROP TABLE table2;