Save Point in SQL

In SQL, the classification is done into 4 languages. They are

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Transaction Control Language (TCL)
4. Data Control Language (DCL)

Save Point falls under the Transaction Control Language. Transaction is a unit of work in the database, which is formed after execution of many commandsconsecutively. So, Transaction Control Language handles/manages all the transactions in a database.

Under Transaction control Language we also have roll back and commit along with the save point command.

Commit:

Once we write all the data into the tables and perform all the necessary operations on the table, we should commit the work for long lasting storage.

When we perform any Data Manipulation Language commands like insert, delete, update on a table in the database, those manipulations will stay alive till the current session of the database terminates.

Once the current session terminates, all the manipulations on the table will be washed off permanently. At this time, COMMIT command helps us to save all the manipulation progress on the tables permanently.

Syntax for commit:

COMMIT;

Example:

Consider a table of Student data having Name, ID, Age as fields.

ID	Name	Age
511	Daksh	18
512	Ravi	17
513	Priya	19

Now let’s try to insert records into the table.

insert into Student values (514, ‘Yash’, 18);
insert into Student values (515, ‘Keerthi’, 19);
select * from Student;

ID	Name	Age
511	Daksh	18
512	Ravi	17
513	Priya	19
514	Yash	18
515	Keerthi	19

So, the table is updated with two new rows. Now, we should commit the changes in the table.

COMMIT;

So, the two rows are added to the table permanently.

Now, let’s update the age of Yash from 18 to 19.

Update Student set Age = 19 where ID = 514;
select * from Student;

ID	Name	Age
511	Daksh	18
512	Ravi	17
513	Priya	19
514	Yash	19
515	Keerthi	19

Again, after the update command operations on the table, we should again commit the changes for Permanent Storage.

Save Point:

We perform many operations on a particular table. All these operations are classified into two parts. Consider a part of the transactions having all delete statements and another part of the transaction having all update statements. For saving the insert queries into the insert part and delete queries into the delete part, we prefer to use Save point command in SQL.

Syntax:

Let’s suppose that we want to save all the insert related queries into an insert save point. For implementing that we will perform the following query,

SAVEPOINT savepoint name;

Example:

Consider a customer table having Name, ID, and Age as the fields of the table.

ID	Name	Age
2551	Ravi	23
2552	Virat	24
2553	Rinku	26
2554	Sharma	18
2555	Shravya	20

Now let’s delete a row having 2552 as the customer ID.

delete from Customer where ID = 2552;
select * from Customer;

ID	Name	Age
2551	Ravi	23
2553	Rinku	26
2554	Sharma	18
2555	Shravya	20

Now we will create a savepoint.

SAVEPOINT sp1;

This will create a savepoint after deletion of a row.

 The above syntax serves for the creation of the save point only. So, to undo some of the previous group of transactions, here we use rollback command.

ROLLBACK TO savepoint name;

Example:

In the above table, we are left with 4 rows after deleting a row and creating the first savepoint.

Consider the above table.

CUSTOMER TABLE

ID	Name	Age
2551	Ravi	23
2553	Rinku	26
2554	Sharma	18
2555	Shravya	20

 Now, try to delete another row from the Customer table and create another savepoint.

delete from Customer where ID = 2555;

Now the table looks like,

select * from Customer;

CUSTOMER TABLE

ID	Name	Age
2551	Ravi	23
2553	Rinku	26
2554	Sharma	18

Now, we should create another savepoint.

SAVEPOINT sp2;

Delete the third row now.

delete from Customer where ID = 2554;

Now the table has two rows left.

CUSTOMER TABLE

ID	Name	Age
2551	Ravi	23
2553	Rinku	26

So, now let’s bring back the rows with the help of the savepoints which we had created for the Customer table.

Execute the following command.

ROLLBACK TO sp2;

Rollback Complete.

Now if we try to print the table, we will be directed to sp2.

CUSTOMER TABLE

ID	Name	Age
2551	Ravi	23
2553	Rinku	26
2554	Sharma	18

As we can observe we got the last deleted row back. So, for undo operations performed on tables, SAVEPOINT works efficiently.

Now, execute the rollback to sp1 command, and observe the changes.

ROLLBACK TO sp1;

This will undo the operations performed on the table up to Save point 1 (sp1).

So, now we have the table as,

select * from Customer;

CUSTOMER TABLE

ID	Name	Age
2551	Ravi	23
2553	Rinku	26
2554	Sharma	18
2555	Shravya	20

So, we can observe the Rollback operation is done successfully.

Let’s now consider another example for the above table only.

CUSTOMER TABLE

ID	Name	Age
2551	Ravi	23
2552	Virat	24
2553	Rinku	26
2554	Sharma	18
2555	Priya	20

We will now insert a row to the above table.

insert into Customer values (2556, ‘Shashi’, 24);
select * from Customer;

ID	Name	Age
2551	Ravi	23
2552	Virat	24
2553	Rinku	26
2554	Sharma	18
2555	Priya	20
2556	Shashi	24

So, as we inserted a row, we will now create a Save point over here with the name Insertion.

SAVEPOINT Insertion;

For Updating the row having ID = 2555, we give the following query.

Update Customer set Age = 21 where ID = 2555;

ID	Name	Age
2551	Ravi	23
2552	Virat	24
2553	Rinku	26
2554	Sharma	18
2555	Priya	21
2556	Shashi	24

So, we can now create an Update savepoint.

SAVEPOINT updation;

So, again if we do not need any updation we can rollback to the Insertion Save Point.

ROLLBACK TO Insertion;

We can get the table which is having changes up to the Insertion Save Point.