ACID Properties

A transaction is a sequence of one or more database operations (reads and writes) that the application treats as a single logical unit — for example, "transfer $500 from Alice to Bob." The ACID properties are the guarantees the DBMS makes about how transactions behave, individually and in combination with each other and with failures.

Atomicity means a transaction is indivisible: either every one of its writes is applied to the database, or none are. There is no state in which the debit from Alice happened but the credit to Bob did not. Atomicity is what makes it safe to write multi-step logic without manually checking, after every crash, which steps completed.

Consistency means a transaction, run alone starting from a state that satisfies the database's integrity constraints (declared constraints like foreign keys and check constraints, plus any invariants the application relies on, such as "total money in the system is constant"), leaves the database in a state that again satisfies those constraints. Consistency is a joint responsibility: the DBMS enforces declared constraints automatically, but application logic must be written correctly for constraints that are not directly expressible in schema declarations, such as "the sum of all balances equals the sum of all deposits ever made."

Isolation means that even though many transactions may execute concurrently for performance, each transaction should behave as though it were the only one running. Formally, a concurrent execution (a schedule) should have the same effect as some serial (one-at-a-time) execution of the same transactions — this property is called <a class="wiki-link" href="/wiki/cs-db-serializability-schedules" data-track="wiki_article.body.wiki_link">serializability</a>. Isolation is what prevents one transaction's half-finished work from ever being visible to, or corrupted by, another.

Durability means that once a transaction commits — the DBMS has reported success to the application — its effects are permanent. Even a crash one instruction later, or a disk failure the next day, must not undo a committed transaction (subject to the assumption that the storage medium itself isn't destroyed). These four properties are logically independent axes, but the mechanisms that implement them overlap: the same Write-Ahead Logging infrastructure used for atomicity (undoing incomplete transactions) is also used for durability (redoing committed ones after a crash), while isolation is a separate concern handled by concurrency control.

Atomicity and durability: logging and recovery

The standard technique is write-ahead logging (WAL): before any change is made to the actual database, a log record describing the change is written to stable storage. The log records enough information to redo the change (if the transaction commits) or undo it (if the transaction aborts or the system crashes before commit). The essential rule, called the WAL rule, is: 1. Before a data page is written to disk, all log records describing changes to that page must already be on stable storage (write log first, ahead of data). 2. Before a transaction is considered committed, all its log records — including a special commit record — must be on stable storage. This ordering is what makes recovery possible after a crash: the log is a durable, ordered history of every intended change, so the recovery process can replay it to reconstruct exactly which transactions finished and which did not. ``` procedure COMMIT(T): write log record flush log to stable storage // durability boundary mark T as committed return success to caller procedure ABORT(T): for each log record of T, in reverse order: restore X to old_value // undo write log record flush log to stable storage procedure RECOVER(): // runs after a crash, on restart // Phase 1: analyze the log to find which transactions committed committed := {}, active := {} for each log record in the log, in order: if record is : committed.add(T); active.remove(T) if record is : active.remove(T) if record is : active.add(T) // Phase 2: redo all writes of committed transactions for each log record in the log, in order: if T in committed: write new to X // Phase 3: undo all writes of transactions that never committed for each log record in the log, in reverse order: if T in active: // never committed, never explicitly aborted write old to X ``` This "redo committed, undo uncommitted" scheme is what real systems (with further refinements such as checkpoints and the ARIES algorithm) implement; the full mechanics are the subject of Write-Ahead Logging. The key point for ACID is that atomicity (undo) and durability (redo) are two sides of the same log.

Isolation: concurrency control

Isolation is enforced by a concurrency-control protocol that regulates the interleaving of operations from different transactions. The two dominant families are:

• Lock-based protocols, such as two-phase locking, in which a transaction must acquire a shared (read) or exclusive (write) lock on a data item before accessing it, and — in the "two-phase" discipline — releases no lock until it has acquired all the locks it will ever need.
• Timestamp-ordering / multiversion protocols, in which transactions are ordered by timestamps and the system either enforces that ordering directly on reads/writes, or keeps multiple versions of each data item so readers never block writers (multiversion concurrency control, used by PostgreSQL, Oracle, and others).

``` procedure TWO_PHASE_LOCKING(T): growing_phase := true locks_held := {} procedure READ(X): if growing_phase: acquire shared-lock(X); locks_held.add(X) return value of X procedure WRITE(X, v): if growing_phase: acquire exclusive-lock(X); locks_held.add(X) X := v procedure COMMIT_OR_ABORT(): growing_phase := false // shrinking phase begins release all locks in locks_held ``` The isolation guarantee delivered is precisely: any schedule produced by a protocol satisfying two-phase locking is guaranteed conflict-serializable — equivalent in effect to some serial order of the transactions. This is the formal notion developed fully under Serializability and Schedules; here it suffices to know that isolation is achieved by restricting which interleavings are allowed, not by literally running transactions one at a time (which would be correct but far too slow). Real systems also expose weaker isolation levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — trading some isolation guarantees for concurrency and performance; "full" isolation (serializability) is the strongest and most expensive.

Consistency

Consistency is enforced partly automatically and partly by discipline:

• The DBMS checks declared constraints (primary keys, foreign keys, `CHECK` constraints, `NOT NULL`) at the end of each transaction (or statement) and aborts the transaction if any is violated.
• The application programmer is responsible for ensuring that, in isolation, each transaction's logic preserves invariants not captured by declared constraints.

Because atomicity and isolation are what make "checking constraints only at transaction boundaries" meaningful (intermediate, possibly-inconsistent states are never exposed to other transactions and never persist if the transaction aborts), consistency is often described as a consequence of the other three properties working correctly, combined with correct application logic.

Atomicity / durability cost. Every write to a data item requires one additional sequential write to the log before (or, in practice, batched with) the corresponding data write. Because the log is append-only, log writes are sequential I/O, which is cheap relative to random-access data-page writes; typical systems amortize this cost by flushing the log in batches (group commit). The space overhead is one log record per write, each of size proportional to the changed data (or, with logical logging, proportional to the operation), so total log size is $O(\text{total writes})$ across the system's history until old records are discarded at a checkpoint.

Correctness of recovery. The WAL rule is the invariant that makes the redo/undo recovery procedure correct:

• Claim: after `RECOVER()` completes, the database state is exactly the state that would result from applying every committed transaction's writes, in log order, to the pre-transaction state, with no trace of any transaction that never committed.
• Argument: by the WAL rule, every log record for a transaction is durable before that transaction's commit record is durable, and every commit record is durable before the transaction is reported as committed. Hence, at any crash point, the log contains a prefix of true history that includes complete information for every transaction the outside world has been told committed. The analysis phase correctly partitions transactions into "committed" (has a commit record) and "never committed" (does not), because a transaction cannot be told it committed without its commit record being durable. Redo re-establishes every committed write (idempotent, since redoing an already-applied write is harmless if writes are applied in log order), and undo removes every write made by a transaction with no commit record, restoring the pre-transaction value. No transaction's effects are partially present in the final state, which is exactly atomicity plus durability.

Isolation cost and correctness. Two-phase locking's correctness argument: suppose two transactions $T_i$ and $T_j$ both hold their final lock release points $t_i$ and $t_j$ respectively (end of growing phase). If $T_i$'s lock point precedes $T_j$'s, one can show by induction on conflicting operations that every conflicting pair of operations between $T_i$ and $T_j$ occurs in the order "$T_i$ before $T_j$," which is precisely the condition for the schedule to be conflict-equivalent to the serial schedule $T_i, T_j$ — this is the formal content covered under Serializability and Schedules. The cost is concurrency lost to blocking (a transaction may wait for locks) and the possibility of deadlock, requiring detection or prevention, typically $O(1)$ amortized overhead per lock request plus periodic $O(V+E)$ deadlock-detection graph traversals in systems that use detection.

Honest tradeoffs. Full serializability and full durability are the strongest and most expensive settings; virtually every production system offers weaker, faster alternatives — weaker isolation levels (Read Committed is the common default), asynchronous replication instead of synchronous durability, and eventual consistency in distributed systems (see the CAP-theorem tradeoff space) — because the ACID guarantees, while easy to reason about, are not free.

Trace the bank transfer $T_1$: "transfer $500 from Alice (balance 1000) to Bob (balance 300)," alongside a concurrent transaction $T_2$: "read Alice's balance for a statement," with a crash inserted mid-way.

Now replay the same scenario but let $T_1$ reach commit before the crash:

The two traces show atomicity ("all $T_1$'s writes or none") and durability ("committed effects survive a crash") as directly enforced by the log, and isolation (step 4) as enforced by locking preventing $T_2$ from ever observing the half-finished value $A = 500$.

Every mainstream relational database — PostgreSQL, MySQL/InnoDB, Oracle, Microsoft SQL Server, SQLite — implements ACID transactions as its core correctness contract, using write-ahead logging (often the ARIES-style algorithm) for atomicity/durability and locking or multiversion concurrency control for isolation. Financial systems (core banking, payment processing, stock exchanges) depend directly on atomicity and durability to guarantee money is never created or destroyed and never lost after a confirmed transfer. Airline reservation systems and inventory management rely on isolation to prevent two customers from double-booking the same seat or the same last unit of stock. Modern "NewSQL" distributed databases (Google Spanner, CockroachDB, YugabyteDB) extend ACID guarantees across machines, using distributed logging and consensus protocols to preserve the same four properties at global scale, while many NoSQL systems deliberately relax one or more ACID guarantees (often isolation or the scope of atomicity) in exchange for higher availability and throughput — a tradeoff made explicit in the discussion of distributed consistency models and the CAP theorem. Understanding ACID precisely is also the prerequisite for understanding why Write-Ahead Logging and Serializability and Schedules are built the way they are: both are engineering answers to the guarantees defined here.

Ask the tutor: How does the ARIES recovery algorithm extend the simple redo/undo scheme shown here to handle checkpoints and partial rollbacks efficiently?