The SAVEPOINT statement marks a savepoint during a transaction, to which you can later roll back. Savepoints are optional. A transaction can have multiple savepoints.
Example: Rolling back a transaction to a savepoint
The following example rolls back a transaction that contains multiple DML statements and multiple savepoints to one of its savepoints. In this process, changes made after this savepoint are discarded.
- View current records in a table
obclient> select * from t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
| 4 | JP | NULL | 2020-04-02 17:53:34 |
| 5 | DE | NULL | 2020-04-02 17:58:31 |
+----+------+-------+---------------------+
5 rows in set (0.00 sec)
- Start a transaction and set several savepoints.
obclient> insert into t_insert(id, name) values(6,'FR');
Query OK, 1 row affected (0.00 sec)
obclient> savepoint fr;
Query OK, 0 rows affected (0.00 sec)
obclient> insert into t_insert(id, name) values(7,'RU');
Query OK, 1 row affected (0.00 sec)
obclient> savepoint ru;
Query OK, 0 rows affected (0.00 sec)
obclient> insert into t_insert(id, name) values(8,'CA');
Query OK, 1 row affected (0.00 sec)
obclient> savepoint ca;
Query OK, 0 rows affected (0.00 sec)
- All uncommitted changes in the transaction are visible to the current session.
obclient> select * from t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
| 4 | JP | NULL | 2020-04-02 17:53:34 |
| 5 | DE | NULL | 2020-04-02 17:58:31 |
| 6 | FR | NULL | 2020-04-02 17:58:59 |
| 7 | RU | NULL | 2020-04-02 17:59:09 |
| 8 | CA | NULL | 2020-04-02 17:59:19 |
+----+------+-------+---------------------+
8 rows in set (0.00 sec)
- Roll back the transaction to one of its savepoints.
obclient> rollback to savepoint ru;
Query OK, 0 rows affected (0.00 sec)
obclient> select * from t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
| 4 | JP | NULL | 2020-04-02 17:53:34 |
| 5 | DE | NULL | 2020-04-02 17:58:31 |
| 6 | FR | NULL | 2020-04-02 17:58:59 |
| 7 | RU | NULL | 2020-04-02 17:59:09 |
+----+------+-------+---------------------+
7 rows in set (0.00 sec)
- Commit the transaction and make sure the latest changes include those made before the savepoint.
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient> select * from t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
| 4 | JP | NULL | 2020-04-02 17:53:34 |
| 5 | DE | NULL | 2020-04-02 17:58:31 |
| 6 | FR | NULL | 2020-04-02 17:58:59 |
| 7 | RU | NULL | 2020-04-02 17:59:09 |
+----+------+-------+---------------------+
7 rows in set (0.00 sec)
obclient>