A transaction rollback reverses all changes made in the transaction. You can roll back an entire uncommitted transaction or roll back a transaction to any savepoints of the transaction. To roll back to a specific savepoint, you must use both the ROLLBACK and TO SAVEPOINT statements.
If you roll back an entire transaction:
The transaction will end.
All modifications made from the start of the transaction will be discarded.
All savepoints will be cleared.
All locks held by the transaction will be released.
If you roll back a transaction to a specific savepoint:
The transaction will not end.
Modifications made before the savepoint will be retained but those made after it will be discarded.
All savepoints after the specific savepoint will be cleared.
All locks held by the transaction after the specific savepoint will be released.
Example: Rollback all data modifications of a transaction.
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 |
+----+------+-------+---------------------+
3 rows in set (0.00 sec)
obclient> begin;
Query OK, 0 rows affected (0.00 sec)
obclient> insert into t_insert(id, name, value) values(4,'JP',10004);
Query OK, 1 row affected (0.00 sec)
obclient> insert into t_insert(id, name, value) values(5,'FR',10005),(6,'RU',10006);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
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 | FR | 10005 | 2020-04-02 17:54:53 |
| 6 | RU | 10006 | 2020-04-02 17:54:53 |
+----+------+-------+---------------------+
6 rows in set (0.00 sec)
obclient> rollback;
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 |
+----+------+-------+---------------------+
3 rows in set (0.00 sec)