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 of its savepoints. 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 ends.
All changes made from the start of the transaction are discarded.
All savepoints are cleared.
All locks held by the transaction are released.
If you roll back a transaction to a savepoint:
The transaction does not end.
Changes made before the savepoint are retained but those made after it are discarded.
All savepoints after the specific savepoint are cleared.
All locks held by the transaction after the specific savepoint are released.
Example: Using the ROLLBACK statement to roll back all changes performed in 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> 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)
