What is SAVEPOINT?
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
SAVEPOINT creates a checkpoint in your transaction. You can undo to that point without cancelling everything.
Simple analogy: Like save points in a video game - if you die, you go back to last save point (not the beginning).
Basic Syntax
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
BEGIN;
-- Do some work
SAVEPOINT my_checkpoint;
-- Do more work
ROLLBACK TO my_checkpoint; -- Go back to checkpoint
COMMIT;Simple Example
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
BEGIN;
UPDATE students SET grade = 'A' WHERE student_id = 1; -- Change 1
SAVEPOINT after_first_update; -- Checkpoint!
UPDATE students SET grade = 'B' WHERE student_id = 2; -- Change 2
UPDATE students SET grade = 'C' WHERE student_id = 3; -- Change 3
-- Oops, changes 2 and 3 were wrong!
ROLLBACK TO after_first_update; -- Undo changes 2 and 3, keep change 1
COMMIT; -- Save change 1 onlyResult: Student 1 has grade A, students 2 and 3 unchanged.
Real-Life Example
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
Online shopping cart:
BEGIN;
-- Add item 1
INSERT INTO cart (user_id, product_id) VALUES (1, 101);
SAVEPOINT added_item1;
-- Add item 2
INSERT INTO cart (user_id, product_id) VALUES (1, 102);
SAVEPOINT added_item2;
-- Add item 3
INSERT INTO cart (user_id, product_id) VALUES (1, 103);
-- Changed mind about item 3
ROLLBACK TO added_item2; -- Remove item 3 only
COMMIT; -- Save items 1 and 2Multiple Savepoints
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
You can create many checkpoints:
BEGIN;
UPDATE products SET price = 100 WHERE id = 1;
SAVEPOINT price_updated;
UPDATE products SET stock = 50 WHERE id = 1;
SAVEPOINT stock_updated;
UPDATE products SET name = 'New Name' WHERE id = 1;
-- Undo name change only
ROLLBACK TO stock_updated;
COMMIT; -- Save price and stock changesWhen to Use SAVEPOINT
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
Use when:
- Making multiple changes
- Might need to undo some (but not all)
- Complex multi-step operations
Example: Processing batch of orders
- Process order 1: SAVEPOINT
- Process order 2: SAVEPOINT
- Order 3 fails: ROLLBACK to savepoint 2
- Continue with order 4
Summary
Transaction Commands
| Command | What It Does | Analogy |
|---|---|---|
| BEGIN | Start transaction | Start task |
| SAVEPOINT | Create checkpoint | Save game progress |
| ROLLBACK TO | Undo to checkpoint | Load save point |
| ROLLBACK | Cancel everything | Restart game |
| COMMIT | Save permanently | Exit and save |
5 rows
SAVEPOINT = Checkpoint in transaction
- Like video game save points
- Undo to checkpoint (not beginning)
- Keep some changes, undo others
- Very useful for complex operations
Flow:
- BEGIN (start)
- Do work
- SAVEPOINT (checkpoint)
- Do more work
- ROLLBACK TO savepoint (if needed)
- COMMIT (finish)