Part 123: Partial Rollbacks with SAVEPOINT & ROLLBACK TO

A SAVEPOINT creates a checkpoint inside an active transaction, allowing partial rollback without abandoning the entire transaction.

1. Syntax

BEGIN;

INSERT INTO orders (id, total) VALUES (1, 100.00);

SAVEPOINT after_order;

-- Attempt an optional step:
INSERT INTO order_notifications (order_id, recipient) VALUES (1, 'invalid_address');

-- If optional step fails, rollback ONLY to the savepoint:
ROLLBACK TO SAVEPOINT after_order;

-- The order remains inserted! Commit the core work:
COMMIT;

Key Takeaways

• Use SAVEPOINT for optional or speculative operations within complex transactions.

In Part 124, we explore Concurrency Anomalies: Dirty Reads, Non-Repeatable Reads & Phantom Reads!