MySQL - SAVEPOINT Statement

A save point is a logical rollback point within a transaction. When you set a save point, whenever an error occurs past a save point, you can undo the events you have done up to the save point using the rollback.

MySQL InnoDB provides support for the statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT.

The SAVEPOINT statement is used to set a save point for the transaction with the specified name. If a save point with the given name already exists the old one will be deleted.

Syntax

Following is the syntax of the MySQL SAVEPOINT statement −

SAVEPOINT identifier

Example

MySQL saves the changes done after the execution of each statement. To save changes automatically, set the autocommit option as shown below −

SET autocommit=0;

Assume we have created a table in MySQL with name EMPLOYEES as shown below −

mysql> CREATE TABLE EMP(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT);
Query OK, 0 rows affected (0.36 sec)

Let us insert 4 records in to it using INSERT statements as −

mysql> INSERT INTO EMP VALUES
   ('Krishna', 'Sharma', 19, 'M', 2000),
   ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 25, 'F', 5000);
Query OK, 3 rows affected (0.49 sec)
Records: 3 Duplicates: 0 Warnings: 0

Following transaction updates, the age values of all the employees in the emp table −

START TRANSACTION;

SELECT * FROM EMP;
UPDATE EMP SET AGE = AGE + 1;

SAVEPOINT samplesavepoint;

INSERT INTO EMP ('Mac', 'Mohan', 26, 'M', 2000);

ROLLBACK TO SAVEPOINT samplesavepoint;
COMMIT;

If you retrieve the contents of the table, you can see the updated values as −

mysql> SELECT * FROM EMP;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Krishna    | Sharma    | 20   | M    | 2000   |
| Raj        | Kandukuri | 21   | M    | 7000   |
| Ramya      | Ramapriya | 26   | F    | 5000   |
+------------+-----------+------+------+--------+
3 rows in set (0.07 sec)

Kickstart Your Career

Get certified by completing the course

Get Started