Databases · intermediate
Database transaction and ACID
A database transaction groups operations into a commit or rollback boundary; ACID summarizes atomicity, consistency, isolation, and durability properties provided under the database's documented guarantees.
Why it matters
Transactions prevent related writes from committing permanently half-applied. Applications must separately choose an isolation level for concurrent visibility and encode the invariants they require.
Mental model
How to reason about database transaction and acid
Work happens in a provisional transaction view. Commit asks the database to make the unit durable and visible according to isolation rules; rollback abandons its changes.
Analogy
A batch of ledger changes is either accepted in full or rejected in full. A separate viewing policy determines whether anyone can inspect the draft while it is being prepared.
Examples
See the boundary, not just the happy path
Worked example · Transfer as one unit
BEGIN; debit account A; credit account B; COMMIT;A successful commit makes the related writes one transaction; an error can roll them back rather than preserve only the debit.
Worked example · Protect a business invariant
CHECK (balance >= 0) plus a suitable transaction protocolConsistency is not automatic knowledge of business rules; constraints and application logic must state the valid states.
Useful contrast · Isolation level matters
two transactions read and update the same rowsAtomic commits alone do not determine which concurrent anomalies are possible. The selected isolation level and access pattern do.
Common mistakes
Misconceptions to remove early
Treating ACID consistency as replica consistency
The C in ACID concerns preserving declared invariants across a transaction. Strong versus eventual consistency describes observation across copies or operations.
Keeping transactions open across user or network waits
Long transactions retain locks or old versions, increase conflict, and consume resources. Gather external input before opening the shortest practical transaction.
Quick check
Can you predict the result?
1. Which ACID property makes a transaction's writes commit as a unit or roll back?
- • Atomicity
- • Durability
- • Eventual consistency
2. Why must an application still understand its isolation level?
Keep building