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-03 16:05:45 |
| 2 | US | 10002 | 2020-04-03 16:05:54 |
| 3 | UK | 10003 | 2020-04-03 16:05:54 |
+----+------+-------+---------------------+
3 rows in set (0.00 sec)
- Start a transaction and set several savepoints.
obclient> set session autocommit=off;
Query OK, 0 rows affected (0.00 sec)
obclient> begin;
Query OK, 0 rows affected (0.00 sec)
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-03 16:05:45 |
| 2 | US | 10002 | 2020-04-03 16:05:54 |
| 3 | UK | 10003 | 2020-04-03 16:05:54 |
| 6 | FR | NULL | 2020-04-03 16:26:22 |
| 7 | RU | NULL | 2020-04-03 16:26:32 |
| 8 | CA | NULL | 2020-04-03 16:26:42 |
+----+------+-------+---------------------+
6 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-03 16:05:45 |
| 2 | US | 10002 | 2020-04-03 16:05:54 |
| 3 | UK | 10003 | 2020-04-03 16:05:54 |
| 6 | FR | NULL | 2020-04-03 16:26:22 |
| 7 | RU | NULL | 2020-04-03 16:26:32 |
+----+------+-------+---------------------+
5 rows in set (0.01 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-03 16:05:45 |
| 2 | US | 10002 | 2020-04-03 16:05:54 |
| 3 | UK | 10003 | 2020-04-03 16:05:54 |
| 6 | FR | NULL | 2020-04-03 16:26:22 |
| 7 | RU | NULL | 2020-04-03 16:26:32 |
+----+------+-------+---------------------+
5 rows in set (0.00 sec)
obclient>