ACID transactions

Why it matters

Transfer money from account A to account B: debit A, credit B. Crash between the two and money vanishes. Run two transfers at once without care and both read the same balance and one update is lost. Transactions are the database's answer to both problems, and they are the reason relational databases remain the default for anything involving money, inventory or state that must stay internally consistent. Knowing what they guarantee, and at which isolation level, tells you when you can rely on the database and when you must do the work yourself.

The idea

A transaction groups several reads and writes into one unit that either fully happens or does not happen at all, and that behaves as if it ran alone. ACID names the four promises.

Promise	Means	Failure it prevents	How it is implemented
Atomicity	All of the writes commit, or none do	A crash after the debit and before the credit	Write-ahead log: changes are logged before applied; on recovery, incomplete transactions are rolled back
Consistency	The transaction moves the database from one valid state to another; constraints hold	A negative balance, an order with no customer	Constraints, foreign keys, checks, triggers; the application supplies the rest
Isolation	Concurrent transactions do not see each other's intermediate state	Lost updates, reading half-applied changes	Locks or multi-version concurrency control (MVCC), per isolation level
Durability	Once committed, the change survives crashes	Acknowledged writes disappearing on power loss	Flushing the log to disk before acknowledging (fsync), plus replication

Isolation levels and the anomalies they permit

Full isolation is expensive, so SQL defines levels that allow specific anomalies in exchange for throughput.

Level	Dirty read	Non-repeatable read	Phantom	Write skew	Notes
Read uncommitted	Yes	Yes	Yes	Yes	Rarely used
Read committed	No	Yes	Yes	Yes	Default in Postgres and Oracle; each statement sees committed data as of its start
Repeatable read / snapshot	No	No	Postgres: no; MySQL: mostly no	Yes	Whole transaction sees one snapshot; default in MySQL InnoDB
Serializable	No	No	No	No	Equivalent to some serial order; Postgres uses SSI, others use strict two-phase locking

How isolation is implemented

Locking (two-phase locking) makes readers and writers block each other: simple, serializable when strict, and prone to contention and deadlocks. MVCC keeps multiple versions of each row stamped with transaction ids; readers see the version visible at their snapshot and never block writers, writers never block readers. Nearly every modern database (Postgres, MySQL InnoDB, Oracle, SQL Server in snapshot mode) uses MVCC, which is why "readers do not block writers" is something you can usually rely on. The cost is version cleanup (vacuum, purge) and the subtle anomalies like write skew that snapshots permit.

Practical patterns

Keep transactions short: hold them across network calls or user think time and you hold locks and versions for everyone. Use SELECT ... FOR UPDATE or an explicit constraint when a read-then-write decision must be safe under snapshot isolation. Retry on serialization failures, which serializable databases raise instead of allowing the anomaly. Use unique constraints to enforce invariants the application cannot reliably check (one active subscription per user). Across services, there is no shared transaction: that is the Distributed transactions module.

Trade-offs

Stronger isolation means fewer anomalies and more aborts or blocking; most applications run at read committed or snapshot and add targeted locks or constraints where it matters. Durability's fsync costs latency per commit, which is why batched or group commits exist, and why "durable" must be read with the replication setting in mind: a commit acknowledged by one node is only as durable as that node until replicas have it.

In an interview

Name the level and the exception: "Postgres at read committed for throughput; the inventory decrement uses SELECT FOR UPDATE so two checkouts cannot both see stock and both decrement; the one-active-subscription rule is a unique partial index, not application logic." If someone says "ACID solves it", add: "within one database; across services we need sagas or an outbox."