====== ACID Properties in Databases ======

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)

----

===== A — Atomicity (Tính nguyên tử) =====

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

<code sql>
-- 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;
</code>

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.

==== Without Atomicity ====

<code>
Alice loses $100 but Bob never receives it.
</code>

----

===== C — Consistency (Tính nhất quán) =====

"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.

<code sql>
-- 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;
</code>

==== Without Consistency ====

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

----

===== D — Durability (Tính bền vững) =====

"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.

<code sql>
BEGIN;

  UPDATE accounts
  SET balance = 500
  WHERE id = 1;

COMMIT;

-- ← server crashes HERE
</code>

After restart:

<code>
balance is still 500 ✓
</code>

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

==== Without Durability ====

<code>
You would never know whether a COMMIT actually succeeded after a crash.
</code>

----

===== I — Isolation (Tính cô lập) =====

"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.

<code sql>
-- 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
</code>

==== With Proper Isolation + FOR UPDATE ====

<code sql>
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;
</code>

After Txn A commits:

<code>
Txn B continues
→ sees qty = 0
→ purchase is rejected
</code>

Isolation is what all transaction anomalies are about:

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

----

===== How the 4 Properties Connect =====

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

==== Easy Way to Remember ====

^ Property ^ Question It Answers ^
| Atomicity | Did everything succeed, or was it all undone? |
| Consistency | Is the data still valid after the transaction? |
| Isolation | Can concurrent transactions see each other's work? |
| Durability | Is committed data safe even after a crash? |

----

===== ACID and Transaction Anomalies =====

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

<code>
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
</code>

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

----

===== Summary =====

^ Property ^ Purpose ^
| Atomicity | Prevent partial transactions |
| Consistency | Keep data valid |
| Isolation | Prevent concurrent conflicts |
| Durability | Prevent committed data loss |

<code>
Strong databases guarantee ACID.
Good developers understand when and how to control Isolation.
</code>