Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Using SAVEPOINT

Explore how to implement SAVEPOINT to manage transactions in MySQL effectively. Understand creating savepoints, rolling back to them, and using session variables to handle intermediate states. This lesson helps you master transaction control techniques, ensuring atomic updates while allowing partial rollback for data integrity.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
START TRANSACTION;
-- Step 1: Capture baseline values
SELECT Quantity, Stock INTO @orig_qty, @orig_stock
FROM   Order_Details od 
JOIN   Products p ON od.ProductID = p.ProductID
WHERE  od.OrderID = 3 AND od.ProductID = 3;
-- Step 2: Apply the first update to increase quantity and decrease stock, then create a savepoint
UPDATE Order_Details
SET    Quantity = Quantity + 2
WHERE  OrderID = 3 AND ProductID = 3;
UPDATE Products
SET    Stock = Stock - 2
WHERE  ProductID = 3;
SAVEPOINT sp_first_update;
-- Step 3: Perform another update to simulate a second change
UPDATE Order_Details
SET    Quantity = Quantity + 1
WHERE  OrderID = 3 AND ProductID = 3;
UPDATE Products
SET    Stock = Stock - 1
WHERE  ProductID = 3;
-- Step 4: Roll back to the savepoint to undo only the latest change while keeping the first
ROLLBACK TO SAVEPOINT sp_first_update;
-- Step 5: Query the data to display all stages of the transaction
SELECT 'Before Changes' AS Stage, @orig_qty AS OrderLineQuantity, @orig_stock AS ProductStock
UNION ALL
SELECT 'After First Update', Quantity, Stock 
FROM   Order_Details od JOIN Products p ON od.ProductID = p.ProductID
WHERE  od.OrderID = 3 AND od.ProductID = 3
UNION ALL
SELECT 'After ROLLBACK TO SAVEPOINT', @orig_qty, @orig_stock;
-- Step 6: Roll back the transaction to leave the database unchanged
ROLLBACK;

Explanation

The explanation of the solution code is given below:

  • Line 1: Starts a new transaction so that all following operations are grouped into one atomic unit of work that can be committed or rolled back together.

  • Lines 4–7: Select the current Quantity and Stock values for OrderID = 3 and ProductID = 3. The values are stored in session variables @orig_qty and @orig_stock for later comparison.

  • Lines 10–12: Update the Order_Details table by increasing the Quantity by 2 units. This simulates an adjustment to the order quantity.

  • Lines 14–16: Update the Products table by reducing the Stock by 2 units to keep the inventory consistent with the order change.

  • Line 18: Create a savepoint named sp_first_update that marks the state of the database after the first set of updates. This allows rolling back to this specific point ...