In OceanBase Database, savepoints are user-defined execution marks in a transaction. You can define multiple savepoints in a transaction. In this way, the transaction can be rolled back to a specified savepoint when necessary.
For example, if you make mistakes in operations after defining a savepoint, there is no need to roll back the entire transaction and execute it again. Instead, you can run the ROLLBACK TO command to roll back only operations after the savepoint.
In the example described in the following table, the sp1 savepoint is created, so that data inserts after this savepoint can be rolled back.
| Statement | Description |
|---|---|
BEGIN; |
Starts a transaction. |
INSERT INTO a VALUE(1); |
Inserts row 1. |
SAVEPOINT sp1; |
Creates a savepoint named sp1. |
INSERT INTO a VALUE(2); |
Inserts row 2. |
SAVEPOINT sp2; |
Creates a savepoint named sp2. |
ROLLBACK TO sp1; |
Rolls back to sp1. |
INSERT INTO a VALUE(3); |
Inserts row 3. |
COMMIT; |
Commits a transaction. |
Each operation in a transaction corresponds to an SQL sequence in OceanBase Database. The value of the SQL sequence increases during transaction execution (excluding parallel operations). Creating a savepoint is to match the name of the savepoint to the current SQL sequence of the transaction. When you run the ROLLBACK TO command, OceanBase Database performs the following operations internally:
Roll back all the operations with an SQL sequence that is greater than the SQL sequence of the savepoint, and release the corresponding row lock, such as the lock of row 2 in this example.
Delete all savepoints that are created after this savepoint. In this example, the
sp2savepoint is deleted.
You can continue to execute the transaction after running a ROLLBACK TO command.