SQL / TRANSACTIONS AND CONCURRENCY
Savepoints and rolling back part of a transaction
Use savepoints to undo only part of an open transaction and recover from a failed statement without throwing away the work that already succeeded.
What you will learn
- Undo part of a transaction with ROLLBACK TO SAVEPOINT, keeping earlier work
- Recover from a failed statement inside a transaction instead of losing all of it
- Tell RELEASE SAVEPOINT (drop the marker) from ROLLBACK TO (undo the work)
- Nest savepoints and know that rolling back to an outer one discards inner ones
Understanding Savepoints and rolling back part of a transaction
A savepoint is a named marker placed inside an already open transaction. SAVEPOINT after_debit records the current state, and ROLLBACK TO SAVEPOINT after_debit later undoes every change made after that marker while leaving the transaction open, so the statements before it stay exactly as they were. The mental model that keeps this straight is a stack of marks on the transaction's undo trail rather than a set of independent transactions: nothing a savepoint touches becomes visible to other sessions, and nothing becomes durable, until the outer COMMIT.
Savepoints matter more in practice than the phrase "partial undo" suggests, because of error handling. In PostgreSQL a single failed statement poisons the whole transaction, whether it is a duplicate key, a cast that does not parse, or a violated check constraint, and every later command answers "current transaction is aborted" until the transaction ends. A savepoint set before the risky statement is the only way to absorb that failure and carry on, which is precisely why drivers and ORMs implement their "nested transactions" by emitting SAVEPOINT under the hood.
Three rules cover the remaining behaviour. RELEASE SAVEPOINT s forgets the marker but keeps the work done after it, so it is a "that part went fine" signal and not a commit. Rolling back to an outer savepoint destroys any savepoints created after it, because they mark states that no longer exist. ROLLBACK TO SAVEPOINT does not consume its savepoint either, so you can retry a step in a loop and rewind to the same mark each time; reusing a name creates a second savepoint that shadows the first, and rolling back to that name reaches the newer one.
Savepoints are cheap to use but they are not free structure: each one starts a subtransaction, so a loop that sets a savepoint per row can turn a fast batch into a slow one.
-- PostgreSQL 16
CREATE TABLE accounts (id int PRIMARY KEY, owner text, balance numeric);
INSERT INTO accounts VALUES (1, 'ada', 100), (2, 'linus', 50);
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1; -- the debit must survive
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 30 WHERE id = 1; -- mistake: credited the sender
ROLLBACK TO SAVEPOINT after_debit; -- undo only the mistake
UPDATE accounts SET balance = balance + 30 WHERE id = 2; -- credit the right account
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;A savepoint is a named marker inside one transaction, so ROLLBACK TO SAVEPOINT rewinds to that marker instead of discarding the entire transaction.
Worked examples
Surviving a constraint violation
A savepoint lets a transaction continue after a statement fails, instead of the whole transaction being thrown away.
-- PostgreSQL 16
CREATE TABLE tags (name text PRIMARY KEY);
BEGIN;
INSERT INTO tags VALUES ('sql');
SAVEPOINT before_risky;
INSERT INTO tags VALUES ('sql'); -- fails: already there
ROLLBACK TO SAVEPOINT before_risky;
INSERT INTO tags VALUES ('joins');
COMMIT;
SELECT name FROM tags ORDER BY name;Example explained
Line 1The second INSERT violates tags_pkey, which marks the whole transaction as aborted, not just that statement.
Line 2ROLLBACK TO SAVEPOINT before_risky is one of the few commands accepted while aborted, and it returns the transaction to a usable state.
Line 3Without that savepoint, the following INSERT would fail too and COMMIT would report ROLLBACK instead of COMMIT.
Line 4The row 'sql' survives because it was inserted before the savepoint was set.
RELEASE does not make work permanent
Releasing an inner savepoint keeps its changes, and rolling back to an outer savepoint still discards them.
-- PostgreSQL 16
CREATE TABLE stock (sku text PRIMARY KEY, qty int);
INSERT INTO stock VALUES ('a1', 10);
BEGIN;
UPDATE stock SET qty = qty - 1 WHERE sku = 'a1'; -- 9
SAVEPOINT s1;
UPDATE stock SET qty = qty - 2 WHERE sku = 'a1'; -- 7
SAVEPOINT s2;
UPDATE stock SET qty = qty - 4 WHERE sku = 'a1'; -- 3
RELEASE SAVEPOINT s2;
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT qty FROM stock;Example explained
Line 1SAVEPOINT s2 is set after s1, so it sits above s1 on the savepoint stack.
Line 2RELEASE SAVEPOINT s2 keeps the qty - 4 update and only discards the marker, which is why the count is still 3 at that point.
Line 3ROLLBACK TO SAVEPOINT s1 undoes both the -2 and the -4, including work whose savepoint was already released.
Line 4Only the -1 applied before s1 remains, so COMMIT stores 9.
Important notes
The spelling is not universal. PostgreSQL, MySQL/InnoDB, Oracle, and SQLite use SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT, while SQL Server uses SAVE TRANSACTION name and ROLLBACK TRANSACTION name and has no release command.
Rolling back to a savepoint is not a way to unblock another session: the locks stay with the enclosing transaction, and other sessions keep waiting until it commits or rolls back.
Common mistakes
Typing plain ROLLBACK when meaning ROLLBACK TO SAVEPOINT sp: the transaction ends and every change is discarded, including the work done before the savepoint, and the next statement runs outside any transaction.
Reading RELEASE SAVEPOINT as "commit this part": it writes nothing durably, and a later ROLLBACK still erases everything the released savepoint covered.
Rolling back to a savepoint that was already released, or one that was created after the savepoint you just rewound to: the name no longer exists, the command errors, and in PostgreSQL that error aborts the transaction you were trying to rescue.
Try it yourself
Change, predict, then run
Create orders(id int primary key, status text) with one row, then in a single transaction set status to 'paid', set a savepoint, delete the row, roll back to the savepoint, and commit. Finish with a SELECT that proves the row still exists and its status is 'paid'.
Open the SQL workspaceCheck your understanding
A transaction runs an UPDATE that succeeds, then SAVEPOINT s1, then an INSERT that succeeds, then RELEASE SAVEPOINT s1, then ROLLBACK. What is left in the tables?
- Nothing: ROLLBACK discards the entire transaction, and RELEASE never made anything permanent
- Both the UPDATE and the INSERT, because RELEASE SAVEPOINT committed the work up to that point
- Only the UPDATE, because releasing s1 kept what came before it and dropped what came after
- Only the INSERT, because ROLLBACK rewinds to the most recently released savepoint
Show answer
RELEASE SAVEPOINT only removes the marker; the changes it covered remain part of the enclosing transaction, and only COMMIT makes anything permanent, so the final ROLLBACK erases both statements. Option 2 is tempting because "release" sounds like flushing or committing, but no savepoint command can commit, and no savepoint command undoes work except ROLLBACK TO SAVEPOINT.