ACID Properties in DBMS: Atomicity, Consistency, Isolation & Durability

PerfectNotes TeamUpdated May 2026

Key Takeaways

ACID — stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions.
Atomicity — a transaction either commits completely or rolls back entirely; no partial state is ever persisted.
Consistency — every committed transaction moves the database from one valid state to another, respecting all schema constraints.
Isolation — concurrent transactions execute without interference; four ANSI levels trade safety for throughput.
Durability — committed data survives crashes; Write-Ahead Logging (WAL) is the standard implementation technique.

Introduction to Database Transactions

A database transactionis the fundamental unit of work in a relational database. Every time you transfer money, place an order, or update a healthcare record, one or more SQL statements are grouped into a transaction. For decades, engineers asked: "What guarantees does the database provide when things go wrong — hardware failures, network partitions, concurrent users, or application bugs?"

The answer arrived in 1983 when Theo Härder and Andreas Reuter coined the acronym ACIDin their landmark paper "Principles of Transaction-Oriented Database Recovery." ACID defines four non-negotiable properties for any system claiming to be a reliable transaction processor — and they remain the gold standard in 2026.

What is a Database Transaction?

A transaction is a sequence of one or more SQL operations (INSERT, UPDATE, DELETE, SELECT) treated as a single logical unit of work. A transaction has a clear beginning (BEGIN) and is either permanently applied (COMMIT) or fully undone (ROLLBACK). No partial result is ever visible to the outside world.

Transactions map directly to business operations. A bank transfer is not "two UPDATEs" — it is one transaction that either moves the money or does not.

The "Bank Transfer" Analogy

Imagine transferring $500 from a Savings account to a Checking account. This requires two separate database writes. Consider what happens if the server crashes between Step 1 (debit Savings) and Step 2 (credit Checking):

Without transactions — $500 vanishes. The debit happened; the credit did not.
With ACID transactions — the entire operation rolls back on failure. The database is restored to its exact pre-transfer state.

ACID Atomicity flowchart showing bank transfer: START → Step 1 Subtract $500 → decision diamond → COMMIT on success or ROLLBACK on failure — Figure 1: Atomicity in action — the bank transfer either commits both steps or rolls back to the original state.

This demonstrates the simplest form of ACID. The principle scales identically to order systems with 50 steps or distributed microservices spanning three continents.

Why Do We Need ACID?

Without ACID guarantees, databases become unreliable in three dangerous scenarios:

System Failures — Power outages or OS panics interrupt a transaction mid-flight. ACID (through WAL and rollback) restores consistent state on restart.
Concurrent Access — Multiple users reading and writing simultaneously create race conditions. Without isolation, one transaction can read data that another has modified but not yet committed.
Application Bugs — Code may raise an exception partway through a series of updates. ACID rollback discards all partial changes automatically, preventing corruption.

Core Concepts: Breaking Down A-C-I-D

Atomicity — The "All or Nothing" Rule

Atomicity guarantees that a transaction is treated as a single, indivisible unit. Either all its operations succeed and are committed, or all are undone and the database remains unchanged.

The database engine implements atomicity through an undo log (rollback segment). Every modification records its before-image in the undo log. If the transaction aborts, the engine reads the undo log in reverse and restores every original value.

Undo Log / Rollback Segment — stores before-images of every modified row for potential undo operations.
Savepoints — allow partial rollback to a named checkpoint within a transaction for fine-grained control.
Deferred Constraints — constraint checks postponed to commit time, keeping intermediate states valid.

Consistency — Following the Rules

Consistencyguarantees that a transaction moves the database from one valid state to another valid state. "Valid" is defined by schema constraints: primary keys, foreign keys, unique indexes, check constraints, and application-level business rules enforced as triggers.

Unlike the other three properties (which are purely the database engine's responsibility), consistency is a shared responsibility between the database and the developer. The database enforces schema constraints; the developer encodes business rules correctly. For example, a CHECK (balance >= 0) constraint ensures no transaction can create a negative balance — the violation triggers automatic rollback.

Isolation — Working in Secret

Isolation guarantees that concurrently executing transactions produce the same result as if they had run serially. Each transaction appears to have exclusive access to the database while it runs.

Durability — Surviving the Crash

Durability guarantees that once a transaction is committed, it remains committed — even through power failures, OS crashes, or disk controller resets. The commit is permanent.

The primary implementation mechanism is Write-Ahead Logging (WAL): before any data page is modified on disk, the change is first written to a sequential log file on durable storage. On restart after a crash, the engine replays the WAL from the last checkpoint to restore all committed transactions.

ACID vs. BASE: Choosing a Consistency Model

(Use this table for "difference between ACID and BASE" exam questions)

Feature	ACID (Relational DBs)	BASE (NoSQL DBs)
Consistency	Strong — immediate after commit	Eventual — converges over time
Availability	May block under contention	Always available (AP model)
Scalability	Vertical scaling primary	Horizontal scaling primary
Use Case	Banking, ERP, healthcare records	Social feeds, caching, analytics
Examples	PostgreSQL, MySQL, Oracle, SQL Server	Cassandra, DynamoDB, CouchDB

Advanced Engineering Concepts

Concurrency Control and Conflict Serializability

A schedule is conflict-serializable if it is equivalent to some serial execution of the same transactions. Databases use two families of concurrency control:

Lock-Based (Pessimistic) — Transactions acquire shared (read) and exclusive (write) locks before accessing data. Two-Phase Locking (2PL) guarantees conflict serializability. Used in Oracle, SQL Server, and legacy systems.
MVCC (Optimistic) — Readers never block writers and writers never block readers. Each transaction sees a consistent snapshot at its start time. Used in PostgreSQL, MySQL InnoDB, and Oracle.

Isolation Levels and Read Phenomena

The ANSI SQL-92 standard defines four isolation levels, each preventing a progressively more severe class of concurrency anomaly:

Isolation levels matrix table showing Read Uncommitted, Read Committed, Repeatable Read, and Serializable rows against Dirty Read, Non-Repeatable Read, and Phantom Read columns with green checkmarks for prevented and red X for possible — Figure 2: ANSI SQL Isolation Levels vs. Read Phenomena. Higher levels prevent more anomalies at the cost of throughput.

PostgreSQL defaults to Read Committed, MySQL InnoDB defaults to Repeatable Read, and financial systems typically mandate Serializable for critical paths. Choose the lowest level that satisfies your consistency requirements to maximize concurrency.

Write-Ahead Logging (WAL)

WAL transforms the abstract Durability property into a concrete, crash-safe guarantee. The core rule: log the change before applying it to data pages.

Figure 3: WAL architecture — sequential log writes enable fast durability; lazy page flushes keep throughput high.

Performance insight:Writing to a WAL is fast because it is an append-only sequential write. Writing modified data pages is expensive because of random I/O. WAL decouples "making the commit durable" (sequential) from "updating data pages" (lazy background), achieving both Durability and high throughput simultaneously.

On restart after a crash, the engine performs REDO recovery (replay all committed WAL records forward) then UNDO recovery (roll back transactions whose commit record was absent), restoring a perfectly consistent state.

Distributed Transactions and the Two-Phase Commit (2PC)

Modern architectures split data across multiple databases. A single "place order" operation might write to Orders, Inventory, and Payment services on separate database instances. The Two-Phase Commit (2PC) protocol solves this by electing a Coordinator that orchestrates the commit:

Two-Phase Commit sequence diagram with three swimlane columns: Inventory DB, Coordinator, Payment DB showing Phase 1 PREPARE and VOTE YES, then Phase 2 COMMIT and ACK — Figure 4: Two-Phase Commit — all participating nodes must vote YES before the Coordinator issues the global COMMIT.

Phase 1 (Prepare) — The Coordinator sends PREPARE to all participants. Each node logs the transaction to its WAL, locks the rows, and replies VOTE: YES or VOTE: NO.
Phase 2 (Commit/Abort) — If all participants voted YES, the Coordinator broadcasts COMMIT. If any voted NO or timed out, it broadcasts ROLLBACK to all participants.

Limitations: The Coordinator is a single point of failure and participants can get stuck in a "prepared but not committed" limbo if the Coordinator crashes after Phase 1. Modern systems replace 2PC with the Saga pattern (compensating transactions) or use distributed databases like Google Spanner and CockroachDB that implement 2PC with fault-tolerant Paxos/Raft consensus internally.

Real-World Case Study: Knight Capital Trading Glitch (August 2012)

On August 1, 2012, Knight Capital Group — one of the largest US equity market makers — lost $440 million in 45 minutes due to a software deployment error that violated ACID transaction properties in their high-frequency trading system. The incident stands as one of the most dramatic database/software failures in Wall Street history.

Aspect	Details
The Incident	Knight Capital was deploying new trading software across 8 production servers. A technician failed to deploy the new code to one server — leaving it running an old, decommissioned algorithm called "Power Peg." When the NYSE opened, the 7 updated servers executed correctly while the 8th executed the defunct logic. For 45 minutes, the system executed 4 million unintended trades across 154 stocks — buying at high prices and immediately selling at lower prices in a catastrophic loop.
Root Cause	Multiple ACID violations compounded: Atomicity failed — the deployment was not an all-or-nothing transaction (partial deployment left system in inconsistent state). Consistency failed — the 8-server cluster operated in a split state, executing two contradictory strategies simultaneously. Isolation failed — the strategies' transactions interfered, creating a buy-high/sell-low feedback loop. No circuit breaker existed to halt the runaway system.
The Impact	In 45 minutes, Knight Capital accumulated a $7 billion long position in equities and an equivalent short position. The NYSE halted trading in 6 affected stocks. Knight's internal risk team noticed anomalous volumes but could not identify the source server or halt the system for nearly an hour. Knight had to be bailed out by a consortium of investors and was subsequently acquired by Getco for a fraction of its former value.
Financial Cost	$440 million lost in 45 minutes — wiping out Knight Capital's entire four years of earnings. The stock fell 75% in two days. Knight was forced to accept a $400M emergency capital injection from Getco, Blackstone, and others. Within months, Knight Capital was acquired by Getco LLC, effectively ending its 17-year existence as an independent firm.
Key Lesson	Deployment atomicity is as critical as database atomicity. The Knight Capital disaster demonstrates that ACID properties must extend beyond individual database transactions to encompass entire system deployments. A deployment leaving servers in inconsistent states is a transaction that violated Atomicity. Modern best practices mandate: blue-green deployments (switch all traffic at once), automated rollback on anomaly detection, circuit breakers on financial systems, and per-server deployment verification — all direct lessons from Knight Capital's $440M failure.

Key Statistics & Industry Data (2026)

Cost of Inconsistency — Financial institutions report that a single significant database consistency failure can cost upwards of $10 million in recovery, audits, and regulatory fines. (Source: IBM Cost of a Data Breach Report, 2026)
Performance Trade-offs — Enforcing strict Serializable isolation levels can reduce database transaction throughput by up to 60% compared to Read Committed, highlighting the cost of perfect ACID compliance. (Source: Gartner, 2026)
BASE Adoption — Despite the power of NoSQL, over 85% of enterprise e-commerce platforms still rely on strictly ACID-compliant relational databases for their core checkout and payment processing systems. (Source: Statista, 2026)

Real-World Applications of ACID Properties

Banking & Financial Transactions
Every wire transfer, card payment, and loan disbursement relies on ACID to guarantee funds are never created or destroyed by a partial failure.
E-Commerce Order Processing
Placing an order atomically deducts inventory, records the order row, and charges payment — all succeed or all roll back.
Healthcare Record Systems
Patient prescription and billing updates must remain consistent; ACID prevents a charge being recorded without a matching medication event.
Airline Reservation Systems
Seat locking, payment capture, and boarding-pass generation are wrapped in one ACID transaction so double-booking is impossible.
Stock Trading Platforms
Buy and sell orders update positions, cash balances, and audit logs together — isolation levels prevent phantom orders from distorting portfolios.
Enterprise Resource Planning (ERP)
Manufacturing, payroll, and procurement modules share one database; ACID keeps cross-module data consistent even during bulk imports.

Advantages of ACID Properties

Data Integrity Guaranteed — atomicity and consistency make it impossible for a committed transaction to leave data in a corrupt partial state.
Crash Recovery Built-In — Write-Ahead Logging enables full recovery from power failures by replaying the durable log, no manual repair scripts needed.
Concurrent User Safety — isolation levels allow hundreds of concurrent transactions without interference, preventing dirty reads and phantom rows.
Audit-Ready Commit Trail — every committed transaction creates a permanent, ordered WAL record for complete chronological compliance auditing.
Predictable Application Logic — developers write success-or-failure logic; ACID handles rollback mechanics, radically simplifying error handling code.
Industry-Standard Compatibility — all major RDBMS engines implement ANSI SQL ACID, ensuring portability between PostgreSQL, MySQL, Oracle, and SQL Server.

Disadvantages and Trade-offs

Performance Overhead — maintaining isolation with row-level or table-level locks adds CPU overhead and latency, especially under high-concurrency OLTP workloads.
WAL Storage Requirement — Write-Ahead Logs must be stored on durable media and periodically checkpointed, consuming additional disk I/O and storage capacity.
Distributed Transaction Complexity — ACID across microservices requires Two-Phase Commit (2PC) or saga patterns, which are significantly harder to implement than single-node transactions.
Horizontal Scalability Challenges — strict ACID guarantees conflict with the BASE model; achieving full ACID at massive horizontal scale requires difficult trade-offs.
Deadlock Risk Under Contention — concurrent transactions acquiring locks in different orders can deadlock; databases detect cycles by aborting one transaction, causing application retries.
Not Suitable for All Workloads — analytics, time-series, and event-streaming workloads rarely need per-row ACID guarantees; enforcing them adds unnecessary overhead.

Quick Reference Cheat Sheet

Feature	Mechanism	Without It
Atomicity	Undo log / ROLLBACK	Partial writes corrupt data
Consistency	Schema constraints & triggers	Invalid data states persist
Isolation	Locks / MVCC snapshots	Dirty reads & phantom rows
Durability	Write-Ahead Logging (WAL)	Committed data lost on crash
Distributed ACID	Two-Phase Commit (2PC)	Split-brain inconsistency

Frequently Asked Questions (FAQ)

What does ACID stand for in database systems?

ACID stands for Atomicity, Consistency, Isolation, and Durability. Together, these four properties define what a reliable database transaction must guarantee. Atomicity ensures all-or-nothing execution; Consistency ensures only valid states are persisted; Isolation prevents concurrent transactions from interfering; and Durability ensures committed data survives failures.

What is the difference between Atomicity and Durability?

Atomicity governs what happens during a transaction — all operations succeed together or all are rolled back, leaving no partial changes. Durability governs what happens after a transaction commits — the changes must survive any subsequent failure such as a crash, power cut, or OS restart. Write-Ahead Logging (WAL) is the main mechanism that provides Durability.

Why does Isolation matter and what are the different levels?

Without isolation, concurrent transactions can corrupt each other's reads. ANSI SQL defines four levels from weakest to strongest: Read Uncommitted (allows dirty reads), Read Committed (prevents dirty reads), Repeatable Read (prevents non-repeatable reads), and Serializable (prevents phantom reads). Higher levels provide more protection at the cost of concurrency throughput.

Do NoSQL databases support ACID?

Historically NoSQL databases favored BASE (Basically Available, Soft state, Eventual consistency) over ACID for scalability. However, many modern NoSQL systems now offer partial or full ACID support — MongoDB introduced multi-document ACID transactions in v4.0, Google Spanner provides global ACID, and Amazon DynamoDB offers ACID transactions on selected operations.

What is Write-Ahead Logging (WAL) and why is it critical?

WAL is the crash-recovery technique where every change is written to a sequential log file before being applied to actual data pages. If a crash occurs mid-transaction, the database replays the WAL from the last checkpoint to restore consistency. This approach dramatically improves write throughput and is the primary mechanism for the Durability guarantee.

What is the difference between Read Committed and Repeatable Read?

Read Committed ensures a transaction only reads data that has been fully committed (preventing dirty reads). However, if the same query is run twice within the transaction, the results might differ if another transaction committed changes in between (a non-repeatable read). Repeatable Read guarantees that if you read a row once, you will see the exact same data if you read it again within the same transaction.

How does a database resolve a deadlock?

A deadlock occurs when two or more transactions hold locks and are waiting for each other to release them, creating an infinite cycle. The database engine employs a Deadlock Detector that periodically checks for cycle graphs in the lock table. When a cycle is found, the engine unilaterally aborts (rolls back) one of the transactions (often the one that has done the least amount of work) to break the cycle, throwing a deadlock error to the application, which must then retry the transaction.

Test Your Knowledge

Ready to prove your skills? Take our rigorous multiple-choice quiz designed to test your understanding of this topic and prepare you for interviews.

Start Quiz