Roll back a transaction |V2.2.77|OceanBase Database| docs|Distributed Database

Roll back a transaction

Last Updated：2023-08-18 09:26:34 Updated

Description

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.

Roll back an entire transaction

Examples:

Roll back an entire transaction in MySQL mode:

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> BEGIN;
Query OK, 0 rows affected (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)

Roll back an entire transaction in Oracle mode:

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)

Roll back a transaction to a savepoint

The transaction in the following example contains several data manipulation language (DML) statements and several savepoints. After the transaction is rolled back to a specific savepoint, only the modifications after the savepoint are discarded.

MySQL mode

View the records in the table:

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2020-04-03 16:05:45 |
|  2 | US   | 10002 | 2020-04-03 16:05:54 |
|  3 | UK   | 10003 | 2020-04-03 16:05:54 |
+----+------+-------+---------------------+
3 rows in set (0.00 sec)

Start a transaction and set several savepoints:

obclient> SET SESSION autocommit=off;
Query OK, 0 rows affected (0.00 sec)

obclient> BEGIN;
Query OK, 0 rows affected (0.00 sec)

obclient> INSERT INTO t_insert(id, name) VALUES(6,'FR');
Query OK, 1 row affected (0.00 sec)

obclient> SAVEPOINT fr;
Query OK, 0 rows affected (0.00 sec)

obclient> INSERT INTO t_insert(id, name) VALUES(7,'RU');
Query OK, 1 row affected (0.00 sec)

obclient> SAVEPOINT ru;
Query OK, 0 rows affected (0.00 sec)

obclient> INSERT INTO t_insert(id, name) VALUES(8,'CA');
Query OK, 1 row affected (0.00 sec)

obclient> SAVEPOINT ca;
Query OK, 0 rows affected (0.00 sec)

View all the uncommitted modifications in the current transaction of the current session:

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2020-04-03 16:05:45 |
|  2 | US   | 10002 | 2020-04-03 16:05:54 |
|  3 | UK   | 10003 | 2020-04-03 16:05:54 |
|  6 | FR   |  NULL | 2020-04-03 16:26:22 |
|  7 | RU   |  NULL | 2020-04-03 16:26:32 |
|  8 | CA   |  NULL | 2020-04-03 16:26:42 |
+----+------+-------+---------------------+
6 rows in set (0.00 sec)

Roll back the transaction to one of its savepoints:

obclient> ROLLBACK TO SAVEPOINT ru;
Query OK, 0 rows affected (0.00 sec)

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2020-04-03 16:05:45 |
|  2 | US   | 10002 | 2020-04-03 16:05:54 |
|  3 | UK   | 10003 | 2020-04-03 16:05:54 |
|  6 | FR   |  NULL | 2020-04-03 16:26:22 |
|  7 | RU   |  NULL | 2020-04-03 16:26:32 |
+----+------+-------+---------------------+
5 rows in set (0.01 sec)

Commit the transaction and make sure the latest changes include those made before the savepoint:

obclient> COMMIT;
Query OK, 0 rows affected (0.00 sec)

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2020-04-03 16:05:45 |
|  2 | US   | 10002 | 2020-04-03 16:05:54 |
|  3 | UK   | 10003 | 2020-04-03 16:05:54 |
|  6 | FR   |  NULL | 2020-04-03 16:26:22 |
|  7 | RU   |  NULL | 2020-04-03 16:26:32 |
+----+------+-------+---------------------+
5 rows in set (0.00 sec)

Oracle mode

View the records in the table:

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 | DE   |  NULL | 2020-04-02 17:58:31 |
+----+------+-------+---------------------+
5 rows in set (0.00 sec)

Start a transaction and set several savepoints:

obclient> INSERT INTO t_insert(id, name) VALUES(6,'FR');
Query OK, 1 row affected (0.00 sec)

obclient> SAVEPOINT fr;
Query OK, 0 rows affected (0.00 sec)

obclient> INSERT INTO t_insert(id, name) VALUES(7,'RU');
Query OK, 1 row affected (0.00 sec)

obclient> SAVEPOINT ru;
Query OK, 0 rows affected (0.00 sec)

obclient> INSERT INTO t_insert(id, name) VALUES(8,'CA');
Query OK, 1 row affected (0.00 sec)

obclient> SAVEPOINT ca;
Query OK, 0 rows affected (0.00 sec)

View all the uncommitted modifications in the current transaction in the current session:

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 | DE   |  NULL | 2020-04-02 17:58:31 |
|  6 | FR   |  NULL | 2020-04-02 17:58:59 |
|  7 | RU   |  NULL | 2020-04-02 17:59:09 |
|  8 | CA   |  NULL | 2020-04-02 17:59:19 |
+----+------+-------+---------------------+
8 rows in set (0.00 sec)

Roll back the transaction to one of its savepoints:

obclient> ROLLBACK TO SAVEPOINT ru;
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 |
|  4 | JP   |  NULL | 2020-04-02 17:53:34 |
|  5 | DE   |  NULL | 2020-04-02 17:58:31 |
|  6 | FR   |  NULL | 2020-04-02 17:58:59 |
|  7 | RU   |  NULL | 2020-04-02 17:59:09 |
+----+------+-------+---------------------+
7 rows in set (0.00 sec)

Commit the transaction and make sure the latest changes include those made before the savepoint:

obclient> COMMIT;
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 |
|  4 | JP   |  NULL | 2020-04-02 17:53:34 |
|  5 | DE   |  NULL | 2020-04-02 17:58:31 |
|  6 | FR   |  NULL | 2020-04-02 17:58:59 |
|  7 | RU   |  NULL | 2020-04-02 17:59:09 |
+----+------+-------+---------------------+
7 rows in set (0.00 sec)