ROLLBACK

Purpose

You can use this statement to roll back work done in the current transaction.

Syntax

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

Parameters

Examples

Create a table named 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 tbl1 table by changing name to c for the row where ID is 3, and then roll back the entire 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 named savepoint1.

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