Roll back a transaction

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, note that:

• All modifications made to the transaction 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;

In the following example, ROLLBACK is used to revoke all modifications 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 specific savepoint, note that:

• The transaction does not end.

• Modifications 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.

The syntax for rolling back a transaction to a specific savepoint is as follows:

ROLLBACK TO SAVEPOINT pointname;

Here, 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.

References

• Commit a transaction

• Mark a savepoint