The SAVEPOINT statement performs a partial rollback of a transaction.
Create a savepoint:
Roll back to a savepoint:
ROLLBACK [WORK] to [SAVEPOINT] spname
Delete a savepoint:
RELEASE SAVEPOINT 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
statement to roll back the entire transaction.
Assume that the following statements are executed in a transaction.
p1, p3, p6
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.
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:
Savepoint rollback process
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.
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.
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.
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.
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.