SAVEPOINT

Purpose

You can use this statement to create a savepoint in a transaction to partially roll back the transaction.

Syntax

• Create a savepoint:

  SAVEPOINT savepoint_name;
  

• Roll back a transaction to a savepoint:

  ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name;
  

• Release a savepoint:

  RELEASE SAVEPOINT savepoint_name;
  

Parameters

Examples

Assume that you have executed the statements in the following table for a transaction.

Record savepoints

You can create savepoints before committing the transaction. You must create a linked list of savepoints based on the order in which they are created. The preceding transaction contains seven SQL statements and four savepoints. The following figure shows the linked list of savepoints. Each node records the mapping of <savepoint_name, sql_no>.

Participant partition list of a transaction

To enable the rollback of all modifications made after an SQL statement was executed, the system records the involved partitions and the corresponding sql_no values. The preceding transaction has executed seven SQL statements, involving six partitions, which are partitions p1 to p6.

Roll back a transaction to a savepoint

1. Query the sql_no value corresponding to savepoint_name based on the linked list of savepoints.

   Assume that you need to execute ROLLBACK to SAVEPOINT sp2. It can be learned from the linked list of savepoints that the sql_no value corresponding to sp2 is 3.

2. Query the partition corresponding to the sql_no value based on the participant partition list of the transaction.

   It can be learned from the participant partition list of the transaction that statements with a sql_no greater than 3 are executed on partitions p1, p3, p5, and p6.

3. When rolling back partition data, the scheduler initiates rollback requests to the partitions determined in Step 2 to roll back all the modifications made by this transaction on these partitions after sp2.

   Some modifications made by this transaction on p1, p3, and p5 are rolled back, and all modifications made by this transaction on p6 are rolled back.

4. Update the participant partition list of the transaction.

   Delete sql_no values greater than 3 from the participant partition list of the transaction. All modifications on p6 are rolled back. Therefore, p6 can be deleted from the list.

   

5. Release invalid savepoints.

   After you execute ROLLBACK to SAVEPOINT sp2, the system releases savepoints sp3 and sp4. The transaction can no longer be rolled back to sp3 or sp4.