ACID Properties and Anomalies
Recap atomicity, consistency, isolation, durability, and the four classical anomalies: dirty read, non-repeatable read, phantom, lost update.
ACID
A transaction is reliable when it is:
- Atomic — all or nothing
- Consistent — moves the DB from one valid state to another
- Isolated — concurrent transactions don't step on each other
- Durable — once committed, data survives crashes
A Transaction in SQL
Wrap operations in BEGIN ... COMMIT:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If anything fails, ROLLBACK leaves both rows unchanged.