intermediateData IntegrityFree Guide

ACID Properties in Databases

Definition

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee database transactions are processed reliably even in the presence of errors, power failures, and concurrent access.

Introduction to ACID Properties in Databases

ACID properties are the bedrock guarantee of relational databases. They exist because real-world operations — bank transfers, order processing, inventory management — require absolute confidence that partial failures, system crashes, or concurrent users will not leave data in an incorrect state. Every major RDBMS (PostgreSQL, MySQL, SQL Server, Oracle) implements ACID guarantees.

Key Takeaways

  • Atomicity: implemented via rollback logs — on failure, the database replays the log to undo partial changes
  • Consistency: implemented via constraints (CHECK, NOT NULL, FOREIGN KEY), triggers, and cascades
  • Isolation: implemented via locking (pessimistic) or MVCC — Multi-Version Concurrency Control (optimistic)
  • Durability: implemented via Write-Ahead Logging (WAL) — changes are logged to disk before the commit succeeds
  • MVCC allows readers and writers to not block each other — PostgreSQL and InnoDB use MVCC
  • BASE (Basically Available, Soft state, Eventually consistent) is the NoSQL alternative to ACID

Real-World Examples & SQL Schema

1

ACID in Action — E-commerce Order

BEGIN;

-- Atomicity: all or nothing
INSERT INTO orders (customer_id, total)
VALUES (42, 299.99);

-- Consistency: stock cannot go negative (CHECK constraint)
UPDATE products
SET stock = stock - 1
WHERE id = 101 AND stock > 0;

-- Isolation: other sessions don't see this until COMMIT
-- (MVCC: readers see the old stock value)

CHARGE_PAYMENT(42, 299.99); -- application call

COMMIT;
-- Durability: WAL entry flushed to disk — data survives crash

A complete order transaction showing all four ACID properties in action.

Run code in Playground

Primary Use Cases

Financial systems: every transaction must balance (debit = credit)

Healthcare: patient records must be consistent and never partially written

E-commerce: orders, inventory, and payments must be atomically coordinated

Booking systems: seat/room availability must not be double-booked

Frequently Asked Questions

What does ACID stand for in databases?
ACID stands for: A — Atomicity (a transaction is treated as a single indivisible unit; either all operations succeed and commit, or all are rolled back — no partial commits). C — Consistency (a transaction brings the database from one valid state to another, respecting all defined constraints, cascades, and triggers). I — Isolation (concurrent transactions execute as if they were sequential — intermediate states are not visible to other transactions). D — Durability (once a transaction commits, the changes are permanent even if the system crashes immediately after — guaranteed by write-ahead logging).
What is MVCC (Multi-Version Concurrency Control)?
MVCC is the mechanism PostgreSQL, MySQL InnoDB, and Oracle use to implement Isolation without locking readers. Each transaction sees a consistent snapshot of the database as it existed at transaction start. When a row is updated, the database creates a new version of the row instead of modifying it in place. Readers see the old version; the new version becomes visible after commit. This allows readers and writers to not block each other.
What is the difference between ACID and BASE?
ACID (relational databases): prioritizes consistency and reliability. Transactions are fully consistent immediately. BASE (NoSQL databases): prioritizes availability and partition tolerance. Basically Available — the system is always available but may return stale data. Soft state — data may change over time even without input (replication catching up). Eventually consistent — the system will become consistent eventually, but not immediately.

Related Concepts

TransactionsNormalizationReplicationIndexing