This topic describes how to roll back a transaction.
Syntax
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.
If 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;To roll back a transaction to a specific savepoint, use the
ROLLBACK TO SAVEPOINTstatement.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.
Syntax:
ROLLBACK TO SAVEPOINT pointname;
Examples
In the following example, ROLLBACK is used to revoke all changes made by the current transaction. To roll back the transaction to a savepoint, see Example: Roll back a transaction to a savepoint.
obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 01-JAN-70 |
| 2 | US | 10002 | 01-JAN-70 |
| 3 | EN | 10003 | 01-JAN-70 |
+----+------+-------+------------+
3 rows in set
obclient> BEGIN;
Query OK, 0 rows affected
obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004);
Query OK, 1 row affected
obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 01-JAN-70 |
| 2 | US | 10002 | 01-JAN-70 |
| 3 | EN | 10003 | 01-JAN-70 |
| 4 | JP | 10004 | 01-JAN-70 |
| 5 | FR | 10005 | 01-JAN-70 |
| 6 | RU | 10006 | 01-JAN-70 |
+----+------+-------+------------+
6 rows in set
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 01-JAN-70 |
| 2 | US | 10002 | 01-JAN-70 |
| 3 | EN | 10003 | 01-JAN-70 |
+----+------+-------+------------+
3 rows in set
