SAVEPOINT

Purpose

This statement is used to create a savepoint before committing a transaction, allowing for partial rollback of the transaction.

Syntax

SAVEPOINT savepoint_name

Parameters

Examples

Create the sample table employee.

obclient> CREATE TABLE employee (id INT, name VARCHAR(10), salary INT);

obclient> INSERT INTO employee VALUES (1, 'Alice', 1000), (2, 'Bob', 2000), (3, 'Alice', 3000);

obclient> SELECT * FROM employee;

+------+-------+--------+
| ID   | NAME  | SALARY |
+------+-------+--------+
|    1 | Alice |   1000 |
|    2 | Bob   |   2000 |
|    3 | Alice |   3000 |
+------+-------+--------+
3 rows in set

1. Update the employee table, changing the name of the record with id = 3 to 'Carol', and create a savepoint named point_after_update.

   obclient> UPDATE employee SET name = 'Carol' WHERE id = 3;
   obclient> SAVEPOINT point_after_update;
   

2. Insert a new row into the employee table and query the table.

   obclient> INSERT INTO employee VALUES (4, 'David', 4000);
   obclient> SELECT * FROM employee;
   

   Query OK, 1 row affected
   
   +------+-------+--------+
   | ID   | NAME  | SALARY |
   +------+-------+--------+
   |    1 | Alice |   1000 |
   |    2 | Bob   |   2000 |
   |    3 | Carol |   3000 |
   |    4 | David |   4000 |
   +------+-------+--------+
   4 rows in set
   

3. Rollback the transaction to the point_after_update savepoint and query the table.

   obclient> ROLLBACK TO SAVEPOINT point_after_update;
   obclient> SELECT * FROM employee;
   

   Query OK, 0 rows affected
   
   +------+-------+--------+
   | ID   | NAME  | SALARY |
   +------+-------+--------+
   |    1 | Alice |   1000 |
   |    2 | Bob   |   2000 |
   |    3 | Carol |   3000 |
   +------+-------+--------+
   3 rows in set
   

   obclient> COMMIT;