Introduction
ACID is an acronym describing four properties that a database transaction must guarantee to be considered reliable: Atomicity, Consistency, Isolation, and Durability. Together they ensure that data remains correct and trustworthy even when multiple transactions run concurrently or the system crashes mid-operation. Relational databases such as PostgreSQL, MySQL (InnoDB), Oracle, and SQL Server are built around these guarantees.
Cricket analogy: Just as the DRS system must confirm a wicket decision is complete, correct, unaffected by other reviews, and permanently recorded on the scorecard before play resumes, ACID guarantees a transaction is atomic, consistent, isolated, and durable before a database considers it done.
Syntax
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;Explanation
Atomicity means a transaction is treated as a single indivisible unit: either every statement inside it succeeds and is applied, or none of them are — a failure partway through causes the whole transaction to be rolled back. Consistency means a transaction can only move the database from one valid state to another valid state, never violating constraints, triggers, or business rules such as foreign keys. Isolation means concurrently running transactions do not see each other's uncommitted, intermediate changes, so each transaction behaves as if it ran alone. Durability means once a transaction is committed, its changes survive permanently, even in the event of a power loss or crash immediately afterward, typically because the changes are written to durable storage or a write-ahead log.
Cricket analogy: Atomicity is like a run-out appeal that's either given fully out or not out, no half decisions; consistency keeps the scorecard within valid overs; isolation means one umpire's review doesn't leak into another match; durability means the recorded result survives even if the stadium lights fail.
Example
-- A bank transfer must be atomic: if the credit fails,
-- the debit must also be undone.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Suppose account 2 does not exist, this fails a constraint check
UPDATE accounts SET balance = balance + 100 WHERE account_id = 999;
ROLLBACK; -- both updates are undone, balances stay unchangedAnalysis
If the second UPDATE in the example fails, atomicity guarantees the first UPDATE is undone too, so account 1 never loses money without account 2 gaining it. Consistency ensures that any CHECK constraints (like balance >= 0) or foreign keys are respected throughout. Isolation ensures that another transaction reading account 1's balance mid-transfer either sees the balance before the transfer or after it, never a half-applied state. Durability ensures that once COMMIT succeeds, the transferred funds are not lost even if the server crashes one second later.
Cricket analogy: If the second run in a two-run single is somehow disallowed, the whole delivery reverts like atomicity undoing both UPDATEs; a CHECK-like no-ball rule stops an invalid extra run; isolation means a TV viewer sees either the pre-run or post-run score, never a half-recorded run; durability means the committed run stays on the scorecard even if the broadcast cuts out.
Key Takeaways
- Atomicity: all statements in a transaction succeed together or none are applied.
- Consistency: a transaction only moves the database between valid states that satisfy all constraints and rules.
- Isolation: concurrent transactions do not observe each other's uncommitted intermediate changes.
- Durability: committed changes persist permanently, surviving crashes and power failures.
- ACID is what makes transactions a safe unit of work for multi-step, multi-table operations.
Practice what you learned
1. Which ACID property guarantees that a transaction's changes are either fully applied or not applied at all?
2. A transaction that would leave a table violating a CHECK constraint is prevented from committing. Which ACID property is this?
3. Which property ensures that once COMMIT returns successfully, the change survives a subsequent server crash?
4. Which ACID property is primarily concerned with concurrently running transactions not seeing each other's uncommitted changes?
Was this page helpful?
You May Also Like
Transactions and COMMIT/ROLLBACK
How to group SQL statements into transactions and use COMMIT and ROLLBACK to make or undo their effects.
Locking and Concurrency Control
How databases use locks and isolation levels to let transactions run concurrently without corrupting data.
Constraints and Data Integrity
Explore how PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints enforce valid data.