Database Transactions Explained: ACID, Isolation Levels, and Concurrency Control
What ACID actually guarantees, and how pessimistic vs optimistic concurrency control decide whether your app locks or retries under load.
On this page
A ride gets booked, an item gets purchased, an account balance gets updated — every one of these is actually several operations (read a row, validate a condition, write a new value, maybe write a second row) that must succeed or fail as a single unit. No half-finished orders. No account debited without the matching credit landing somewhere. That’s the entire reason transactions exist: turning “several things that could partially fail” into “one thing that either fully happens or doesn’t happen at all.”
What is a database transaction?
A transaction is a sequence of operations treated as a single logical unit. It begins, executes some number of reads and writes, and ends either by committing (all changes become permanent and visible) or rolling back (none of them do — as if nothing happened). The database guarantees you never observe a transaction halfway done: you see the world before it ran, or the world after it fully ran, never the in-between.
That guarantee is formalized as four properties — ACID:
Atomicity, Consistency, and Durability are largely mechanical — write-ahead logging and careful commit ordering handle them. Isolation is the hard one, because it’s the only property that has to hold up against other transactions running at the same time, and that’s where concurrency control lives.
Why isolation is the hard part
Picture two transactions running at nearly the same instant:
- Transaction A reads a bank balance of $500, plans to withdraw $100.
- Transaction B reads the same balance of $500, plans to withdraw $200.
If both read before either writes, both compute their new balance from the same stale $500. Whichever writes last wins, silently erasing the other withdrawal. No crash occurred, no code was obviously wrong — the bug is purely a timing problem. This is a race condition, and it’s the exact class of bug isolation exists to prevent.
Full isolation — behaving as if every transaction ran one after another, with zero overlap — is the safest guarantee (Serializable isolation) but also the most expensive to enforce. Every database offers weaker, cheaper isolation levels below it, each one tolerating a specific, well-documented class of anomaly in exchange for more concurrency.
Two philosophies of concurrency control
Given that many transactions want to touch overlapping data at once, a database has exactly two philosophies available for keeping them from corrupting each other.
Do
Reach for pessimistic locking when conflicts are frequent and the cost of a retry (re-running expensive logic, or user-visible failure) is higher than the cost of a wait.
Don't
Default to pessimistic locking everywhere out of caution. Under low contention it adds latency and lock-management overhead for conflicts that were never going to happen.
Pessimistic concurrency control: lock first, ask questions later
A transaction acquires a lock on a row (or range of rows) before touching it. Any other transaction that wants the same row has to wait until the lock releases. This is “pessimistic” in the literal sense — it assumes a conflict will happen, so it prevents one from being possible at all.
- Upside: correctness by construction. No transaction can ever read or write data another transaction is mid-flight on.
- Downside: you pay the cost of locking — and the latency of waiting — even for the transactions that were never going to conflict with anything. Under high contention, this also opens the door to deadlocks: transaction A holds a lock B needs, B holds a lock A needs, and neither can proceed. Databases detect this with a wait-for graph (a cycle in that graph means deadlock) and resolve it by aborting one transaction as the “victim” and letting it retry.
Optimistic concurrency control: proceed freely, check at the end
A transaction reads data without locking it, does its work, and only at commit time checks whether the data it read has since changed. If nothing changed, it commits. If something did, it aborts and the caller retries — usually by re-reading and re-applying the operation.
- Upside: no waiting for locks when contention is actually low, which is the common case for most reads and many writes. Throughput stays high because nothing blocks anything else during normal execution.
- Downside: under high contention, transactions repeatedly discover conflicts at commit time and have to retry — sometimes several times — which wastes the work already done and can degrade badly as contention rises.
The trade-off in one table
| Pessimistic | Optimistic | |
|---|---|---|
| Assumes | Conflicts are likely | Conflicts are rare |
| Cost paid | Lock acquisition + wait time, always | Commit-time check + retries, only on conflict |
| Best under | High contention (many transactions touching the same rows) | Low contention (transactions mostly touch different rows) |
| Failure mode | Deadlock (mutual waiting) | Repeated retry storms under sudden contention spikes |
| Typical mechanism | Row/table locks, SELECT ... FOR UPDATE | Version numbers or timestamps checked at commit (WHERE version = ?) |
Most production systems don’t pick one globally — they pick per operation. A checkout flow reserving the last unit of inventory is high-contention by nature (many customers racing for the same row) and often reaches for pessimistic locking or a dedicated queue. A profile update touching a row almost nobody else is editing at that moment is a textbook case for optimistic control — lock-free by default, retry on the rare conflict.
Isolation levels: the practical dial between the two
Most relational databases expose isolation as a configurable level rather than a binary choice, letting you trade correctness guarantees for throughput deliberately:
- Read Uncommitted: transactions can see each other’s uncommitted changes. Fastest, almost never used in practice — the anomalies it permits (dirty reads) are rarely acceptable.
- Read Committed: a transaction only ever sees committed data, but a value can change between two reads within the same transaction (non-repeatable reads). The default in many production Postgres and SQL Server deployments.
- Repeatable Read: a transaction sees a consistent snapshot for its entire duration — the same row read twice returns the same value — but new rows matching a query can still appear (phantom reads).
- Serializable: behaves as if transactions ran one at a time, in some order. No anomalies possible, at the cost of the most locking or the most abort-and-retry overhead.
Takeaway
Transactions exist to answer one question honestly: did this multi-step operation fully happen, or not at all. ACID is the contract that makes “fully happen” mean something concrete. The genuinely hard engineering problem hiding inside that contract is Isolation under concurrency — and the two answers to it, pessimistic locking and optimistic checking, aren’t competing techniques so much as opposite bets on how often your transactions actually collide. Get that bet right for each operation in your system, and correctness and throughput stop being in tension.
Frequently asked questions
What does ACID actually stand for, and why do all four properties matter together?
Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't corrupt each other), Durability (once committed, it survives a crash). They matter together because dropping any one breaks the guarantee the others depend on — atomicity without isolation still lets concurrent transactions see each other's half-finished work.
What's the practical difference between pessimistic and optimistic concurrency control?
Pessimistic control locks a row before touching it, so conflicting transactions wait — safe by construction, but you pay for locks even when conflicts are rare. Optimistic control lets everyone proceed unlocked and checks for conflicts at commit time, retrying the loser — fast under low contention, but wasteful and retry-heavy under high contention. The right choice depends on how often two transactions actually collide.
What is a race condition in a database transaction, and how does isolation prevent it?
A race condition happens when two transactions read and write overlapping data at the same time, and the final result depends on timing rather than correctness — e.g. two withdrawals both reading the same starting balance before either writes. Isolation levels define how much of that overlap is allowed; higher isolation prevents more race conditions at the cost of more locking or more retries.
Why would a database ever allow a weaker isolation level than Serializable?
Serializable isolation — behaving as if every transaction ran one at a time — is the safest but the most expensive, since it requires the most locking or the most conflict detection. Weaker levels (Read Committed, Repeatable Read) allow more concurrency and higher throughput by tolerating specific, well-understood anomalies that many applications can live with, like non-repeatable reads.
What causes a deadlock, and how do databases resolve it?
A deadlock happens when transaction A holds a lock transaction B needs, while B holds a lock A needs — neither can proceed. Databases detect this with a wait-for graph (a cycle means deadlock) and resolve it by picking a victim transaction to abort and retry, rather than letting both wait forever.
/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.