All Products
Search
Document Center

SAVEPOINT

Last Updated: Jan 10, 2020

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

Syntax

  1. Create a savepoint.

    1. SAVEPOINT spname
  2. Roll back to a savepoint.

    1. ROLLBACK [WORK] to [SAVEPOINT] spname

Parameters

spname is the name of 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.

1

List of transaction participants

To roll back all changes made after you execute a certain SQL statement in a transaction, the corresponding participants and sql_no are recorded. The preceding transaction executes 7 SQL statements which involve 6 partitions (p1~p6).

2

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.

    3

  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.

    4