Transactions and ACID
Group changes so they all happen — or none do — with BEGIN, COMMIT, and ROLLBACK.
- Group statements into an all-or-nothing transaction
- Undo a transaction with ROLLBACK
- Explain the ACID guarantees, and isolation at a high level
Some operations are only correct as a unit. Transferring money debits one account and credits another — if the debit succeeds but the credit fails, money vanishes. A transaction groups statements so they all take effect, or none do.
BEGIN, COMMIT, ROLLBACK
Wrap the steps in BEGIN … COMMIT. If anything goes wrong, ROLLBACK undoes
everything since BEGIN — as if it never happened:
With ROLLBACK, both balances are unchanged — the transfer was abandoned
atomically. Swap it for COMMIT and both changes stick together.
ACID
Transactions give four guarantees, abbreviated ACID:
- Atomicity — all of the transaction happens, or none of it.
- Consistency — it moves the database from one valid state to another (constraints hold).
- Isolation — concurrent transactions don't see each other's half-finished work.
- Durability — once committed, it survives a crash.
These are exactly the distributed-systems concerns from the fundamentals track, handled for you by the database — which is a big reason to keep important state in one.
Isolation, briefly
When many transactions run at once, isolation levels control how much they can interfere (preventing anomalies like reading another transaction's uncommitted data). The trade-off is the familiar one: stricter isolation is safer but slower. The exact levels and defaults are engine-specific — territory for the dialect appendices.
Where to go next
Last in the module: window functions — calculations across related rows without collapsing them.