Savepoint - SQL Programming

A savepoint marks a point inside a transaction. ROLLBACK TO returns to that mark without canceling earlier work.

Program

Play the script to keep a review status change while undoing a selected risky total adjustment.

                            extra
                            extra = 30 (default)extra = 15extra = 45
                        

                            savepoint_recover.sql
                        
Replay: real traced execution (multi-file project)

                            CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);
INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);
BEGIN;
UPDATE orders SET status = 'checked' WHERE id = 1;
SAVEPOINT risky_adjustment;
WITH params(extra) AS (VALUES (30)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;
ROLLBACK TO risky_adjustment;
RELEASE risky_adjustment;
COMMIT;
SELECT id, status, total FROM orders ORDER BY id;
CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);
INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);
BEGIN;
UPDATE orders SET status = 'checked' WHERE id = 1;
SAVEPOINT risky_adjustment;
WITH params(extra) AS (VALUES (15)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;
ROLLBACK TO risky_adjustment;
RELEASE risky_adjustment;
COMMIT;
SELECT id, status, total FROM orders ORDER BY id;
CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);
INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);
BEGIN;
UPDATE orders SET status = 'checked' WHERE id = 1;
SAVEPOINT risky_adjustment;
WITH params(extra) AS (VALUES (45)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;
ROLLBACK TO risky_adjustment;
RELEASE risky_adjustment;
COMMIT;
SELECT id, status, total FROM orders ORDER BY id;

                        
1tables ← 1 row1CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);⋯values this step1 rowtables
2orders ← 2 rows1CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);3BEGIN;⋯values this step2 rowsorders
3orders ← 2 rows⋯2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);3BEGIN;4UPDATE orders SET status = 'checked' WHERE id = 1;⋯values this step2 rowsorders
4orders ← 2 rows⋯3BEGIN;4UPDATE orders SET status = 'checked' WHERE id = 1;5SAVEPOINT risky_adjustment;⋯values this step2 rowsorders
5orders ← 2 rows⋯4UPDATE orders SET status = 'checked' WHERE id = 1;5SAVEPOINT risky_adjustment;6WITH params(extra) AS (VALUES (30)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;⋯values this step2 rowsorders
6orders ← 2 rows⋯5SAVEPOINT risky_adjustment;6WITH params(extra) AS (VALUES (30)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;7ROLLBACK TO risky_adjustment;⋯values this step2 rowsorders
7orders ← 2 rows⋯6WITH params(extra) AS (VALUES (30)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;7ROLLBACK TO risky_adjustment;8RELEASE risky_adjustment;⋯values this step2 rowsorders
8orders ← 2 rows⋯7ROLLBACK TO risky_adjustment;8RELEASE risky_adjustment;9COMMIT;⋯values this step2 rowsorders
9orders ← 2 rows⋯8RELEASE risky_adjustment;9COMMIT;10SELECT id, status, total FROM orders ORDER BY id;values this step2 rowsorders
10result ← 2 rows⋯9COMMIT;10SELECT id, status, total FROM orders ORDER BY id;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);⋯values this step1 rowtables
2orders ← 2 rows1CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);3BEGIN;⋯values this step2 rowsorders
3orders ← 2 rows⋯2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);3BEGIN;4UPDATE orders SET status = 'checked' WHERE id = 1;⋯values this step2 rowsorders
4orders ← 2 rows⋯3BEGIN;4UPDATE orders SET status = 'checked' WHERE id = 1;5SAVEPOINT risky_adjustment;⋯values this step2 rowsorders
5orders ← 2 rows⋯4UPDATE orders SET status = 'checked' WHERE id = 1;5SAVEPOINT risky_adjustment;6WITH params(extra) AS (VALUES (15)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;⋯values this step2 rowsorders
6orders ← 2 rows⋯5SAVEPOINT risky_adjustment;6WITH params(extra) AS (VALUES (15)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;7ROLLBACK TO risky_adjustment;⋯values this step2 rowsorders
7orders ← 2 rows⋯6WITH params(extra) AS (VALUES (15)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;7ROLLBACK TO risky_adjustment;8RELEASE risky_adjustment;⋯values this step2 rowsorders
8orders ← 2 rows⋯7ROLLBACK TO risky_adjustment;8RELEASE risky_adjustment;9COMMIT;⋯values this step2 rowsorders
9orders ← 2 rows⋯8RELEASE risky_adjustment;9COMMIT;10SELECT id, status, total FROM orders ORDER BY id;values this step2 rowsorders
10result ← 2 rows⋯9COMMIT;10SELECT id, status, total FROM orders ORDER BY id;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);⋯values this step1 rowtables
2orders ← 2 rows1CREATE TABLE orders (id INTEGER, status TEXT, total INTEGER);2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);3BEGIN;⋯values this step2 rowsorders
3orders ← 2 rows⋯2INSERT INTO orders VALUES (1, 'new', 40), (2, 'new', 20);3BEGIN;4UPDATE orders SET status = 'checked' WHERE id = 1;⋯values this step2 rowsorders
4orders ← 2 rows⋯3BEGIN;4UPDATE orders SET status = 'checked' WHERE id = 1;5SAVEPOINT risky_adjustment;⋯values this step2 rowsorders
5orders ← 2 rows⋯4UPDATE orders SET status = 'checked' WHERE id = 1;5SAVEPOINT risky_adjustment;6WITH params(extra) AS (VALUES (45)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;⋯values this step2 rowsorders
6orders ← 2 rows⋯5SAVEPOINT risky_adjustment;6WITH params(extra) AS (VALUES (45)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;7ROLLBACK TO risky_adjustment;⋯values this step2 rowsorders
7orders ← 2 rows⋯6WITH params(extra) AS (VALUES (45)) UPDATE orders SET total = total + (SELECT extra FROM params) WHERE id = 1;7ROLLBACK TO risky_adjustment;8RELEASE risky_adjustment;⋯values this step2 rowsorders
8orders ← 2 rows⋯7ROLLBACK TO risky_adjustment;8RELEASE risky_adjustment;9COMMIT;⋯values this step2 rowsorders
9orders ← 2 rows⋯8RELEASE risky_adjustment;9COMMIT;10SELECT id, status, total FROM orders ORDER BY id;values this step2 rowsorders
10result ← 2 rows⋯9COMMIT;10SELECT id, status, total FROM orders ORDER BY id;values this step2 rowsresult

Follow the Savepoint

The table starts with order 1 as new 40 and order 2 as new 20.
Before the savepoint, order 1 changes to status checked.
After the savepoint, the risky total adjustment adds 30 to order 1.
ROLLBACK TO risky_adjustment cancels only that total change.
COMMIT keeps the status change, so order 1 is checked 40 and order 2 is new 20. | order | status after commit | total after commit | | --- | --- | --- | | 1 | checked | 40 | | 2 | new | 20 |

SAVEPOINT `SAVEPOINT name` marks a point within the current transaction.

ROLLBACK TO `ROLLBACK TO name` undoes work after that savepoint.

RELEASE `RELEASE name` closes the savepoint before the outer transaction commits.

Exercise: savepoint_recover.sql

Reproduce the final rows 1 checked 40 and 2 new 20, then use the pinned extra variants 15 and 45 to predict the same final table because the risky adjustment is rolled back.