Save Point in SQL
In SQL, the classification is done into 4 languages. They are
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Transaction Control Language (TCL)
- 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.