ACID Properties in Databases

A transaction is a group of database operations that the system treats as one indivisible unit — either all of them happen or none of them do. The reason we need this is that real work rarely fits in a single write. Moving money means subtracting from one account and adding to another; if the machine crashes between those two steps, one account has lost money that never arrived anywhere.

Without a transaction, every multi-step change risks leaving the data in a broken, half-updated state. A transaction wraps the steps so the outside world only ever sees "before" or "after," never "halfway." The four guarantees a database makes about that wrapper are named by the acronym ACID: Atomicity, Consistency, Isolation, Durability. Each is a separate promise, and the rest of this day takes them one at a time.

Every transaction follows the same shape: you open it, do some work, and then either commit (make the changes permanent) or roll back (discard everything you did as if it never happened). Naming these three moments makes the guarantees below concrete — each property is about what the database promises at one of these points.

Read it as a fork: work accumulates privately after BEGIN, and the transaction ends by taking exactly one of two exits. A commit publishes everything at once; a rollback erases everything at once. There is no third door where half the work leaks out.

Atomicity means a transaction is indivisible: the database either applies all of its writes or, if anything goes wrong, applies none of them. The word comes from "atom" in its original sense — something that cannot be split.

The classic example is a bank transfer of $100 from Alice to Bob. It is two writes: debit Alice, credit Bob. If the server dies after the debit but before the credit, atomicity requires the database to undo the debit on recovery, so Alice's $100 is not lost. The mechanism is rollback: if a transaction cannot finish, the database reverses every change it had started, returning to exactly the state before BEGIN.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
COMMIT;

If the second UPDATE fails — say Bob's row is missing — the database rolls the first one back too, and Alice keeps her money. Atomicity is what makes it safe to write multi-step logic without hand-coding "undo" for every failure path.

Consistency means a transaction takes the database from one valid state to another valid state, never leaving it in a state that breaks its rules. Those rules are the constraints you declared: primary keys are unique, foreign keys point at rows that exist, a CHECK says a balance may not go negative, a column marked NOT NULL is never empty.

If a transaction would violate any of these, the database refuses it and rolls it back rather than saving invalid data. In the transfer above, if a CHECK (balance >= 0) constraint exists and Alice only has $50, the debit fails the constraint, the whole transaction aborts, and no money moves.

A subtle point: the "C" in ACID is partly your responsibility. The database enforces the constraints you define, but it cannot know that a transfer should conserve total money unless you express that as a rule or write the logic correctly. Consistency is the guarantee that declared invariants always hold at commit time — not a promise that your business logic is correct.

Isolation means that when many transactions run at the same time, each behaves as if it were running alone — one transaction's half-finished work is not visible to another, and two transactions don't overwrite each other into nonsense. Without isolation, concurrency produces anomalies: specific, named ways that interleaved transactions read or write wrong values.

Three anomalies come up constantly:

• Dirty read — transaction A reads a value that transaction B has written but not yet committed. If B rolls back, A acted on data that never officially existed.
• Non-repeatable read — A reads a row, B updates and commits that row, and A reads the same row again and sees a different value within the one transaction.
• Phantom read — A runs a query returning a set of rows, B inserts a new row matching that query and commits, and A re-runs the query and sees an extra "phantom" row that wasn't there before.

The diagram shows a dirty read: A trusts a number that B then throws away. Isolation levels exist to rule out anomalies like this.

Perfect isolation is expensive, because making every transaction wait its turn kills concurrency. So the SQL standard defines four isolation levels — a dial that trades safety for speed. A higher level forbids more anomalies but allows less concurrency. You pick the weakest level that is still safe for the work at hand.

Reading the table top to bottom, each level closes off one more anomaly:

• Read uncommitted — the weakest; you can even see uncommitted changes. Rarely used.
• Read committed — you only see committed data, but a value can change between two reads. This is the default in PostgreSQL and many others.
• Repeatable read — any row you read stays the same value for the whole transaction, but new rows matching your queries can still appear.
• Serializable — the strongest; transactions behave as if they ran one after another in some order, with no anomalies at all. Safest, slowest.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT count(*) FROM seats WHERE flight = 'AZ42' AND booked = false;
-- decide, then book; serializable stops a phantom seat appearing mid-transaction
UPDATE seats SET booked = true WHERE id = 17;
COMMIT;

Rule of thumb: default to read committed; raise to serializable only for logic where an anomaly would cause real harm, such as inventory, seat booking, or balance checks.

Most modern databases implement isolation with MVCC (Multi-Version Concurrency Control): instead of locking a row so readers wait, the database keeps multiple versions of each row so a reader sees a consistent snapshot from when its transaction began while writers create newer versions — readers never block writers and writers never block readers.

Durability means that once the database has told you COMMIT succeeded, that change survives anything short of hardware destruction — a power cut, a process crash, an operating-system panic. When the machine comes back up, the committed data is still there.

The standard mechanism is the WAL (Write-Ahead Log): before the database modifies the actual data files, it first appends a record of the change to an append-only log on disk and flushes it. Because the log hits durable storage before the commit is acknowledged, a crash mid-write is recoverable — on restart the database replays the log to redo any committed change that hadn't yet reached the data files.

The ordering is the whole trick: the log is durable before the client hears "committed," so a promise the database makes is never one it can't keep after a crash. Everything else — writing the data files — can happen lazily because the log can always reconstruct it.

Not every system wants strong ACID guarantees. Many large-scale distributed stores instead offer BASE — Basically Available, Soft state, Eventual consistency — meaning they stay available and fast but only promise that replicas converge to the same value eventually, tolerating brief windows where different reads disagree. ACID favors correctness; BASE favors availability and scale. Choose ACID for money, inventory, and permissions; accept BASE-style eventual consistency for feeds, counters, and analytics where a moment of staleness is harmless.

• A transaction groups operations so the outside world sees only "before" or "after," never a half-finished state.
• Atomicity = all-or-nothing via rollback; a failed transaction reverses every change it started.
• Consistency = valid state to valid state; the database enforces the constraints you declare and aborts anything that would break them.
• Isolation = concurrent transactions don't corrupt each other; the named failures to prevent are dirty reads, non-repeatable reads, and phantom reads.
• Isolation levels (read uncommitted → read committed → repeatable read → serializable) are a dial trading concurrency for safety; use the weakest level that is still correct.
• MVCC gives isolation by keeping row versions so readers and writers don't block each other.
• Durability = committed survives a crash, guaranteed by writing to a write-ahead log before acknowledging the commit.
• ACID favors correctness; BASE/eventual consistency favors availability and scale — pick per feature.

• [ ] I can explain what a transaction is and why multi-step writes need one.
• [ ] I can name the four ACID properties and state each in one sentence.
• [ ] I can trace a bank transfer through BEGIN → operations → COMMIT/ROLLBACK.
• [ ] I can define dirty read, non-repeatable read, and phantom read.
• [ ] I can order the four isolation levels and say which anomaly each one adds protection against.
• [ ] I can explain how a write-ahead log delivers durability.
• [ ] I can decide when ACID is required and when BASE/eventual consistency is acceptable.