SQL vs NoSQL: How to Choose
SQL vs NoSQL explained: data models, consistency, scaling, query flexibility and when to use relational, document, key-value, wide-column or graph databases.
ACID and Transaction Isolation Levels
ACID and isolation levels explained: dirty reads, lost updates, write skew and phantoms, what each level prevents, database defaults and practical fixes.
Key takeaways
- ACID means atomicity, consistency, isolation and durability; isolation is the part with real trade-offs.
- Weaker isolation levels allow anomalies such as non-repeatable reads, lost updates and write skew.
- PostgreSQL defaults to Read Committed and MySQL InnoDB to Repeatable Read; use locks, atomic updates or Serializable where correctness depends on it.
On this page
A transaction groups several database operations so they succeed or fail together. ACID describes the guarantees: atomicity (all or nothing), consistency (constraints hold), isolation (concurrent transactions don’t interfere) and durability (committed data survives crashes). Isolation is the tricky one, because full isolation is expensive, so databases offer levels. Lower levels allow specific anomalies; Serializable prevents them all, at a cost in performance and retries.
ACID in brief
- Atomicity: if any step fails, the whole transaction rolls back. No half-transferred money.
- Consistency: each transaction moves the database from one valid state to another, as defined by constraints and application rules.
- Isolation: each transaction behaves as if it ran alone, to a degree set by the isolation level.
- Durability: once committed, data survives a crash, usually thanks to a write-ahead log.
The “C” in ACID is not the “C” in the CAP theorem: ACID consistency is about valid data; CAP consistency is about replicas agreeing.
The anomalies
| Anomaly | What happens |
|---|---|
| Dirty read | You read another transaction’s uncommitted change, which may be rolled back |
| Non-repeatable read | You read the same row twice and get different values, because another transaction committed in between |
| Phantom read | You run the same query twice and new rows appear |
| Lost update | Two transactions read a value, both modify it, and one overwrites the other’s change |
| Write skew | Two transactions read overlapping data, make decisions based on it, and write different rows, breaking a rule together |
Write skew is the subtle one. Classic example: a rule says at least one doctor must be on call. Two doctors, both on call, each check “is someone else on call?”, see yes, and both go off call. Neither transaction broke the rule alone; together they did.
The isolation levels
| Level | Prevents | Still allows |
|---|---|---|
| Read Uncommitted | Almost nothing | Dirty reads and everything else |
| Read Committed | Dirty reads | Non-repeatable reads, phantoms, lost updates, write skew |
| Repeatable Read / Snapshot | Dirty and non-repeatable reads (in many databases, phantoms too) | Write skew; lost updates in some implementations |
| Serializable | All of the above | Nothing: results match some serial order |
The SQL standard defines levels by which phenomena they rule out, and real databases implement them differently. A 1995 paper by Berenson and colleagues showed the standard’s definitions miss anomalies such as write skew, and described snapshot isolation, which many databases actually provide.
What real databases do
- PostgreSQL defaults to Read Committed. Its Repeatable Read uses a snapshot and doesn’t allow phantom reads; its Serializable level uses Serializable Snapshot Isolation, which detects dangerous patterns and aborts one transaction with a serialization failure that the application must retry.
- MySQL InnoDB defaults to Repeatable Read, using consistent snapshots for plain reads and locks for locking reads and writes.
- Most databases let you choose the level per transaction, for example with
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, so you can reserve stronger isolation for the few paths that need it.
Check your database’s documentation, because the same level name can mean different guarantees.
Practical fixes for common bugs
Lost updates on counters or balances: use atomic updates instead of read-modify-write in application code.
UPDATE accounts
SET balance = balance - 100
WHERE id = 7 AND balance >= 100;Check-then-act logic: lock the rows you’re reading.
SELECT * FROM seats
WHERE show_id = 12 AND seat = 'C4'
FOR UPDATE;Optimistic concurrency: add a version column and update only if it hasn’t changed; if no row is updated, reload and retry.
UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;Write skew and complex invariants: use Serializable for those transactions, or materialise the conflict as a row both transactions must lock.
Retrying safely
Serializable and some lock conflicts end in aborts that you’re expected to retry. In PostgreSQL, serialization failures carry SQLSTATE 40001, which makes them easy to detect. Keep transactions short, retry with a limit and backoff, and make sure the surrounding operation is safe to repeat; idempotency keys stop client retries turning into duplicates.
Transactions across services
ACID transactions live inside one database. When a business process spans several services, each with its own database, you need a different tool, such as the saga pattern, which replaces one big transaction with steps and compensating actions. This is one reason to keep data that must change together in one service, as our SQL vs NoSQL guide discusses when comparing transactional guarantees.
Frequently asked questions
Which isolation level should I use?
Start with your database’s default, and fix the specific paths where correctness depends on stronger guarantees: atomic updates, row locks or Serializable for those transactions.
Is Serializable too slow?
It costs more, mainly through aborted and retried transactions under contention. For many workloads it’s perfectly usable; measure before ruling it out.
What is snapshot isolation?
Each transaction reads from a consistent snapshot of committed data taken when it started. It prevents dirty and non-repeatable reads but still allows write skew.
Sources
Every article is edited by a human and checked against our editorial policy. Spotted a mistake? Tell us.
Keep reading
Database Sharding Explained
What database sharding is, when you need it, how to choose a shard key, range vs hash vs directory sharding, and the operational pain points to plan for.
B-Trees vs LSM Trees: How Databases Store Data
How B-tree and LSM-tree storage engines work, why one favours reads and the other writes, write and read amplification, compaction and which databases use each.