Explain ACID & Transactions in Databases

7 / 66

transactions & locking

ACID & Transactions

Group multiple statements so they succeed or fail as a unit — protecting consistency in the face of crashes and concurrency.

A transaction groups several statements so they behave as a single indivisible operation, which is the foundation of correctness whenever one logical change touches more than one row or table. The canonical example — transferring money by debiting one account and crediting another — shows why: if the system crashes between the two updates, money must not vanish or be created. Transactions guarantee that either both happen or neither does. The properties they provide are captured by the acronym ACID.

A bank wire — debit one account, credit another. Both happen, or neither. A power outage mid-transfer leaves both accounts as if nothing happened.

Key Concepts

Atomicity means all-or-nothing: the whole transaction commits, or it rolls back leaving no partial effects, even across a crash. Consistency means a transaction moves the database from one valid state to another, preserving every declared constraint, foreign key, and invariant; a transaction that would violate a rule is rejected wholesale. Isolation means concurrent transactions do not corrupt each other — to a degree set by the isolation level — so that interleaved execution yields results equivalent to some serial order. Durability means once a transaction commits, its effects survive crashes and power loss, typically guaranteed by a write-ahead log flushed to stable storage before the commit is acknowledged. Databases deliver these with mechanisms like the WAL/redo log for atomicity and durability and locking or multi-version concurrency control for isolation.

The interview-worthy nuances: of the four, Isolation is the one you actively tune and trade off against throughput, which is why isolation levels are a topic of their own, whereas Atomicity and Durability are largely non-negotiable guarantees of the engine. Consistency in ACID (constraint validity) is a different idea from the "consistency" in the CAP theorem (all nodes seeing the same data) — conflating the two is a common slip. And a transaction held open too long magnifies lock contention, so the practical advice is to keep transactions short and focused.