ROLLBACK

Purpose

This statement is used to undo the work completed in the current transaction or manually undo the work completed in an uncertain distributed transaction.

Limitations and considerations

It is recommended to use the COMMIT or ROLLBACK statement to explicitly end transactions in applications. If you do not explicitly commit a transaction and the program terminates abnormally, OceanBase Database will roll back the last uncommitted transaction.

Privilege requirements

You do not need any privileges to execute the ROLLBACK statement. For more information about privileges in OceanBase Database, see Privilege classification in Oracle mode.

Syntax

ROLLBACK [WORK][ TO [ SAVEPOINT ] savepoint_name];

Parameters

Examples

Create the sample table tbl1.

obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES (1, 'a',10),(2, 'b',20),(3, 'a',30);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |   10 |
|    2 | b    |   20 |
|    3 | a    |   30 |
+------+------+------+
3 rows in set

• Update the name of the row with id = 3 in the tbl1 table to c, and then roll back the entire current transaction.

  obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3;
  Query OK, 1 row affected
  Rows matched: 1  Changed: 1  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |   10 |
  |    2 | b    |   20 |
  |    3 | c    |   30 |
  +------+------+------+
  3 rows in set
  
  obclient> ROLLBACK;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |   10 |
  |    2 | b    |   20 |
  |    3 | a    |   30 |
  +------+------+------+
  3 rows in set
  

• Roll back the current transaction to the savepoint savepoint1.

  obclient> ROLLBACK TO SAVEPOINT savepoint1;
  Query OK, 0 rows affected