ACID Properties in DBMS

Problem Explain the ACID properties of database transactions, with particular focus on Consistency and Isolation, and how transactions maintain them in real-world scenarios.

Be ready to discuss

• Atomicity: all-or-nothing execution - a transaction fully commits or fully rolls back, implemented via undo logs so a mid-flight failure leaves no partial writes.
• Consistency: every transaction takes the database from one valid state to another, preserving primary and foreign keys, uniqueness, check constraints, and triggers.
• Why Consistency is the odd one out: it is largely the application's and schema's responsibility, whereas the engine owns A, I, and D.
• Isolation: concurrent transactions must not see each other's uncommitted intermediate state.
• The four isolation levels - Read Uncommitted, Read Committed, Repeatable Read, Serializable - and the anomaly each admits: dirty reads, non-repeatable reads, phantom reads.
• How isolation is implemented: MVCC snapshots so readers don't block writers, plus row locks, gap locks, and predicate locks for conflicting writes.
• Durability: committed data survives a crash via write-ahead logging and fsync, with replication for durability beyond a single machine.
• A concurrent-update walkthrough: two transactions decrementing the same inventory row, why a read-modify-write races, and how SELECT ... FOR UPDATE or an atomic decrement fixes it.
• Rollback on failure: what happens on constraint violation, deadlock victim selection, or client disconnect mid-transaction.
• The trade-off: stronger isolation costs concurrency, which is why Read Committed and Repeatable Read are the common defaults rather than Serializable.