Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
What are the ACID properties in relational databases?
In Simple Words: ACID guarantees that database transactions are executed reliably:
- A - Atomicity ("All or Nothing"): If one step of a transfer fails, the entire transaction rolls back.
- C - Consistency: Data must always obey schema constraints (Foreign Keys, Unique checks).
- I - Isolation: Concurrent transactions don't interfere with each other.
- D - Durability: Once committed, data is written to disk (WAL) and won't be lost even on power failure.
sql
-- Atomicity demonstration: If balance check fails, ROLLBACK occurs
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
-- If 0 rows updated, abort transaction:
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;