ACID Transactions & Isolation Levels: A Practical Guide

Q: What do the four ACID properties actually mean?

Atomicity: a transaction's writes apply entirely or not at all — a crash mid-transaction leaves no half-applied state, because the write-ahead log lets the database roll incomplete work back. Consistency: the transaction moves the database from one valid state to another, where "valid" means your declared rules — constraints, foreign keys, checks — hold before and after; notably, this is the property that depends most on what you declare, since the database cannot enforce rules you never expressed. Isolation: concurrent transactions behave as if they ran one at a time — and this is the property sold at a discount, because full isolation (serializable) is expensive, so databases default to weaker levels that permit specific, well-catalogued anomalies. Durability: once the database acknowledges a commit, the data survives a crash — implemented by fsyncing the log before acknowledging, with the modern asterisk that an asynchronously replicated commit can still vanish if the primary dies before replicas catch up. The practical summary: A and D are engineering you inherit for free; C is only as strong as your constraints; I is a dial you must consciously set per workload.

Q: What is the difference between isolation levels?

Each level is precisely a list of concurrency anomalies it permits. Read Uncommitted allows dirty reads — seeing another transaction's uncommitted writes — and is essentially unused in modern engines (Postgres accepts the syntax and silently gives you Read Committed). Read Committed, the Postgres default, blocks dirty reads but permits non-repeatable reads (a row changes between two reads in your transaction), phantoms (a range query's result set changes), and lost updates (two read-modify-write cycles overwrite each other). Repeatable Read in Postgres is true snapshot isolation via MVCC: your transaction sees a frozen snapshot of the database as of its start, which kills non-repeatable reads and phantoms without read locks — but write skew still slips through, because two transactions can each read a condition and write different rows whose combination violates it. Serializable blocks everything, guaranteeing the outcome equals some serial execution order; Postgres implements it optimistically (SSI), detecting dangerous patterns and aborting one participant, which is why serializable code must retry on SQLSTATE 40001. MySQL InnoDB defaults to Repeatable Read with gap-locking behaviour that differs meaningfully from Postgres — the engine-by-engine truth is what the Hermitage test suite documents.

Q: What is write skew and why does snapshot isolation not prevent it?

Write skew is two transactions each reading a shared condition, then each writing a different row, such that the combined result violates an invariant neither violated alone. The canonical example: a hospital requires at least one doctor on call; Alice and Bob are both on call; two simultaneous transactions each check "are there at least two on call? yes" and each removes a different doctor; both commit; zero doctors are on call. Snapshot isolation cannot catch it because each transaction's snapshot was internally consistent and their write sets do not overlap — no row was written by both, so no write-write conflict exists to detect. The anomaly lives in the read-write dependency between them: each wrote something the other read. Only serializable isolation tracks those dependencies (Postgres SSI aborts one transaction when it detects the dangerous cycle), which is precisely why the level exists despite its cost. The alternatives if you cannot afford serializable on that path: materialize the conflict — make both transactions write the same row, for example a lock row per on-call roster via SELECT FOR UPDATE, converting invisible skew into an ordinary row conflict — or enforce the invariant with a constraint or trigger that re-checks the rule at write time. Any multi-row business rule (inventory floors, accounting balance, capacity limits) is a write-skew candidate and deserves one of these three treatments.

Q: How do lost updates happen and how do I prevent them?

The generator is read-modify-write in application code: two requests both read balance = 100, both compute 100 minus 30 in application memory, both write 70 — one withdrawal has evaporated, and Read Committed permits it because each write was individually valid. Prevention, cheapest first. One: push the arithmetic into the database — UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance >= 30 — the row change serializes on the row lock, and checking rows-affected tells you whether the guard held; this is atomic, contention-friendly, and free. Two: pessimistic locking — SELECT ... FOR UPDATE reads the row and holds a lock until commit, so the second transaction's read waits for the first's write; right when the modify step genuinely needs application logic between read and write. Three: optimistic concurrency — carry a version column, write with WHERE id = ? AND version = ?, and retry the whole cycle when zero rows match; right for low-contention paths and APIs where holding locks across user think-time is unacceptable. Four: run the transaction at Repeatable Read or Serializable, where the engine detects the conflict and aborts one participant (which your code must retry). Most production systems use the first two for hot paths and versioning for user-facing edits; what they never do is trust the default isolation level to save them.

Q: When should I use SELECT FOR UPDATE?

Use it when a transaction reads a row specifically to decide how to modify it and correctness depends on nobody else changing that row in between — the read-modify-write window that Read Committed leaves open. FOR UPDATE takes a row lock at read time, so concurrent claimants queue behind you until commit: the right tool for claiming work items, adjusting balances with logic too complex for a single UPDATE, enforcing per-entity sequencing, or materializing a write-skew conflict onto a designated lock row. The discipline it demands: keep the locked window milliseconds long — never hold FOR UPDATE across an external API call, an LLM request, or user think-time, because every other claimant of that row is stalled behind you and lock queues cascade into timeouts; lock rows in a consistent order across code paths, or two transactions locking A-then-B and B-then-A will deadlock (the database resolves it by killing one, which your code must handle); and prefer SKIP LOCKED for queue-consumption patterns, where workers should claim the next unlocked row rather than wait — the foundation of Postgres-backed job queues. If the modification is expressible as a single guarded UPDATE, skip the lock entirely: the atomic statement is cheaper, cannot be held open by a slow application, and cannot deadlock on itself.

Q: Should I just run everything at serializable isolation?

It is the only defensible global default if you can afford it — CockroachDB ships it as the default and its users live well — but on Postgres and MySQL the honest answer for most teams is: scope it. The costs are real and workload-shaped: serializable adds dependency tracking on every transaction, and on contended workloads it aborts transactions to preserve correctness, so throughput drops anywhere from a few percent (low contention) to 40 percent or more (hot rows, long transactions), and every serializable code path needs a retry loop for SQLSTATE 40001 — typically 3 attempts with backoff starting around 50 ms. Paying that on every transaction to protect the small fraction with genuine multi-row invariants is buying insurance on things that cannot crash. The scoped strategy: default Read Committed; make single-row hot paths safe with guarded atomic UPDATEs and FOR UPDATE; reserve SET TRANSACTION ISOLATION LEVEL SERIALIZABLE for the paths where a multi-row rule must hold — ledger entries that must balance, capacity and inventory floors, anything write-skew-shaped. Two honest exceptions favour going global: greenfield systems (retrofitting retry loops later is much harder than starting with them) and teams without the discipline to maintain the inventory of which paths carry invariants — a blanket guarantee beats a stale spreadsheet.

Q: What is the default isolation level in PostgreSQL and MySQL?

PostgreSQL defaults to Read Committed: each statement sees data committed before that statement began, so dirty reads are impossible, but non-repeatable reads, phantoms, and lost updates are all permitted within a multi-statement transaction. MySQL InnoDB defaults to Repeatable Read: consistent snapshot reads within the transaction plus gap locks on indexed ranges, which blocks more anomalies than Postgres's default but with locking behaviour (gap and next-key locks) that surprises people — including deadlocks on inserts into contended ranges — and its plain reads are non-locking snapshot reads while UPDATE and FOR UPDATE reads see the latest committed data, a mismatch that produces its own subtle bugs. Neither default prevents write skew; neither is serializable. Two practical consequences follow. First, portability: code that behaves correctly on MySQL's default can exhibit lost updates when ported to Postgres unchanged, because the defaults differ by a full level. Second, auditability: since the level is per-session and per-transaction, verify what you actually run — SHOW transaction_isolation in Postgres, SELECT @@transaction_isolation in MySQL — because a connection pooler or ORM may be setting it under you. Treat the engine's documented default as the beginning of the investigation, not the answer: the Hermitage suite exists precisely because implementations of the same level name block different anomalies.

Q: Does ACID apply across microservices?

No — and no configuration makes it apply. ACID's guarantees are the property of a single database engine coordinating its own transactions; the moment an operation spans two services with two databases, there is no shared transaction, no shared isolation level, and no atomic commit across them (two-phase commit exists but is avoided in modern service architectures for its blocking failure modes and coordinator fragility). The distributed substitutes trade isolation for explicitness. Sagas break the operation into local transactions with compensating actions — order created, payment charged, inventory reserved, and if reservation fails, payment is refunded — accepting that intermediate states are visible (a customer may see the order confirmed before inventory settles) in exchange for no distributed locks. The outbox pattern makes each local step atomic with its outgoing events, closing the dual-write gap between a database commit and the message that announces it. Idempotency keys make the inevitable retries harmless, since at-least-once delivery is a certainty between services. And invariants that genuinely must hold atomically — the balance check and the debit — should be co-located in one service, one database, one transaction, which is a data-ownership design decision, not an infrastructure setting. The rule of thumb: inside the database, isolation is a dial; between databases, it is architecture.

Satyam Kumar

عودة إلى المدونة

ai-architecture

ACID Transactions and Isolation Levels, Explained with Failures

By Satyam KumarJuly 8, 20268 min read

acid transactions database isolation levels serializable isolation snapshot isolation read committed repeatable read dirty read phantom read lost update write skew postgres mvcc select for update transaction retry database concurrency cockroachdb database architecture 2026

ACID Transactions and Isolation Levels, Explained with Failures

Frequently Asked Questions

شارك هذه المقالة

Twitter LinkedIn WhatsApp

Satyam Kumar

Founder & AI Architect, AppScale LLP

مهندس الذكاء الاصطناعي والسحابة. مساعدة الفرق على بناء أنظمة تتسع للملايين.

LinkedIn GitHub

ACID Transactions and Isolation Levels, Explained with Failures

Frequently Asked Questions

شارك هذه المقالة

Comments

Leave a comment