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 employee.
obclient> CREATE TABLE employee (id INT, name VARCHAR(10), salary INT);
obclient> INSERT INTO employee VALUES (1, 'Alice', 1000), (2, 'Bob', 2000), (3, 'Alice', 3000);
obclient> SELECT * FROM employee;
+------+-------+--------+
| ID | NAME | SALARY |
+------+-------+--------+
| 1 | Alice | 1000 |
| 2 | Bob | 2000 |
| 3 | Alice | 3000 |
+------+-------+--------+
3 rows in set
Update the
employeetable by changing the value ofnameto 'Carol' for the row whereid = 3, and create a savepoint namedpoint_after_update.obclient> UPDATE employee SET name = 'Carol' WHERE id = 3; obclient> SAVEPOINT point_after_update;Insert one row into the
employeetable and query the table.obclient> INSERT INTO employee VALUES (4, 'David', 4000); obclient> SELECT * FROM employee;Query OK, 1 row affected +------+-------+--------+ | ID | NAME | SALARY | +------+-------+--------+ | 1 | Alice | 1000 | | 2 | Bob | 2000 | | 3 | Carol | 3000 | | 4 | David | 4000 | +------+-------+--------+ 4 rows in setRoll back to
point_after_updateand query the table.obclient> ROLLBACK TO SAVEPOINT point_after_update; obclient> SELECT * FROM employee;Query OK, 0 rows affected +------+-------+--------+ | ID | NAME | SALARY | +------+-------+--------+ | 1 | Alice | 1000 | | 2 | Bob | 2000 | | 3 | Carol | 3000 | +------+-------+--------+ 3 rows in setobclient> COMMIT;