Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
SAVEPOINT Command
2.1.
Syntax for Savepoint command:
3.
Examples:
3.1.
Example
3.1.1.
Using Savepoints in SQL
3.2.
SQL
3.3.
SQL
3.4.
SQL
3.5.
SQL
3.6.
COMMIT
4.
Frequently Asked Questions
4.1.
Can I create multiple savepoints in a single transaction?
4.2.
What happens to a savepoint after rolling back?
4.3.
Is it possible to rollback the entire transaction using a savepoint?
5.
Conclusion
Last Updated: Mar 27, 2024
Easy

Savepoint in SQL

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Ever confused upon a situation where a minor error in a database transaction felt like undoing hours of progress? Savepoints in SQL are the unsung heroes in such scenarios, offering a safety net to rollback to specific points without losing all your work. 

Savepoint in SQL

This guide will walk you through what savepoints are, how to effectively use them, and the syntax that makes it all possible. By the end of this article, you'll have a solid understanding of implementing savepoints in your database transactions, making your data manipulation tasks more efficient & error-proof.

SAVEPOINT Command

A savepoint is essentially a marker within a transaction. It allows you to rollback part of a transaction without aborting the entire thing. This is incredibly useful in large transactions where you want the ability to undo some changes without starting over from scratch.

Syntax for Savepoint command:

The basic syntax for creating a savepoint is:

SAVEPOINT savepoint_name;


Here, savepoint_name is a unique identifier for the savepoint within the transaction.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Examples:

Let's say you're updating records in a database and want to create a savepoint before making each change:

BEGIN TRANSACTION;

-- First update

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp1;


-- Second update

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT sp2;

-- Oops! We need to rollback the second update

ROLLBACK TO SAVEPOINT sp2;

Example

Let's consider a scenario where you're working with a database table named StudentScores, which records students' IDs, names, and their scores in a recent test.

Table: StudentScores

StudentID   Name  Score
1 Alice 88
2 Bob 75
3 Charlie 93
4 Diana 85
Output

Imagine you're tasked with updating scores and need to ensure that all changes are correct before committing them permanently to the database. This is where savepoints come into play.

Using Savepoints in SQL

Start Transaction: Begin a transaction to encapsulate the upcoming operations.

BEGIN TRANSACTION;

Initial Update: Increase Charlie's score by 5 points because of an extra credit project.

  • SQL

SQL

UPDATE StudentScores SET Score = Score + 5 WHERE Name = 'Charlie';

Create Savepoint: After this update, create a savepoint before making further changes.

  • SQL

SQL

SAVEPOINT ScoreUpdate;
Output

Further Updates: Suppose you then decide to increase Diana's score by 2 points but later realize this was a mistake.

  • SQL

SQL

UPDATE StudentScores SET Score = Score + 2 WHERE Name = 'Diana';

Rollback to Savepoint: Since the last update was a mistake, you can rollback to the ScoreUpdate savepoint, undoing Diana's score change but keeping Charlie's update intact.

  • SQL

SQL

ROLLBACK TO SAVEPOINT ScoreUpdate;
Output

Commit Transaction: Once you're satisfied with all the changes (just Charlie's score update in this case), commit the transaction to make the changes permanent.

COMMIT

This example illustrates how savepoints allow for partial rollbacks within a transaction, offering a safety net when performing multiple updates or changes. You can test and adjust your updates without affecting the entire transaction, providing flexibility and reliability in database management.

In this example, if the second update doesn't go as planned, you can rollback to sp2, undoing the second update but keeping the first one intact.

Frequently Asked Questions

Can I create multiple savepoints in a single transaction?

Yes, you can create multiple savepoints within the same transaction to mark different stages.

What happens to a savepoint after rolling back?

Rolling back to a savepoint does not remove the savepoint; it remains valid unless the transaction is ended.

Is it possible to rollback the entire transaction using a savepoint?

No, savepoints are for partial rollbacks. To rollback the entire transaction, use the ROLLBACK command without specifying a savepoint.

Conclusion

Savepoints in SQL offer a flexible way to manage transactions by allowing partial rollbacks. This feature is invaluable in maintaining data integrity during complex database operations. By mastering savepoints, you're equipping yourself with a powerful tool to handle database transactions more effectively, ensuring your data remains consistent, even when unexpected changes occur.

You can refer to our guided paths on the Coding Ninjas. You can check our course to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. 

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.

Live masterclass