Using SAVEPOINT to Handle Partial Rollbacks in Transactions

Imagine you’re writing a book. But hey, things don’t always go as planned. Sometimes you write a whole chapter, but when you read it back, you realize the 5th paragraph is just awful. What do you do? You don’t throw the whole chapter in the trash, right? Instead, you just edit the parts you’re not happy with.

That’s pretty much how SAVEPOINT works in PostgreSQL. It lets you:

1. Create save points inside a transaction—like bookmarks in a book.
2. Jump back to those points to undo just some of your work, without rolling back the whole transaction.
3. Keep working with the rest of your data, without having to start over.

Basic SAVEPOINT Syntax

The commands for using SAVEPOINT are super simple. Here’s the basic set:

Creating a save point (SAVEPOINT):

SAVEPOINT savepoint_name;

This is like saying, “Let’s remember this spot, just in case we need to come back later.”

ROLLBACK TO SAVEPOINT savepoint_name;

If something goes wrong, you jump back to the specified SAVEPOINT and undo everything you did since you created it.

RELEASE SAVEPOINT savepoint_name;

This frees up the spot where your “bookmark” was. After this, you can’t roll back to that point anymore.

Simple Example: Shopping in an Online Store

Let’s say you’re running an online store. A customer adds a few items to their cart, and you want to run a transaction that includes placing the order and updating the inventory table. But if one step fails, you want to undo just part of the transaction, not everything.

BEGIN;

-- Step 1: Reserve the item "SQL Book"
UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;

-- Create a save point
SAVEPOINT book_reserved;

-- Step 2: Reserve the item "PostgreSQL Mug"
UPDATE inventory SET stock = stock - 1 WHERE product_id = 102;

-- Uh-oh, turns out there are no mugs in stock!
ROLLBACK TO SAVEPOINT book_reserved;

-- Commit changes just for the book
COMMIT;

What’s happening in this example?

1. We started a transaction with BEGIN.
2. After reserving the book, we created a save point called book_reserved. That’s our first “checkpoint.”
3. We tried to reserve the mug, but there was an error (like, the item wasn’t in stock).
4. We rolled back to the book_reserved save point to undo just the mug changes.
5. Finally, we committed the changes for the book using COMMIT.

More Complex Example: Multi-Step Data Processing

Now imagine you’re working with an order management system, where you need to update several tables: orders, inventory, and billing. If something fails at any step, you don’t want to lose progress in the other tables. That’s where SAVEPOINT saves the day.

BEGIN;

-- Step 1: Create a new order
INSERT INTO orders (order_id, customer_id, status) VALUES (1, 123, 'pending');
SAVEPOINT after_order_created;

-- Step 2: Update inventory
UPDATE inventory SET stock = stock - 2 WHERE product_id = 101;
SAVEPOINT after_stock_updated;

-- Step 3: Process payment
INSERT INTO billing (order_id, amount, status) VALUES (1, 100, 'paid');

-- Oops, error: credit card declined!
ROLLBACK TO SAVEPOINT after_stock_updated;

-- We’re back to the step after updating inventory, but the order is still "pending".
UPDATE orders SET status = 'failed' WHERE order_id = 1;

COMMIT;

Notice how we used SAVEPOINT to break the transaction into logical steps and jump back to the right spot, keeping some changes intact.

Handy Tips for Working with SAVEPOINT

• Use meaningful save point names. In the examples above, after_order_created is way more helpful than just step1.
• Nested save points work just fine: you can create a SAVEPOINT even inside a rollback to another point.
• Free up resources by deleting save points you don’t need anymore with RELEASE SAVEPOINT. This can boost performance, especially in big transactions.

Real-World Use Cases

Handling Bank Transactions: For example, when transferring money between several accounts, you can roll back to a certain step if one of the transfers fails.

Importing Data from Files: If you’re importing a big CSV file, you can check each row and roll back just the bad data, keeping the good stuff.

Bulk Updating Records: If you’ve got a gnarly SQL script updating thousands of rows, SAVEPOINT lets you roll back to a previous step if something blows up halfway through.

Common Mistakes and Gotchas

Sometimes using SAVEPOINT can lead to weird results if you don’t get how they work. For example:

• If you forget to roll back to a save point or delete it, you might end up with resources locked until the transaction finishes.
• SAVEPOINT can’t undo stuff that happened before the save point was created. For example, data committed with COMMIT is already set in stone and can’t be rolled back.

Now you can safely experiment with transactions, dropping save points wherever you need them. There’s more SQL practice ahead, so get ready for the next round of adventures.

2

Task

SQL SELF, level 39, lesson 2

Locked

Simple rollback of changes using `SAVEPOINT`

Simple rollback of changes using `SAVEPOINT`

Open