MySQL SAVEPOINT

A SAVEPOINT creates a named checkpoint within a transaction. It allows rolling back to a specific point in the middle of a transaction without discarding all the work done before that point. This gives finer control over partial rollbacks inside a complex transaction.

Why Use SAVEPOINT?

In a long transaction with multiple steps, an error in one step should not always require undoing everything. A SAVEPOINT marks a safe point, so only the steps after that point are rolled back when something goes wrong.

Transaction Control with SAVEPOINT

Syntax

START TRANSACTION;

-- Step 1
SAVEPOINT sp1;

-- Step 2
SAVEPOINT sp2;

-- Roll back to a specific point
ROLLBACK TO SAVEPOINT sp1;

-- Or commit everything
COMMIT;

Sample Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    status VARCHAR(20)
);

INSERT INTO orders VALUES (1, 'Laptop', 5, 'Pending');
INSERT INTO orders VALUES (2, 'Phone', 10, 'Pending');
INSERT INTO orders VALUES (3, 'Tablet', 8, 'Pending');

Example: Using SAVEPOINT for Partial Rollback

START TRANSACTION;

-- Update order 1 — correct
UPDATE orders SET status = 'Processing' WHERE order_id = 1;

SAVEPOINT after_order1;

-- Update order 2 — correct
UPDATE orders SET status = 'Processing' WHERE order_id = 2;

SAVEPOINT after_order2;

-- Update order 3 — made a mistake
UPDATE orders SET status = 'Cancelled' WHERE order_id = 3;

-- Realise the mistake for order 3 only
-- Roll back only to after_order2, preserving orders 1 and 2
ROLLBACK TO SAVEPOINT after_order2;

-- Continue and commit the rest
COMMIT;

Final state:

order_id | product_name | quantity | status
---------+--------------+----------+------------
1        | Laptop       | 5        | Processing
2        | Phone        | 10       | Processing
3        | Tablet       | 8        | Pending

Order 3 is back to "Pending" because the rollback reversed only the last step. Orders 1 and 2 retain their "Processing" status.

Multiple SAVEPOINTs in One Transaction

START TRANSACTION;

INSERT INTO orders VALUES (4, 'Headphones', 20, 'Pending');
SAVEPOINT sp_insert;

UPDATE orders SET quantity = 25 WHERE order_id = 4;
SAVEPOINT sp_update;

DELETE FROM orders WHERE order_id = 3;

-- Only undo the delete
ROLLBACK TO SAVEPOINT sp_update;

COMMIT;

RELEASE SAVEPOINT

RELEASE SAVEPOINT removes a named savepoint. It does not commit or roll back any data — it simply frees the savepoint name.

RELEASE SAVEPOINT sp_insert;

Key Points

• SAVEPOINT creates a named checkpoint inside an active transaction.
• ROLLBACK TO SAVEPOINT name undoes only the changes made after that savepoint.
• Changes before the savepoint are preserved and can still be committed.
• Multiple savepoints can exist within a single transaction.
• RELEASE SAVEPOINT removes the savepoint without affecting data.