MySQL - RELEASE SAVEPOINT Statement



RELEASE 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 RELEASE SAVEPOINT statement deletes the specified savepoint. Once you delete a save point you cannot commit or rollback to it. If the specified savepoint does not exist an error will be generated.

If you invoke a COMMIT or ROLLBACK statement without specifying the name of the savepoint all the savepoints of the current transaction are deleted.

Syntax

Following is the syntax of the MySQL RELEASE SAVEPOINT Statement −

RELEASE SAVEPOINT identifier

Example

Assume we have created a table using the CREATE statement as shown below −

CREATE TABLE EMPLOYEE(
   FIRST_NAME VARCHAR(20),
   LAST_NAME VARCHAR(20),
   AGE INT,
   INCOME INT
);

If we have a .csv file, and an .xml file with the following contents

data.xml −

<rowgt;
   <FIRST_NAMEgt;Javed</FIRST_NAMEgt;
   <LAST_NAMEgt;Syed</LAST_NAMEgt;
   <AGEgt;25</AGEgt;
   <INCOMEgt;9855</INCOMEgt;
</rowgt;
<rowgt;
   <FIRST_NAMEgt;Abhinav</FIRST_NAMEgt; 
   <LAST_NAMEgt;Gomatam</LAST_NAMEgt;
   <AGEgt;30</AGEgt;
   <INCOMEgt;7000</INCOMEgt;
</rowgt;

data.csv −

'Krishna','Sharma',19,2000
'Raj','Kandukuri',20,7000

Following MySQL transaction tries to insert contents of these files in to the table −

START TRANSACTION;
SAVEPOINT mysavepoint;
LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.csv" 
into table employee
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n';

load xml infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.xml" 
into table employee ROWS IDENTIFIED BY '<row>';
RELEASE SAVEPOINT mysavepoint;

If you verify the contents of the above table, you can observe all the inserted records as −

SELECT * FROM EMPLOYEE;

Output

Following is the output of the above program −

FIRST_NAME LAST_NAME AGE INCOME
'Krishna' 'Sharma' 19 2000
'Raj' 'Kandukuri' 20 7000
Javed Syed 25 9855
Abhinav Gomatam 30 7000

Since we have released the savepoint if to try to rollback you will get an error −

ROLLBACK TO SAVEPOINT mysavepoint;
ERROR 1305 (42000): SAVEPOINT mysavepoint does not exist
Advertisements