ACID Properties Explained

Concept

ACID is the set of four guarantees a transactional database makes. They are not marketing terms - each is implemented by specific machinery, and each can be traded away deliberately.

Atomicity

All statements in the transaction take effect, or none of them do.

START TRANSACTION;
    UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
    -- power failure here
    UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;
-- On restart, the first update is undone. The money is intact.

How: an undo log. Before changing a row, InnoDB records the old version. A rollback - explicit, or automatic after a crash - replays the undo log backwards.

Consistency

A transaction moves the database from one valid state to another. Every constraint holds before it starts and after it commits.

START TRANSACTION;
    INSERT INTO orders (id, customer_id, order_date, status, total)
    VALUES (3000, 999, '2024-10-01', 'pending', 100);   -- customer 999 does not exist
COMMIT;
-- The foreign key rejects it. The transaction cannot commit an invalid state.

How: constraints - primary keys, foreign keys, unique, check, not null - are enforced as part of the transaction. Note that consistency here means your declared rules hold. The database cannot know a rule you never declared, which is the strongest argument for declaring them.

Isolation

Concurrent transactions do not interfere with each other. Each behaves as though it ran alone - to the degree the isolation level promises.

-- Session A                         -- Session B
START TRANSACTION;                    START TRANSACTION;
UPDATE employees
   SET salary = 99000 WHERE id = 2;
                                      SELECT salary FROM employees WHERE id = 2;
                                      -- under REPEATABLE READ: the OLD value
COMMIT;
                                      SELECT salary FROM employees WHERE id = 2;
                                      -- still the OLD value, within this transaction
                                      COMMIT;

How: locking and multi version concurrency control (MVCC). InnoDB keeps older versions of rows so readers never block writers. Isolation is the one ACID property that is tunable - see the isolation levels note.

Durability

Once COMMIT returns, the change survives a crash, a power cut or a process kill.

How: write ahead logging. InnoDB writes the change to the redo log and flushes it to disk before reporting the commit. The data pages themselves can be written later; on restart, recovery replays the redo log.

-- The setting that decides how strong durability actually is
SELECT @@innodb_flush_log_at_trx_commit;
--  1  flush to disk on every commit - fully durable, the default
--  2  write to OS cache each commit  - survives a process crash, not a power cut
--  0  flush once per second          - fastest, can lose up to a second of commits

That setting is worth knowing about because it is the most common place durability is quietly traded for throughput. The default is 1; anything else means committed transactions can be lost.

Where ACID is relaxed on purpose

Important rules

• ACID applies to a transaction, not to a single statement - though a lone statement is its own transaction under autocommit.
• In MySQL, ACID requires InnoDB. MyISAM offers none of the four.
• Consistency depends on the constraints you declared; the database enforces your rules, not your intentions.
• Isolation is a spectrum controlled by the isolation level; the other three are not tunable in the same way.
• Durability depends on the log flushing configuration and on the storage actually honouring flush requests.

Common mistakes

• Believing a MyISAM table is protected by transactions.
• Assuming isolation is absolute; the default level in MySQL is REPEATABLE READ, not SERIALIZABLE.
• Treating consistency as "the data looks sensible" rather than "the declared constraints hold".
• Running with innodb_flush_log_at_trx_commit = 0 in a system that must never lose a commit.
• Expecting durability across an asynchronous replica failover.

Best practices

• Use InnoDB for anything that matters.
• Declare constraints; consistency is only as good as they are.
• Choose the isolation level deliberately rather than inheriting the default without thought.
• Leave innodb_flush_log_at_trx_commit at 1 unless you have accepted, in writing, the risk of losing commits.
• Test crash recovery before you need it.

Practice

1. Which ACID property does a foreign key violation demonstrate, and which does a crash mid transfer demonstrate?
2. Explain how the undo log and the redo log serve different ACID properties.
3. Name one setting or design decision that weakens each of the four properties.