Transactions and Concurrency
Using savepoints
So far, you've learned that when a statement fails inside an explicit transaction, PostgreSQL marks the transaction as failed.
The transaction can't continue executing ordinary statements. We can recover by rolling back the entire transaction:
1ROLLBACK;But sometimes we want to undo only part of the transaction while keeping the work performed earlier.
PostgreSQL provides savepoints for this.
A savepoint marks a position inside a transaction. We can roll back commands executed after that position without discarding the commands executed before it.
Savepoints can only be created inside a transaction.
Starting a transaction
Imagine we're creating an order and then adding an order item.
Start a transaction:
1BEGIN;Create an order for customer 9 with an initial total of 0.00:
1234567891011121314INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)VALUES ( 9, 'pending', 0.00, 'txn_savepoint_001', CURRENT_TIMESTAMP);You should see INSERT 0 1.
The order now exists inside the transaction, but we haven't committed it.
Creating a savepoint
Before adding the order item, create a savepoint:
1SAVEPOINT before_order_item;You should see SAVEPOINT.
before_order_item is the name we've given the savepoint.
Our transaction now looks like this:
12345BEGIN ↓create order ↓SAVEPOINT before_order_itemIf something goes wrong after this point, we can return to the savepoint without discarding the order.
Making a change after the savepoint
Update the order's total:
123UPDATE ordersSET total_amount = 49.99WHERE payment_reference = 'txn_savepoint_001';You should see UPDATE 1.
This change happened after the savepoint.
Verify the current total:
1234567SELECT id, status, total_amount, payment_referenceFROM ordersWHERE payment_reference = 'txn_savepoint_001';You should see:
1total_amount = 49.99The transaction is still open, so this change hasn't been committed.
Making a statement fail
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 because unit_price is a numeric column and 'not-a-price' isn't a valid numeric value.
The transaction is now in a failed state. PostgreSQL won't execute ordinary statements until we recover using either:
1ROLLBACK;or:
1ROLLBACK TO SAVEPOINT before_order_item;A full ROLLBACK would discard the order created before the savepoint.
We only want to undo the work performed after the savepoint.
Rolling back to the savepoint
Run:
1ROLLBACK TO SAVEPOINT before_order_item;You should see ROLLBACK.
PostgreSQL undoes the commands executed after the savepoint and returns the transaction to a usable state.
Our transaction looked like this:
12345678910111213BEGIN ↓create order with total 0.00 ↓SAVEPOINT before_order_item ↓change total to 49.99 ↓invalid order item ↓ERROR ↓ROLLBACK TO SAVEPOINTThe order was created before the savepoint, so it remains.
The total was changed after the savepoint, so that change has been undone.
Verify the order:
1234567SELECT id, status, total_amount, payment_referenceFROM ordersWHERE payment_reference = 'txn_savepoint_001';You should still see the order, but its total should once again be:
1total_amount = 0.00This demonstrates the two effects of ROLLBACK TO SAVEPOINT:
work performed before the savepoint remains;
work performed after the savepoint is undone.
The transaction is still open and can continue.
Trying the operation again
Now retry the work with valid values.
First, update the order's total:
123UPDATE ordersSET total_amount = 49.99WHERE payment_reference = 'txn_savepoint_001';You should see UPDATE 1.
Now add a valid order item:
12345678910111213INSERT INTO order_items ( order_id, product_id, quantity, unit_price)SELECT id, 1, 1, 49.99FROM ordersWHERE payment_reference = 'txn_savepoint_001';You should see INSERT 0 1.
We've recovered from the failed operation and successfully retried it without abandoning the entire transaction.
Releasing the savepoint
Rolling back to a savepoint doesn't remove that savepoint. We can roll back to it again or release it when we no longer need it.
Run:
1RELEASE SAVEPOINT before_order_item;You should see RELEASE.
RELEASE SAVEPOINT removes the savepoint but keeps the surviving changes.
It doesn't commit the transaction. The transaction remains open.
Releasing the savepoint isn't required before COMMIT. Ending the transaction would also remove it, but running RELEASE SAVEPOINT makes it clear that we no longer need to return to that position.
Now commit the transaction:
1COMMIT;The order and its valid order item are now saved.
Checking the result
Run:
1234567891011SELECT o.id AS order_id, o.status, o.total_amount, 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 its successfully created order item:
12345status = pendingtotal_amount = 49.99product_id = 1quantity = 1unit_price = 49.99The invalid order item wasn't saved.
Comparing the commands
| Command | Ends the transaction? | What happens |
|---|---|---|
ROLLBACK | Yes | All changes made during the transaction are discarded |
ROLLBACK TO SAVEPOINT | No | Changes made after the savepoint are discarded |
RELEASE SAVEPOINT | No | The savepoint is removed, but surviving changes are kept |
When are savepoints useful?
Savepoints are useful when a transaction contains a piece of work that might need to be undone or retried without discarding everything that happened earlier.
The basic pattern is:
123456789101112131415BEGIN;
-- Work that should remain
SAVEPOINT my_savepoint;
-- Work that might fail
ROLLBACK TO SAVEPOINT my_savepoint;
-- Retry or continue
RELEASE SAVEPOINT my_savepoint;
COMMIT;You won't need savepoints for every transaction. For many workflows, BEGIN, COMMIT, and ROLLBACK are enough.
Savepoints also don't mean that the application should commit incomplete data.
In this exercise, the order and its order item are required to form one complete operation. We commit only after the valid order item has been added.
If the retry also failed and the order couldn't exist without an item, we should roll back the entire transaction.
What you need to remember
The core ideas are:
A savepoint marks a position inside a transaction.
ROLLBACK TO SAVEPOINTundoes changes made after the savepoint.Work performed before the savepoint remains.
Rolling back to a savepoint returns a failed transaction to a usable state.
RELEASE SAVEPOINTremoves the savepoint without committing the transaction.Savepoints don't replace a full rollback when the entire operation must be discarded.