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.

savepoint_recover.sql

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 ()) 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 ()) 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 ()) 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;

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.