What is a transaction?
A transaction groups several database operations so they all succeed or all fail together. Transfer money between two accounts and you must debit one and credit the other as a single unit — never one without the other. ACID is the set of guarantees that makes this safe.
Why it matters
Concurrent requests touching the same data are where subtle, expensive bugs live: double charges, lost updates, negative balances. Transactions and the right isolation level are how you keep data correct when many users hit it at once. Getting this wrong corrupts data in ways that are hard to even detect.
What to learn
- ACID: atomicity, consistency, isolation, durability
- BEGIN, COMMIT, ROLLBACK
- Isolation levels: read committed, repeatable read, serializable
- Race conditions: lost updates and write skew
- Row locks and
SELECT ... FOR UPDATE - Keeping transactions short
- Idempotency keys for safe retries
Common pitfall
Read-modify-write without a transaction or lock. Two requests both read a balance of 100, both subtract 10, and both write 90 — one update is lost. Wrap the read and write in a transaction with appropriate locking, or use an atomic update that computes the new value in the database.
Resources
Primary (free):
- PostgreSQL — Transactions · docs
- PostgreSQL — Transaction isolation · docs
- PostgreSQL — Explicit locking · docs
Practice
Model a wallet with a balance column. Write a transfer function that debits one row and credits another inside a single transaction, and make it roll back if either step fails. Then simulate two concurrent transfers and confirm the total balance is never corrupted. Done when no run loses an update.
Outcomes
- Explain each letter of ACID with a concrete example.
- Wrap multi-step writes in a transaction that rolls back on failure.
- Pick an isolation level for a given correctness need.
- Prevent lost updates with locking or atomic writes.