The SAVEPOINT statement can be used to perform a partial rollback of a transaction.
- Create a savepoint:
- Roll back to a savepoint:
ROLLBACK [WORK] to [SAVEPOINT] spname
- Remove a savepoint:
RELEASE SAVEPOINT spname
- 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
ROLLBACKstatement to roll back the entire transaction.
A transaction runs the following statements:
|4||update…||p1, p3, p6|
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
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:
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.
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.
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.
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.
Delete the invalid savepoint
After the user runs the
ROLLBACK to SAVEPOINT sp2statement, the system deletes the savepoints sp3 and sp4. The user is no longer allowed to roll back to sp3 and sp4.