SAVEPOINT

Purpose

You can use this statement to create a savepoint before committing a transaction so that you can roll back the transaction partially.

Syntax

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

1. Update the tbl1 table by changing the value of name to c for the row where id = 3, and create a savepoint named savepoint1.

   obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3;
   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   
   obclient> SAVEPOINT savepoint1;
   Query OK, 0 rows affected
   

2. Insert one row into the tbl1 table and query the tbl1 table.

   obclient> INSERT INTO tbl1 VALUES (4, 'd',40);
   Query OK, 1 row affected
   
   obclient> SELECT * FROM tbl1;
   +------+------+------+
   | ID   | NAME | NUM  |
   +------+------+------+
   |    1 | a    |   10 |
   |    2 | b    |   20 |
   |    3 | c    |   30 |
   |    4 | d    |   40 |
   +------+------+------+
   4 rows in set
   

3. Roll back to savepoint1 and query the tbl1 table.

   obclient> ROLLBACK TO SAVEPOINT savepoint1;
   Query OK, 0 rows affected
   
   obclient> SELECT * FROM tbl1;
   +------+------+------+
   | ID   | NAME | NUM  |
   +------+------+------+
   |    1 | a    |   10 |
   |    2 | b    |   20 |
   |    3 | c    |   30 |
   +------+------+------+
   3 rows in set
   
   obclient> COMMIT;
   Query OK, 0 rows affected