SAVEPOINT and changes at the same time
Undo just part of a transaction, and see how databases keep many users from colliding.
Lesson 30 of 33 · about 15 minutes
The lesson
A SAVEPOINT is a bookmark inside a transaction. ROLLBACK TO undoes back to the bookmark, and keeps everything before it:
BEGIN;
INSERT INTO customers (name, city, country, joined_on)
VALUES ('Mateo García', 'Madrid', 'Spain', '2025-09-10');
SAVEPOINT after_mateo;
INSERT INTO customers (name, city, country, joined_on)
VALUES ('Test Person', 'Test', 'Test', '2025-09-11');
ROLLBACK TO after_mateo; -- the test row goes, Mateo stays
COMMIT;
It's like a video game checkpoint: you go back a little way instead of starting the level again.
Many people at once. A real bookshop has many shoppers at the same moment. Imagine Ana in Lisbon and Ken in Osaka both buying the last copy of a book at the same second. Both check the stock and see 1. Both buy. Now the stock is -1 and two people expect a book that only one can have.
Databases prevent this with isolation: each transaction behaves as if it were alone. While Ana's transaction is changing that book's stock, Ken's has to wait its turn. When Ken's goes ahead, it sees stock 0 and the purchase is refused. This waiting is done with locks, like a "please wait" sign on the row.
The four promises. Together, these are known as ACID:
| Letter | Promise | In plain words |
|---|---|---|
| A | Atomic | All or nothing |
| C | Consistent | The rules (keys, NOT NULL, CHECK) are never broken |
| I | Isolated | Transactions at the same time don't trip over each other |
| D | Durable | Once committed, it survives a crash or power cut |
- SAVEPOINT name sets a bookmark; ROLLBACK TO name undoes back to it and keeps earlier changes.
- Isolation and locks stop users who change the same data at the same time from colliding.
- ACID: Atomic, Consistent, Isolated, Durable.