ACID is a set of 4 properties that every database transaction must guarantee to ensure data stays correct and reliable — even when things go wrong (crashes, errors, concurrent users).

The name is an acronym:

• A — Atomicity (Tính nguyên tử)
• C — Consistency (Tính nhất quán)
• I — Isolation (Tính cô lập)
• D — Durability (Tính bền vững)

“All or nothing” — either every operation in a transaction succeeds, or none of them do. There is no partial result.

-- Transfer $100 from Alice to Bob
BEGIN;
 
  UPDATE accounts
  SET balance = balance - 100
  WHERE id = 1;  -- debit Alice
 
  UPDATE accounts
  SET balance = balance + 100
  WHERE id = 2;  -- credit Bob
 
COMMIT;

If the server crashes after the first `UPDATE` but before the second — the database rolls back the first one automatically.

Alice's money is not lost in limbo.

Alice loses $100 but Bob never receives it.

“Data must always be valid” — a transaction can only bring the database from one valid state to another.

All rules, constraints, and invariants must hold before and after.

-- Constraint: balance cannot go below 0
ALTER TABLE accounts
ADD CONSTRAINT chk_balance
CHECK (balance >= 0);
 
BEGIN;
 
  UPDATE accounts
  SET balance = balance - 9999
  WHERE id = 1;
 
  -- Alice only has $100
  -- balance would become -9899
  -- Database rejects this → constraint violated → ROLLBACK
 
COMMIT;

• Withdraw more money than exists
• Foreign keys point to nothing
• Incorrect totals and invalid data

“Committed = permanent” — once a transaction is committed, the data is saved permanently.

Even a power failure, server crash, or OS crash right after `COMMIT` cannot undo it.

BEGIN;
 
  UPDATE accounts
  SET balance = 500
  WHERE id = 1;
 
COMMIT;
 
-- ← server crashes HERE

After restart:

balance is still 500 ✓

The database writes changes to disk (WAL / redo log) before confirming `COMMIT`.

You would never know whether a COMMIT actually succeeded after a crash.

“Transactions don't interfere with each other” — concurrent transactions behave as if they run one at a time.

One transaction's in-progress changes are invisible to others until committed.

-- Two users buying the last item simultaneously
 
Txn A:
SELECT qty FROM products WHERE id = 1;
-- → 1
 
Txn B:
SELECT qty FROM products WHERE id = 1;
-- → 1
 
Txn A:
UPDATE products SET qty = 0 WHERE id = 1;
COMMIT;
 
Txn B:
UPDATE products SET qty = 0 WHERE id = 1;
COMMIT;
 
-- Without isolation:
-- both transactions succeed → oversell

BEGIN;
 
SELECT qty
FROM products
WHERE id = 1
FOR UPDATE;
 
-- Txn B is blocked here until A commits
 
UPDATE products
SET qty = qty - 1
WHERE id = 1;
 
COMMIT;

After Txn A commits:

Txn B continues
→ sees qty = 0
→ purchase is rejected

Isolation is what all transaction anomalies are about:

• Dirty read
• Non-repeatable read
• Phantom read
• Lost update
• Write skew

ATOMICITY   — protects against partial failures
CONSISTENCY — protects against invalid data states
ISOLATION   — protects against concurrent interference
DURABILITY  — protects against data loss after commit

The anomalies and isolation levels are all about the I in ACID:

ACID
 └── Isolation
      ├── Dirty read
      │     → READ COMMITTED fixes it
      │
      ├── Non-repeatable read
      │     → REPEATABLE READ / FOR SHARE
      │
      ├── Phantom read
      │     → SERIALIZABLE / FOR SHARE
      │
      ├── Lost update
      │     → FOR UPDATE / Atomic update
      │
      └── Write skew
            → FOR UPDATE / SERIALIZABLE

Atomicity, Consistency, and Durability are mostly handled automatically by the database engine:

• Transactions
• Constraints
• WAL logs / redo logs
• Recovery systems

Isolation is the property where developers usually need to make decisions about:

• Isolation levels
• Locking strategies
• `FOR UPDATE`
• `FOR SHARE`
• Optimistic locking
• Serializable transactions

Strong databases guarantee ACID.
Good developers understand when and how to control Isolation.