Using SAVEPOINT & ROLLBACK TO SAVEPOINT0%

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Intermediate12 min read•Updated: 2026-09-12

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

In standard transactional workflows, a ROLLBACK cancels the entire transaction from the very beginning. However, in complex multi-step enterprise business processes, you may want to rollback only a specific sub-operation while retaining earlier successful steps.

MySQL achieves this partial rollback capability through Savepoints.

What is a Savepoint?

A Savepoint is a named marker set inside an active transaction. It acts as a checkpoint to which the transaction can be partially rewound without discarding earlier work.

MySQL does not support true nested transactions; if you issue a second START TRANSACTION, MySQL implicitly commits the first one. Savepoints provide the exact nested rollback behavior developers need without starting a new transaction.

Savepoint Syntax

1-- Set a named savepoint
2SAVEPOINT savepoint_name;
3 
4-- Rollback partial work to the marker (transaction remains open!)
5ROLLBACK TO [SAVEPOINT] savepoint_name;
6 
7-- Delete a savepoint (does NOT rollback or commit)
8RELEASE SAVEPOINT savepoint_name;

Practical Walkthrough: Hotel & Flight Booking Package

Suppose a travel platform processes a vacation package booking:

1
Book Hotel Room (Mandatory)
2
Attempt to Book Flight (Optional addon)
3
If flight booking fails, rollback the flight attempt, but keep the hotel reservation and commit!

1START TRANSACTION;
2 
3-- Step 1: Reserve Hotel Room
4INSERT INTO hotel_bookings (customer_id, hotel_id, room_number, price)
5VALUES (101, 88, 'Suite-4B', 450.00);
6 
7-- Establish a checkpoint!
8SAVEPOINT after_hotel_booking;
9 
10-- Step 2: Attempt flight reservation
11INSERT INTO flight_bookings (customer_id, flight_id, seat_number, price)
12VALUES (101, 999, '12A', 320.00);
13 
14-- Suppose flight seat 12A fails due to an overbooking conflict!
15-- Rewind to checkpoint:
16ROLLBACK TO SAVEPOINT after_hotel_booking;
17 
18-- Step 3: Transaction is still alive!
19-- Update notes on customer account
20INSERT INTO booking_logs (customer_id, note)
21VALUES (101, 'Hotel reserved successfully. Flight unavailable.');
22 
23-- Final step: Commit hotel and log, with flight cleanly excluded!
24COMMIT;

Key Operational Rules for Savepoints

1
Transaction Remains Active: Executing ROLLBACK TO SAVEPOINT name; does NOT end or commit the transaction. Locks remain held and subsequent SQL statements can still be executed before the final COMMIT.
2
Cascading Removal of Downstream Savepoints: If you set Savepoint A, then Savepoint B, and then execute ROLLBACK TO SAVEPOINT A, Savepoint B is destroyed.
3
RELEASE SAVEPOINT vs ROLLBACK TO:

ROLLBACK TO SAVEPOINT sp1 rolls back changes made after sp1.
RELEASE SAVEPOINT sp1 merely frees the savepoint marker from memory without modifying any data.

1
Final Closure: You must still issue an eventual COMMIT or full ROLLBACK to terminate the overall transaction.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the SAVEPOINT statement create?

Question 2

Does executing ROLLBACK TO SAVEPOINT my_pt; end or close the transaction?

Question 3

What does RELEASE SAVEPOINT sp_name do?

Question 4

What happens if you issue START TRANSACTION while already inside an uncommitted transaction in MySQL?

Question 5

If savepoints SP1, SP2, and SP3 are created sequentially, what happens to SP2 and SP3 after executing ROLLBACK TO SAVEPOINT SP1;?

Next Lesson

Transaction Isolation Levels in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Transaction Control: START TRANSACTION, COMMIT, ROLLBACK	Transaction Isolation Levels in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Transaction Isolation Levels in MySQL

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

MySQL achieves this partial rollback capability through Savepoints.

What is a Savepoint?

A Savepoint is a named marker set inside an active transaction. It acts as a checkpoint to which the transaction can be partially rewound without discarding earlier work.

Savepoint Syntax

1-- Set a named savepoint
2SAVEPOINT savepoint_name;
3 
4-- Rollback partial work to the marker (transaction remains open!)
5ROLLBACK TO [SAVEPOINT] savepoint_name;
6 
7-- Delete a savepoint (does NOT rollback or commit)
8RELEASE SAVEPOINT savepoint_name;

Practical Walkthrough: Hotel & Flight Booking Package

Suppose a travel platform processes a vacation package booking:

1
Book Hotel Room (Mandatory)
2
Attempt to Book Flight (Optional addon)
3
If flight booking fails, rollback the flight attempt, but keep the hotel reservation and commit!

1START TRANSACTION;
2 
3-- Step 1: Reserve Hotel Room
4INSERT INTO hotel_bookings (customer_id, hotel_id, room_number, price)
5VALUES (101, 88, 'Suite-4B', 450.00);
6 
7-- Establish a checkpoint!
8SAVEPOINT after_hotel_booking;
9 
10-- Step 2: Attempt flight reservation
11INSERT INTO flight_bookings (customer_id, flight_id, seat_number, price)
12VALUES (101, 999, '12A', 320.00);
13 
14-- Suppose flight seat 12A fails due to an overbooking conflict!
15-- Rewind to checkpoint:
16ROLLBACK TO SAVEPOINT after_hotel_booking;
17 
18-- Step 3: Transaction is still alive!
19-- Update notes on customer account
20INSERT INTO booking_logs (customer_id, note)
21VALUES (101, 'Hotel reserved successfully. Flight unavailable.');
22 
23-- Final step: Commit hotel and log, with flight cleanly excluded!
24COMMIT;

Key Operational Rules for Savepoints

1
Transaction Remains Active: Executing ROLLBACK TO SAVEPOINT name; does NOT end or commit the transaction. Locks remain held and subsequent SQL statements can still be executed before the final COMMIT.
2
Cascading Removal of Downstream Savepoints: If you set Savepoint A, then Savepoint B, and then execute ROLLBACK TO SAVEPOINT A, Savepoint B is destroyed.
3
RELEASE SAVEPOINT vs ROLLBACK TO:

ROLLBACK TO SAVEPOINT sp1 rolls back changes made after sp1.
RELEASE SAVEPOINT sp1 merely frees the savepoint marker from memory without modifying any data.

1
Final Closure: You must still issue an eventual COMMIT or full ROLLBACK to terminate the overall transaction.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the SAVEPOINT statement create?

Question 2

Does executing ROLLBACK TO SAVEPOINT my_pt; end or close the transaction?

Question 3

What does RELEASE SAVEPOINT sp_name do?

Question 4

What happens if you issue START TRANSACTION while already inside an uncommitted transaction in MySQL?

Question 5

If savepoints SP1, SP2, and SP3 are created sequentially, what happens to SP2 and SP3 after executing ROLLBACK TO SAVEPOINT SP1;?

Next Lesson

Transaction Isolation Levels in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Transaction Control: START TRANSACTION, COMMIT, ROLLBACK	Transaction Isolation Levels in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next