Purpose
You can use this statement to create a savepoint before committing a transaction so that you can roll back the transaction partially.
Note
- Each savepoint name is unique in a transaction. A new savepoint will overwrite an existing savepoint with the same name.
- After you create a savepoint, you can continue to handle and commit jobs, roll back the entire transaction, or roll back the transaction to a specified savepoint. For more information about transaction rollback, see ROLLBACK.
Syntax
SAVEPOINT savepoint_name;
Parameters
| Parameter | Description |
|---|---|
| savepoint_name | The name of the savepoint. |
Examples
Create a table named tbl1.
obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (1, 'a',10),(2, 'b',20),(3, 'a',30);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 10 |
| 2 | b | 20 |
| 3 | a | 30 |
+------+------+------+
3 rows in set
Update the
tbl1table by changing the value ofnametocfor the row whereid = 3, and create a savepoint namedsavepoint1.obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 obclient> SAVEPOINT savepoint1; Query OK, 0 rows affectedInsert one row into the
tbl1table and query thetbl1table.obclient> INSERT INTO tbl1 VALUES (4, 'd',40); Query OK, 1 row affected obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 10 | | 2 | b | 20 | | 3 | c | 30 | | 4 | d | 40 | +------+------+------+ 4 rows in setRoll back to
savepoint1and query thetbl1table.obclient> ROLLBACK TO SAVEPOINT savepoint1; Query OK, 0 rows affected obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 10 | | 2 | b | 20 | | 3 | c | 30 | +------+------+------+ 3 rows in set obclient> COMMIT; Query OK, 0 rows affected