All Products
Search
Document Center

SAVEPOINT

Last Updated: Jun 18, 2021

Description

The SAVEPOINT statement performs a partial rollback of a transaction.

Syntax

  1. Create a savepoint:

SAVEPOINT spname
  1. Roll back to a savepoint:

ROLLBACK [WORK] to [SAVEPOINT] spname
  1. Delete a savepoint:

RELEASE SAVEPOINT spname

Parameter description

Parameter

Description

spname

Specifies the name of the savepoint. Savepoints are unique in the transaction scope. A savepoint overwrites the preceding savepoint that has the same name as the save point. After you create a savepoint, you can roll back the transaction to the specified savepoint. You can also use the

ROLLBACK

statement to roll back the entire transaction.

Examples

Assume that the following statements are executed in a transaction.

sql_no

Statement

Partition

1

update...

p1, p4

savepoint sp1

2

update...

p2, p4

3

update...

p3, p5

savepoint sp2

4

update...

p1, p3, p6

5

update...

p1, p5

savepoint sp3

6

select...

7

update...

p5, p6

savepoint sp4

Record savepoints

You can create savepoints before you submit transactions. You must connect the transaction savepoints to a linked list by using the order in which they are created. The preceding transaction contains seven SQL statements and four savepoints. The linked list of savepoints is shown in the following figure where each node records a <spname, sql_no> mapping.image.png

Transaction participant list

To roll back all the modifications that are made after an SQL statement in a transaction, the participants and sql_no that each statement involves are recorded. The preceding transaction contains seven SQL statements and six partitions that range from p1 to p6:image.png

Savepoint rollback process

  1. Query the sql_no that corresponds to spname by using the savepoint linked list

For example, the user executes the ROLLBACK to SAVEPOINT sp2 statement. Based on the savepoint linked list, the queries sql_no that corresponds to sp2 is 3.

  1. Query the partition that corresponds to sql_no by using the transaction participant list

Based on the transaction participant list, the partitions whose sql_no is larger than 3 involve p1, p3, p5, and p6.

  1. Roll back the data of the partitions

The scheduler initiates a rollback request to these partitions that are queried in Step 2. This request rolls back all modifications that are made by the transaction after sp2 on these partitions. Some modifications that are related to this transaction on p1, p3, and p5 are rolled back, and all the modifications that are related to this transaction on p6 are rolled back.

  1. Update the information about the transaction participant list

Modify the transaction participant list and delete the operation information in which sql_no is larger than 3 from the transaction participant list. p6 can be deleted from the participant list because all the modifications on p6 are rolled back.image.png

  1. Delete invalid savepoints

After the user executes the ROLLBACK to SAVEPOINT sp2 statement, the system deletes the savepoints sp3 and sp4. Then, the transaction cannot be rolled back to sp3 and sp4.image.png