This topic describes how to roll back a transaction.
Rolling back a transaction means undoing all the changes made by the transaction. You can roll back the entire uncommitted transaction or roll back to any savepoint within the transaction.
Roll back an entire transaction
If a transaction is uncommitted, you can use the ROLLBACK statement to roll back the entire transaction.
After rolling back an entire transaction:
All modifications are discarded.
All savepoints are cleared.
All locks held by the transaction are released.
The syntax for rolling back an entire transaction is as follows:
ROLLBACK;
The following example uses ROLLBACK to undo all changes of the current transaction.
obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 10003 | 2022-10-19 14:51:12 |
| 4 | JP | NULL | 2022-10-19 14:51:44 |
| 5 | CN | NULL | 2022-10-19 14:53:56 |
+----+------+-------+---------------------+
5 rows in set
obclient [test]> BEGIN;
Query OK, 0 rows affected
obclient [test]> INSERT INTO ordr(id, name, value) VALUES(6,'JP',10007);
Query OK, 1 row affected
obclient [test]> INSERT INTO ordr(id, name, value) VALUES(8,'FR',10008),(9,'RU',10009);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 1003 | 2022-10-19 14:51:12 |
| 4 | JP | NULL | 2022-10-19 14:51:44 |
| 5 | CN | NULL | 2022-10-19 14:53:56 |
| 6 | JP | 10007 | 2022-10-19 14:58:24 |
| 8 | FR | 10008 | 2022-10-19 14:58:35 |
| 9 | RU | 10009 | 2022-10-19 14:58:35 |
+----+------+-------+---------------------+
8 rows in set
obclient [test]> ROLLBACK;
Query OK, 0 rows affected
obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 1003 | 2022-10-19 14:51:12 |
| 4 | JP | NULL | 2022-10-19 14:51:44 |
| 5 | CN | NULL | 2022-10-19 14:53:56 |
+----+------+-------+---------------------+
5 rows in set
In this example, a transaction is explicitly started with BEGIN. Until the transaction is explicitly committed with COMMIT, all modifications are visible only to the current session and are not persisted. You can use the ROLLBACK statement to undo these modifications.
Roll back a transaction to a specific savepoint
If a transaction has savepoints, you can use the ROLLBACK TO SAVEPOINT statement to roll back to a specific savepoint. For more information about transaction savepoints, see Mark a savepoint.
After rolling back to a specific savepoint:
The transaction does not end.
Modifications made before the savepoint are retained, while modifications made after the savepoint are discarded.
Savepoints created after the specified savepoint (excluding that savepoint) are cleared.
All locks held by the transaction after the savepoint are released.
The syntax for rolling back to a specific savepoint is as follows:
ROLLBACK TO SAVEPOINT pointname;
Here, pointname is the name of the transaction savepoint.
For detailed examples and descriptions on rolling back a transaction to a specific savepoint, see Roll back a transaction to a savepoint.