ACID Fundamentals:
- Atomicity: All operations in a transaction succeed completely, or the entire transaction is rolled back with zero partial writes.
- Consistency: Transactions transition the database from one valid state to another, strictly enforcing constraints, keys, and schema rules.
- Isolation: Concurrent transactions execute without interfering with one another, preventing race conditions.
- Durability: Once a transaction commits, its modifications are permanently recorded (via Write-Ahead Logging to disk), surviving power outages or server crashes.
ANSI SQL Isolation Levels (Weakest to Strongest):
1. Read Uncommitted: Permits dirty reads (reading uncommitted mutations from another transaction).
2. Read Committed: Guarantees reads only see committed data; vulnerable to non-repeatable reads (data changes between reads in the same transaction).
3. Repeatable Read: Guarantees repeated reads within a transaction return identical snapshots; vulnerable to phantom reads in older database engines.
4. Serializable: Strongest level. Guarantees transactions execute as if run serially one after another, eliminating all anomalies using multi-version concurrency control (MVCC) or range locks.