All Products
Search
Document Center

SAVEPOINT

Last Updated: Jan 04, 2020

The SAVEPOINT statement can be used to perform a partial rollback of a transaction.

Syntax

  • Create a savepoint:
  1. SAVEPOINT spname
  • Roll back to a savepoint:
  1. ROLLBACK [WORK] to [SAVEPOINT] spname
  • Remove a savepoint:
  1. RELEASE SAVEPOINT spname

Parameters

  • spname: The name of the savepoint. Savepoints are unique within the scope of a transaction. If the specified name of a savepoint already exists, the new savepoint overwrites the existing savepoint. After a savepoint is created, the transaction can be rolled back to the savepoint. You can also use the ROLLBACK statement to roll back the entire transaction.

Example

A transaction runs the following statements:

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 a savepoint

You can create savepoints before you submit transactions. You must connect the transaction savepoints into a linked list based on 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 mapping.
image.png

List of transaction participants

To roll back all changes made after an SQL statement in a transaction, the corresponding participants and sql_no are recorded. The preceding transaction contains 7 SQL statements and 6 partitions from p1 to p6:
image.png

Roll back to a savepoint

  1. Find the corresponding sql_no of the spname according to the savepoint linked list

    For example, the user performs ROLLBACK to SAVEPOINT sp2. Based on the savepoint linked list, the sql_no corresponding to sp2 is 3.

  2. Find the partition corresponding to the sql_no in the transaction participant list

    Based on the transaction participant list, the partitions with sql_no greater than 3 include p1, p3, p5, and p6.

  3. Roll back the data of the partitions

    The scheduler initiates a rollback request to these partitions obtained in step 2. All changes made by the transaction after sp2 are rolled back on these partitions. Some changes made by this transaction on p1, p3, and p5 are rolled back, and all changes on p6 are rolled back.

  4. Update the transaction participant list

    ApsaraDB for OceanBase modifies the transaction participant list and deletes the operation information with sql_no greater than 3 from the transaction participant list. p6 is deleted from the participant list because all changes on p6 are rolled back.

    image.png

  5. Delete the invalid savepoint

    After the user runs the ROLLBACK to SAVEPOINT sp2 statement, the system deletes the savepoints sp3 and sp4. The user is no longer allowed to roll back to sp3 and sp4.

    image.png