Transactions and ACID
A member ends a ride. Pedal must close the rental, charge the fare and mark the bike as free. If the server crashes after the charge but before the bike is freed, the member pays and the bike stays "in use" forever. A transaction makes those three changes one unit.
The rule
All or nothing, always valid, unseen until done, kept once committed.
Those four phrases are ACID:
- Atomicity: all or nothing. Every statement in the transaction takes effect, or none does.
- Consistency: always valid. Each transaction moves the database from one state that satisfies every constraint to another.
- Isolation: unseen until done. Other sessions do not see half-finished work.
- Durability: kept once committed. After
COMMITreturns, the change survives a crash or power cut.
BEGIN;
UPDATE rentals SET ended_at = now(), fare = 120.00 WHERE rental_id = 9041;
INSERT INTO payments (rental_id, amount) VALUES (9041, 120.00);
UPDATE bikes SET available = true WHERE bike_id = 7;
COMMIT;Some PostgreSQL behaviour every DBA should know:
- Autocommit: without
BEGIN, each statement is its own transaction and commits at once. - An error poisons the transaction. After a failed statement, PostgreSQL answers every further command with current transaction is aborted until you
ROLLBACK. - Savepoints (
SAVEPOINT s1; ... ROLLBACK TO s1;) undo part of a transaction without abandoning all of it. - Isolation levels: the default is
READ COMMITTED, where each statement sees data committed before it began.REPEATABLE READkeeps one snapshot for the whole transaction, andSERIALIZABLEalso guarantees the result matches some one-at-a-time order, at the cost of occasional retries. - Long transactions are expensive: they hold locks and stop old row versions being cleaned up, as the next lesson shows.
The shortcut
Group work by asking "If the server died between these two statements, would the data lie?" If yes, they go in one transaction.
Close the rental alone?
Crash after it: the rental is closed but nobody paid. The data lies, so it must share a transaction.
The trap is holding a transaction open while waiting on something slow, such as a payment gateway or a person. Keep transactions short: gather what you need first, then write.
Drills
Answer fast — trust the rule.
Which ACID property promises a committed change survives a power cut?
Inside a transaction, the INSERT into payments fails. What happens to the next UPDATE you send?
What is PostgreSQL's default isolation level?
A job opens a transaction, then waits ten minutes for a partner's API. What is the main problem?
Remember
- All or nothing, always valid, unseen until done, kept once committed.
- If a crash between two statements would make the data lie, they share a transaction.
- An error aborts the whole transaction; keep transactions short.