MySQL - SAVEPOINT Statement

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 −

CREATE TABLE EMP(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT);

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

INSERT INTO EMP VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000);

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 −

SELECT * FROM EMP;

Output

The above query produces the following output −