ACID Properties
BeginnerAtomicity (all-or-nothing), Consistency (constraints maintained), Isolation (concurrent transactions are invisible to each other), Durability (committed data persists) are guaranteed by InnoDB.
Overview
ACID is the set of four properties that guarantee database transactions are processed reliably. **Atomicity**: every statement in a transaction either fully succeeds or the entire transaction is rolled back — no partial updates. **Consistency**: a transaction brings the database from one valid state to another, respecting all constraints (FK, UNIQUE, CHECK). **Isolation**: concurrent transactions behave as if they were serialised — dirty reads, non-repeatable reads, and phantom reads are controlled by the isolation level. **Durability**: once a transaction commits, the data survives crashes (InnoDB uses the redo log / doublewrite buffer). InnoDB is ACID-compliant; MyISAM is not (no transactions). All four properties work together: without any one of them, the database cannot be trusted.
Atomicity & Consistency in Practice
Atomicity is implemented via the **undo log**: InnoDB records the before-image of every changed row. On ROLLBACK (or crash), it replays the undo log to reverse changes. Consistency means foreign-key constraints, NOT NULL, UNIQUE, and CHECK constraints are validated at commit time. A transaction that violates a constraint is automatically rolled back.
-- Classic bank transfer: must be atomic
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If second UPDATE fails (e.g. account 2 not found), rollback both
COMMIT;
-- Consistency: FK prevents orphan rows
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT; -- rejects DELETE on customers if orders exist
-- Attempted violation → ERROR 1452, transaction rolled back
INSERT INTO orders (customer_id, total) VALUES (9999, 50.00);Isolation Levels
MySQL InnoDB supports four isolation levels. The default is **REPEATABLE READ** — a transaction sees a consistent snapshot from the moment it first reads. **READ COMMITTED** (PostgreSQL default) re-reads the latest committed data on each statement. **SERIALIZABLE** prevents all anomalies but reduces concurrency. **READ UNCOMMITTED** allows dirty reads and is almost never used.
-- Check/set isolation level
SELECT @@transaction_isolation; -- REPEATABLE-READ (default)
SET SESSION transaction_isolation = 'READ-COMMITTED';
-- Isolation anomalies:
-- Dirty read: T2 reads T1's uncommitted change (RC prevents this)
-- Non-repeatable read: T2 re-reads a row and sees T1's committed UPDATE (RR prevents this)
-- Phantom read: T2 re-executes a range query and sees T1's new INSERT (Serializable prevents)
-- InnoDB REPEATABLE READ uses MVCC snapshots + gap locks to prevent phantoms
-- (stricter than the SQL standard requires)
-- Use READ COMMITTED for high-concurrency OLTP to reduce lock contention
-- Use SERIALIZABLE for financial batch jobs requiring total accuracyDurability: Redo Log & Sync Settings
Durability is implemented via the **redo log** (write-ahead log). Before any data page is modified, InnoDB writes the change to the redo log. On crash recovery, uncommitted transactions are rolled back (undo log) and committed-but-unflushed changes are re-applied (redo log). The key durability tuning knob is `innodb_flush_log_at_trx_commit`.
-- innodb_flush_log_at_trx_commit (default = 1 = fully durable)
-- 1: fsync to disk on every COMMIT — fully ACID durable (default, recommended for prod)
-- 2: write to OS buffer on COMMIT; fsync every second — loses ~1s of commits on crash
-- 0: write and sync every second — fastest, loses up to 1s on MySQL crash
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- sync_binlog also affects durability when binary logging is enabled
-- sync_binlog=1 (default) syncs binlog on every COMMIT (safest for replication)
-- sync_binlog=0 lets OS control sync (faster, unsafe for crash durability)
-- Verify redo log size (larger = better performance for write-heavy workloads)
SHOW VARIABLES LIKE 'innodb_redo_log_capacity'; -- MySQL 8.0.30+Key Points to Remember
- 1Atomicity: undo log enables rollback — partial updates never persist
- 2Consistency: FK, UNIQUE, NOT NULL, CHECK constraints enforced at commit
- 3Isolation: four levels (READ UNCOMMITTED → SERIALIZABLE); default is REPEATABLE READ
- 4Durability: redo log (WAL) ensures committed changes survive crashes
- 5innodb_flush_log_at_trx_commit=1 is the only fully durable setting
- 6InnoDB is ACID-compliant; MyISAM has no transaction support
Interview Questions
Sign in to ask AriaExplain the four ACID properties with a bank transfer example.
What is the difference between REPEATABLE READ and READ COMMITTED isolation levels?
What is the redo log and how does it implement durability in InnoDB?
What anomalies can occur if isolation level is READ UNCOMMITTED?
What does innodb_flush_log_at_trx_commit=2 mean for durability guarantees?
Ask Aria about ACID Properties
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.