SAVEPOINT

What is SAVEPOINT?

SAVEPOINT creates a checkpoint inside your transaction. If something goes wrong later, you can go back to that checkpoint without cancelling the entire transaction.

Think of SAVEPOINT like save points in a video game. If your character dies, you restart from the last save point, not from the very beginning of the game.

How SAVEPOINT Works

With SAVEPOINT, you can undo Change 3 but keep Changes 1 and 2.

Basic Syntax

BEGIN;

-- Do some work
SAVEPOINT checkpoint_name;

-- Do more work
ROLLBACK TO checkpoint_name;  -- Undo back to checkpoint

COMMIT;

Why Use SAVEPOINT?

Without SAVEPOINT, ROLLBACK cancels everything in the transaction. Sometimes you want to undo only part of your work.

Without SAVEPOINT:

Change 1, Change 2, Change 3
Problem with Change 3
ROLLBACK cancels ALL three changes

With SAVEPOINT:

Change 1, Change 2, SAVEPOINT, Change 3
Problem with Change 3
ROLLBACK TO savepoint keeps Change 1 and Change 2

Real-life Example: Processing Orders

Imagine you are processing multiple orders in one transaction. Order 3 has a problem, but orders 1 and 2 are fine.

BEGIN;

-- Process order 1
INSERT INTO orders (customer_id, total) VALUES (1, 100);
UPDATE inventory SET stock = stock - 2 WHERE product_id = 10;
SAVEPOINT after_order_1;

-- Process order 2
INSERT INTO orders (customer_id, total) VALUES (2, 150);
UPDATE inventory SET stock = stock - 3 WHERE product_id = 20;
SAVEPOINT after_order_2;

-- Process order 3
INSERT INTO orders (customer_id, total) VALUES (3, 200);
UPDATE inventory SET stock = stock - 5 WHERE product_id = 30;
-- ERROR: Not enough stock for product 30

-- Undo only order 3, keep orders 1 and 2
ROLLBACK TO after_order_2;

-- Continue with order 4
INSERT INTO orders (customer_id, total) VALUES (4, 75);

COMMIT;  -- Orders 1, 2, and 4 are saved

Real-life Example: Multi-step Form

A user fills out a multi-step registration form. If they make an error on step 3, you do not want to lose steps 1 and 2.

BEGIN;

-- Step 1: Create user account
INSERT INTO users (email, password) VALUES ('john@email.com', 'hashed_pw');
SAVEPOINT user_created;

-- Step 2: Add profile info
INSERT INTO profiles (user_id, name, bio) VALUES (1, 'John', 'Hello');
SAVEPOINT profile_created;

-- Step 3: Add preferences
INSERT INTO preferences (user_id, theme, language) VALUES (1, 'dark', 'XX');
-- ERROR: 'XX' is not a valid language code

-- Undo step 3, keep steps 1 and 2
ROLLBACK TO profile_created;

-- Try step 3 again with correct data
INSERT INTO preferences (user_id, theme, language) VALUES (1, 'dark', 'en');

COMMIT;

Multiple Savepoints

You can create many savepoints in one transaction. Each savepoint acts as a checkpoint you can return to.

BEGIN;

UPDATE products SET price = 100 WHERE id = 1;
SAVEPOINT price_done;

UPDATE products SET stock = 50 WHERE id = 1;
SAVEPOINT stock_done;

UPDATE products SET discount = 20 WHERE id = 1;
SAVEPOINT discount_done;

UPDATE products SET name = 'New Name' WHERE id = 1;
-- Decided name change was wrong

ROLLBACK TO discount_done;  -- Undo name change only

COMMIT;  -- Price, stock, and discount are saved

SAVEPOINT Rules

SAVEPOINT names must be unique within a transaction
You can only ROLLBACK TO a savepoint that exists
ROLLBACK TO removes all savepoints created after that point
COMMIT saves everything and removes all savepoints

When to Use SAVEPOINT

Use SAVEPOINT when:

Processing batches of records
Complex multi-step operations
You might need to undo part of your work
Testing changes before finalizing

Do not use SAVEPOINT when:

Simple single-step transactions
You want all-or-nothing behavior

Summary

SAVEPOINT lets you create checkpoints in a transaction:

Undo to a specific point without cancelling everything
Keep successful changes while undoing failed ones
Process multiple items with partial rollback capability
Essential for complex, multi-step operations

Transaction commands work together:

BEGIN starts the transaction
SAVEPOINT creates a checkpoint
ROLLBACK TO returns to a checkpoint
ROLLBACK cancels everything
COMMIT saves everything