Savepoint in SQL

• Savepoint is a command in SQL that is used with the rollback command.
• It is a command in Transaction Control Language that is used to mark the transaction in a table.
• Consider you are making a very long table, and you want to roll back only to a certain position in a table then; this can be achieved using the savepoint.
• If you made a transaction in a table, you could mark the transaction as a certain name, and later on, if you want to roll back to that point, you can do it easily by using the transaction's name.
• Savepoint is helpful when we want to roll back only a small part of a table and not the whole table. In simple words, we can say savepoint is a bookmark in SQL.

Let us see the practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

To create a table in the database, first, we need to select the database in which we want to create a table.

Then we will write a query to create a table named student in the selected database 'dbs'.

Now, we will write a single query to insert multiple records in the student table:

To verify that multiple records are inserted in the student table, we will execute the SELECT query.

The results show that all ten records are inserted successfully.

To use the TCL commands in SQL, we first need to initiate the transaction by using the BEGIN / START TRANSACTION command.

We will save our initiated transaction using the SAVEPOINT command along with some specific names of this savepoint.

Here, we have saved the initiated transaction with the name of 'ini'.

Then, we decided to insert a new record with an ID of 10 into the existing student table.

We will execute the SELECT query to verify that the new record with ID as ten is inserted successfully.

To save the transaction with this newly inserted record, we will create a new savepoint.

Here, the newly inserted record table is saved with the savepoint named 'ins'.

To update the record in the student table and set the updated name as 'Mahesh Kuwar' for the record whose ID is 1, we will execute the following query:

To verify that the record's name field with ID as 1 is updated successfully, we will again execute the SELECT query.

To save the transaction with this updated record, we will create a new savepoint.

Here, the table with the updated record is saved with the savepoint named 'upd'.

To remove the record from the student table with ID as 6, we will execute the following query:

We will again execute the SELECT query to verify that the record with ID as 6 is removed successfully.

To save the transaction with this removed record, we will create a new savepoint.

Here, the table with the deleted record is saved with the savepoint named 'del'.

Later, we decided that we need the record in the student table, which we have deleted in the previous step.

Since at each and every operation, we have created a savepoint. Using that savepoint, we can jump to any point of the transaction. To do so, we will execute the ROLLBACK command along with the name of the savepoint to which we want to jump.

Since we don't want the record with the ID as 6 to be deleted from the student table, we have rollback to the savepoint named as upd.

To verify that we have achieved the exact table which we had after updating the student table in the earlier steps, we will again execute the SELECT query.

The results above show that we have rollback successfully to the savepoint named 'upd'.