Transactions
A grouping of reads and writes that should succeed or fail as a unit.
Level: intermediate
What is it?
A transaction groups database operations into a logical unit. It can commit its changes or roll them back, and the DBMS coordinates concurrent transactions and crash recovery according to configured isolation and durability guarantees.
Why does it matter?
Without transactions, a multi-step update can leave partial state after a failure. For example, a bank transfer should not debit an account while failing before crediting the destination.
Mental model
Think of a transaction as a guarded change set. The DBMS keeps track of the operations until commit decides that the change should become visible and durable according to the configured contract.
How it works
- BEGIN starts an explicit transaction in many SQL systems; COMMIT makes it successful, and ROLLBACK undoes its uncommitted effects.
- Atomicity means a transaction does not leave a partial logical update, though exact behavior can depend on the system and external side effects.
- Isolation defines how concurrent transactions may observe one another's operations.
- Durability depends on the database's commit protocol, logs, storage settings, and hardware assumptions.
- Transactions cannot automatically roll back emails, HTTP requests, or other side effects outside the database.
Steps
- Start a transaction for a transfer.
- Debit account A and credit account B, with validation and constraints.
- If any step fails, roll back the database changes.
- Commit only when the complete invariant holds; handle external messaging with an outbox or another reliable coordination pattern.
Real-world usage
- Maintaining invariants across related row updates.
- Building payment, inventory, and booking workflows.
- Understanding locking, MVCC, isolation anomalies, and recovery logs.
Common mistakes
- Committing each statement separately when the business operation must be atomic as a group.
- Assuming database rollback reverses an external API call already made.
- Keeping transactions open during slow user interaction, increasing lock and resource pressure.
Interview questions
Why use a transaction for a bank transfer?
Debit and credit must succeed or fail together so the database cannot expose a committed half-transfer.
Does a transaction guarantee serializable isolation by default?
No. Isolation level and database semantics determine which concurrent effects are allowed; many systems use a weaker default.
How can a database transaction coordinate reliably with an external message broker?
A common approach is the transactional outbox: write the business change and outgoing event record in one DB transaction, then publish from the outbox with retry/idempotency.