Rolling back a transaction is to revoke all the changes made in the transaction. Before a transaction is committed, you can roll back the entire transaction or roll back the transaction to any savepoint.
Roll back an entire transaction
Before you commit a transaction, you can use the ROLLBACK statement to roll back the entire transaction.
After you roll back an entire transaction:
All changes made in the transaction are discarded.
All savepoints are cleared.
All locks held by the transaction are released.
Syntax:
ROLLBACK;
In the following example, ROLLBACK is used to revoke all changes made by the current transaction.
obclient> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> BEGIN;
Query OK, 0 rows affected
obclient [SYS]> INSERT INTO ordr(id, name, value) VALUES(4,'JP',10004);
Query OK, 1 row affected
obclient [SYS]> INSERT INTO ordr(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
| 4 | JP | 10004 | 03-NOV-22 |
| 5 | FR | 10005 | 03-NOV-22 |
| 6 | RU | 10006 | 03-NOV-22 |
+----+------+-------+------------+
6 rows in set
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
+----+------+-------+------------+
3 rows in set
In this example, BEGIN is used to start a transaction. Before COMMIT is used to commit the transaction, all modifications are visible only to the current session, and are not persisted. You can use the ROLLBACK statement to roll back the modifications.
Roll back a transaction to a savepoint
If savepoints are set for a transaction, you can use the ROLLBACK TO SAVEPOINT statement to roll back the transaction to a savepoint. For more information about transaction savepoints, see Mark a savepoint.
After 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 savepoint are released.
To roll back a transaction to a specific savepoint, use the following syntax:
ROLLBACK TO SAVEPOINT pointname;
pointname specifies the name of the transaction savepoint.
For more information about how to roll back a transaction to a savepoint, see Roll back a transaction to a savepoint.