ACID Properties and MySQL Isolation

Problem Explain the ACID properties of a transaction, and describe specifically how MySQL/InnoDB satisfies the Isolation property.

Be ready to discuss

• Atomicity: a transaction either fully commits or fully rolls back, with no partial application; InnoDB implements rollback using undo logs.
• Consistency: every transaction moves the database from one valid state to another, preserving constraints, foreign keys, and triggers.
• Isolation: concurrent transactions must not observe each other's intermediate state; name the four SQL isolation levels (Read Uncommitted, Read Committed, Repeatable Read - InnoDB's default, Serializable).
• The anomalies each level permits: dirty reads, non-repeatable reads, phantom reads, and where InnoDB's gap/next-key locks prevent phantoms even at Repeatable Read.
• MVCC (Multi-Version Concurrency Control): undo logs give each transaction a consistent read snapshot so readers never block writers, and row-level locking serialises conflicting writes.
• Durability: committed changes survive a crash via the redo log / write-ahead logging and the doublewrite buffer, with fsync behaviour controlled by innodb_flush_log_at_trx_commit.
• Trade-off framing: stronger isolation costs concurrency and throughput, which is why Serializable is rarely the default.