Gyaan

ACID Properties

beginner acid transactions atomicity consistency isolation durability

ACID is the set of four guarantees that make database transactions reliable. Every time we hear “transaction” in the database world, ACID is what makes it trustworthy.

Think of it like a contract. The database promises: “If you wrap your operations in a transaction, I guarantee these four things will hold true.”

The Classic Bank Transfer Example

Let’s say Manish wants to transfer Rs 1000 from Account A to Account B. This involves two operations:

  1. Subtract 1000 from Account A
  2. Add 1000 to Account B

What could go wrong? The system crashes after step 1 but before step 2. Now Rs 1000 has vanished into thin air. ACID prevents this.

A — Atomicity

All or nothing. Either the entire transaction succeeds, or the entire transaction is rolled back. No partial updates.

In our bank transfer: either BOTH the debit and credit happen, or NEITHER happens. If the system crashes mid-transaction, the database rolls back everything to the state before the transaction started.

Think of it like an “undo” button that triggers automatically on failure.

-- Atomicity in action
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
-- What if the system crashes RIGHT HERE?
-- With atomicity, the first UPDATE gets rolled back automatically.
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

COMMIT;
-- Only at COMMIT do both changes become permanent

C — Consistency

The database must move from one valid state to another valid state. All rules, constraints, and triggers must be satisfied before and after the transaction.

If we have a rule that says “balance cannot be negative,” and Manish tries to transfer Rs 5000 from an account with only Rs 1000 — the entire transaction is rejected. The database stays consistent.

-- Consistency: constraints are always enforced
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 'A';
-- If Account A only has 1000, this violates the CHECK constraint
-- The transaction FAILS, nothing changes
COMMIT;
-- ERROR: new row violates check constraint "positive_balance"

I — Isolation

Concurrent transactions don’t interfere with each other. Each transaction runs as if it’s the only one running on the database, even if thousands are running simultaneously.

Without isolation, two people transferring money at the same time could see each other’s half-finished work, leading to incorrect balances.

-- Without isolation (the problem):
-- Transaction 1: reads balance of A = 5000
-- Transaction 2: reads balance of A = 5000
-- Transaction 1: sets A = 5000 - 1000 = 4000
-- Transaction 2: sets A = 5000 - 2000 = 3000
-- Result: A = 3000, but it should be 2000! (one debit was "lost")

-- With isolation, Transaction 2 waits until Transaction 1 finishes
-- or the database detects the conflict and handles it properly

The level of isolation can be tuned (we’ll cover isolation levels in a separate note). More isolation = safer but slower.

D — Durability

Once a transaction is committed, it’s permanent. Even if the server crashes, loses power, or catches fire — the committed data survives.

The database achieves this by writing to a Write-Ahead Log (WAL) before confirming the commit. Even if the main data files are corrupted, the WAL can replay the changes.

In simple language: once the database says “COMMIT successful,” we can trust that the data is safely stored. No take-backsies.

All Four Together

A — Atomicity
All or nothing. No partial updates.
Crash mid-transaction? Everything rolls back.
C — Consistency
Valid state to valid state.
All constraints and rules are respected.
I — Isolation
Transactions don't see each other's work.
Each runs as if it's the only one.
D — Durability
Committed = permanent.
Survives crashes via Write-Ahead Log.

A Full Transaction Example

-- Transfer Rs 1000 from Account A to Account B
BEGIN;  -- Start the transaction (Atomicity boundary)

-- Check balance first (Consistency)
DO $$
BEGIN
    IF (SELECT balance FROM accounts WHERE id = 'A') < 1000 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
END $$;

-- Perform the transfer
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

COMMIT;  -- Make it permanent (Durability)
-- If anything failed above, ROLLBACK undoes everything (Atomicity)

Which Databases Support ACID?

  • Full ACID: PostgreSQL, MySQL (InnoDB), SQLite, Oracle, SQL Server
  • Document-level ACID: MongoDB (multi-document since v4.0)
  • Typically not ACID: Cassandra, DynamoDB, Redis (they follow BASE instead)
  • NewSQL (ACID + distributed): CockroachDB, TiDB, Google Spanner

Interview Tip

ACID is one of the most asked DBMS topics. Don’t just list the four words — explain each with the bank transfer example. Interviewers want to see that we understand why each property matters, not just what the acronym stands for.

References