The last lesson used COMMIT and ROLLBACK without saying exactly what guarantee they're actually backed by. ACID is that guarantee, spelled out as four separate properties, each covering a different way a transaction could otherwise go wrong.
Learning Objectives
- Define each of the four ACID properties: atomicity, consistency, isolation, and durability.
- Give a concrete example of what would break if any one of the four were missing.
- Explain how durability is actually implemented, a write-ahead log by one name or another, on every database here.
Atomicity
All-or-nothing, no partial transaction
Consistency
Only ever a valid, constraint-respecting state
Isolation
Concurrent transactions don't corrupt each other
Durability
A commit survives a crash
Atomicity
Atomicity is exactly the all-or-nothing guarantee the last lesson's example relied on, Beacon Robotics' two-statement reassignment either both runs or neither does. Without it, a crash between the DELETE and the INSERT could leave the project with no assignment at all, a state that should have been impossible.
Consistency
Consistency means a transaction can only move the database from one valid state to another, every constraint, foreign key, CHECK, and UNIQUE rule from Module 8 still has to hold once the transaction commits. A transaction that would leave a foreign key dangling, or violate a CHECK constraint, gets rejected rather than committed, the database never ends up in a state its own rules disallow.
ACID's "Consistency" is a different idea from the "C" in the unrelated CAP theorem (used for distributed systems), the same letter means something genuinely different in each, a common source of confusion.
Isolation
Isolation controls what one transaction is allowed to see from another transaction that's running at the same time, before either has committed. How much isolation a transaction actually gets, and which specific problems it prevents, is itself adjustable, covered in full in the next lesson on isolation levels.
Durability
Durability means a committed transaction survives a crash immediately afterward, a power failure one second after COMMIT returns must not lose that change. Every database here implements this the same underlying way, a write-ahead or transaction log that's flushed to disk before COMMIT is allowed to return, just under a different name.
| Database | Durability Mechanism |
|---|---|
| MySQL / MariaDB (InnoDB) | The InnoDB redo log |
| PostgreSQL | The write-ahead log (WAL) |
| SQL Server | The transaction log |
| SQLite | A rollback journal, or write-ahead logging in WAL mode |
| Oracle | The redo log |
The name differs, the underlying idea doesn't: write the change to a durable, sequential log first, confirm COMMIT only once that write is safely on disk, so a crash can always replay the log to recover anything that was actually committed.
Try It Yourself
Beacon Robotics' finance team runs a transaction that both deducts a bonus pool balance and inserts a payout row for an employee. Which ACID property specifically guarantees that a crash between those two statements can't leave a payout row with no matching deduction?
Atomicity. It guarantees that either both statements take effect, on COMMIT, or neither does, on ROLLBACK or a crash, there's no state where only one of the two has happened.
Frequently Asked Questions
Is a database that doesn't support transactions automatically not ACID-compliant?
Right, without transactions there's no atomicity or isolation to speak of at all, MySQL's MyISAM storage engine is the concrete example already seen in this module, it has no transaction support and so provides no ACID guarantees, unlike InnoDB on the exact same server.
Does ACID guarantee a query returns the fastest possible result?
No, ACID is entirely about correctness guarantees, not performance. A correctly-isolated, durable transaction can still be slow, the two are separate concerns, covered from the performance side in Module 13.
Common Beginner Mistakes
Confusing ACID's Consistency with the CAP theorem's Consistency
They're different ideas that happen to share a letter. ACID's Consistency means a transaction can't leave the database violating its own constraints. CAP's Consistency, a separate concept entirely about distributed systems, means every node sees the same data at the same time. Neither definition implies the other.
Assuming every table on MySQL gets the same ACID guarantees
Not if it's using the MyISAM storage engine, that one has no transaction support, so none of the four ACID properties actually apply to it, even though InnoDB tables on the same server fully support all four.
Best Practices
- Confirm a table is actually using a transactional storage engine (InnoDB on MySQL/MariaDB) before relying on ACID guarantees for it.
- Treat Consistency as "my constraints still hold", not as the unrelated CAP-theorem meaning of the same word.
- Don't confuse ACID's correctness guarantees with performance, a transaction can be fully ACID-compliant and still be slow.
Interview Questions
What do the four letters in ACID stand for, and give one concrete example of what each one prevents?
Atomicity: a crash leaving only half of a multi-statement transaction applied. Consistency: a committed transaction that violates a constraint, like a foreign key pointing at a row that doesn't exist. Isolation: one transaction reading another transaction's uncommitted, possibly-about-to-be-rolled-back changes. Durability: a committed transaction's change disappearing after a crash that happens moments later.
How is Durability actually implemented under the hood?
A write-ahead or transaction log, the change is written to a durable, append-only log and flushed to disk before COMMIT is allowed to return, so a crash can replay the log to recover anything that was genuinely committed. Every database covered in this course implements the same idea, PostgreSQL's WAL, SQL Server's transaction log, InnoDB's and Oracle's redo log, and SQLite's rollback journal or WAL mode are all the same underlying mechanism under a different name.
Summary
ACID is the four-part guarantee behind COMMIT and ROLLBACK: Atomicity (all-or-nothing), Consistency (constraints always hold), Isolation (concurrent transactions don't corrupt each other, degree adjustable), and Durability (a commit survives a crash, via a write-ahead log under whatever name a given database uses for it).
What's Next?
Isolation was the one property left deliberately vague here, the next lesson covers it properly: the four standard isolation levels, the three anomalies they prevent, and the real default each database actually ships with.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →