SQL › Safety Nets and Simultaneous Users

Savepoints and Isolation Levels

A savepoint marks a point inside a transaction that you can go back to without undoing the whole transaction. An isolation level decides which changes made by other users at the same time your transaction can see.

Syntax

Order of execution: BEGIN → statements → SAVEPOINT name marks the spot → more statements → ROLLBACK TO SAVEPOINT name undoes only the work after the mark, and the transaction stays open → COMMIT keeps what's left.

Code
BEGIN;
statement;
SAVEPOINT name;
statement;
ROLLBACK TO SAVEPOINT name;        -- undo since the savepoint
RELEASE SAVEPOINT name;            -- drop the mark, keep the work
COMMIT;
 
BEGIN ISOLATION LEVEL REPEATABLE READ;    -- choose a level for one transaction (not allowed in the lab)

SQL Server writes SAVE TRANSACTION name and ROLLBACK TRANSACTION name. SQL Server and Oracle have no RELEASE SAVEPOINT.

The table shows PostgreSQL. READ COMMITTED is the default in PostgreSQL, SQL Server and Oracle; MySQL's default is REPEATABLE READ. Oracle has no REPEATABLE READ. SQL Server, by default, makes the other user wait instead of showing an older copy of the row. The standard also has READ UNCOMMITTED, which can see changes not yet committed; PostgreSQL treats it as READ COMMITTED.

Examples

Undo part of a transaction

Only the work after the savepoint is undone.

Code
BEGIN;
UPDATE products SET stock = stock + 100 WHERE product_id = 21;      -- restock the rice
SAVEPOINT before_oil;
UPDATE products SET stock = 0 WHERE product_id = 22;                 -- a mistake
ROLLBACK TO SAVEPOINT before_oil;                                    -- the rice restock stays
COMMIT;
SELECT product_id, stock FROM products WHERE product_id IN (21, 22) ORDER BY product_id;

Several savepoints

Going back to an earlier savepoint also removes every savepoint made after it.

Code
BEGIN;
SAVEPOINT first_step;
UPDATE customers SET loyalty_points = 0 WHERE customer_id = 1;
SAVEPOINT second_step;
UPDATE customers SET loyalty_points = 0 WHERE customer_id = 2;
ROLLBACK TO SAVEPOINT first_step;           -- both updates are undone
-- Fails: second_step was removed by the rollback to first_step
ROLLBACK TO SAVEPOINT second_step;

Keep going after a rollback

After ROLLBACK TO, the transaction is still open and the savepoint still exists.

Code
BEGIN;
SAVEPOINT try_price;
UPDATE products SET price = price * 2 WHERE product_id = 5;
ROLLBACK TO SAVEPOINT try_price;
UPDATE products SET price = price * 1.05 WHERE product_id = 5;      -- try again
RELEASE SAVEPOINT try_price;
COMMIT;
SELECT price FROM products WHERE product_id = 5;

The current isolation level

Code
SHOW transaction_isolation;      -- read committed unless you chose another level

Two users at the same time

User B changes a price while user A's transaction is open. What A's second read shows depends on A's isolation level:

Remember

• ROLLBACK TO SAVEPOINT undoes only the work after the savepoint; the transaction stays open.
• Rolling back to an earlier savepoint removes the savepoints made after it.
• RELEASE keeps the work; only COMMIT makes it permanent.
• READ COMMITTED is PostgreSQL's default: each statement sees the latest committed data.
• In PostgreSQL, after an error, ROLLBACK TO a savepoint made before it to carry on (the lab stops at the first error).

Practice

1. Undo Only the Delhi Bonus (Easy)
2. Back to the First Savepoint (Medium)
3. Points to Wallet in Two Batches (Hard)