Transactions and Concurrency
Using savepoints
So far, you've learned that when a statement fails inside a transaction, PostgreSQL marks the transaction as failed.
Normally, we recover by rolling back the entire transaction:
1ROLLBACK;But sometimes a transaction contains several pieces of work, and we don't want to discard everything just because one part fails.
PostgreSQL provides savepoints for this.
A savepoint marks a position inside a transaction that we can return to without rolling back everything that happened before it.
Starting a transaction
Imagine we're creating an order and then adding an order item.
Type the following statement into the SQL editor and run it:
1BEGIN;Create the order:
12345678910111213141516INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)SELECT id, 'pending', 49.99, 'txn_savepoint_001', CURRENT_TIMESTAMPFROM customersORDER BY idLIMIT 1;The order now exists inside the transaction.
We haven't committed it yet.
Creating a savepoint
Before adding the order item, create a savepoint:
1SAVEPOINT before_order_item;before_order_item is simply the name we've given it.
Conceptually:
1234567BEGINcreate orderSAVEPOINT before_order_item ↑ we can return hereAnything we did before the savepoint remains part of the transaction.
What happens if something fails?
Now deliberately try to insert an invalid order item:
12345678910111213INSERT INTO order_items ( order_id, product_id, quantity, unit_price)SELECT id, 1, 1, 'not-a-price'FROM ordersWHERE payment_reference = 'txn_savepoint_001';PostgreSQL rejects the statement.
Without a savepoint, we could recover with:
1ROLLBACK;But that would discard the order we created earlier too.
This time, we have another option.
Rolling back to a savepoint
Type the following statement into the SQL editor and run it:
1ROLLBACK TO SAVEPOINT before_order_item;PostgreSQL returns the transaction to the point where the savepoint was created.
The failed work after the savepoint is discarded, while the work before it remains.
Conceptually:
12345678910111213BEGINcreate order ↓SAVEPOINT before_order_item ↓try to create order item ↓ERROR ↓ROLLBACK TO SAVEPOINT ↓earlier work remainsLet's verify that the order still exists:
123SELECT id, status, payment_referenceFROM ordersWHERE payment_reference = 'txn_savepoint_001';You should still see it.
The source demonstrates the same behavior: work performed after a savepoint can be discarded without throwing away the earlier work in the transaction.
Trying the operation again
Now that we've rolled back to the savepoint, the transaction can continue.
Add a valid order item:
123456789101112131415161718INSERT INTO order_items ( order_id, product_id, quantity, unit_price)SELECT o.id, ( SELECT product_id FROM order_items ORDER BY id LIMIT 1 ), 1, 49.99FROM orders AS oWHERE o.payment_reference = 'txn_savepoint_001';This time, the statement should succeed.
We've recovered from the failed operation without abandoning the entire transaction.
Releasing a savepoint
Once we no longer need the savepoint, we can remove it.
Type the following statement into the SQL editor and run it:
1RELEASE SAVEPOINT before_order_item;We can no longer roll back to that savepoint.
The transaction itself is still open.
Commit it:
1COMMIT;The order and its valid order item are now saved.
Checking the result
Run:
12345678910SELECT o.id AS order_id, o.status, oi.product_id, oi.quantity, oi.unit_priceFROM orders AS oJOIN order_items AS oi ON oi.order_id = o.idWHERE o.payment_reference = 'txn_savepoint_001';You should see the order together with the successfully created order item.
The failed attempt wasn't saved.
ROLLBACK vs ROLLBACK TO SAVEPOINT
ROLLBACK:
1ROLLBACK;ends the transaction and discards all its changes:
12345678910BEGIN ↓work A ↓work B ↓ROLLBACKwork A discardedwork B discardedROLLBACK TO SAVEPOINT:
1ROLLBACK TO SAVEPOINT before_order_item;keeps the transaction open and discards work performed after that savepoint:
12345678910111213BEGIN ↓work A ↓SAVEPOINT ↓work B ↓ROLLBACK TO SAVEPOINTwork A preservedwork B discardedtransaction continuesWhen are savepoints useful?
Savepoints are useful when one transaction contains a piece of work that you may want to undo without abandoning everything that happened before it.
The basic pattern is:
12345678910111213BEGIN;
-- work that should remain
SAVEPOINT my_savepoint;
-- work that might need to be undone
ROLLBACK TO SAVEPOINT my_savepoint;
-- transaction can continue
COMMIT;You won't need savepoints for every transaction.
For many application workflows, BEGIN, COMMIT, and ROLLBACK are enough.
But when you need to recover from part of a transaction while keeping earlier work, savepoints give you that additional control.