ACID Properties
Understand Atomicity, Consistency, Isolation, and Durability in database transactions.
What you'll learn
- What ACID stands for and why each property matters
- Atomicity — all or nothing
- Consistency — valid state before and after
- Isolation levels — Read Uncommitted to Serializable
- Durability — committed data survives crashes
Concept
ACID Overview
ACID is a set of four properties that guarantee reliable processing of database transactions. They were defined by Andreas Reuter and Theo Härder in 1983 and remain the gold standard for transactional integrity. Relational databases like PostgreSQL and MySQL (with InnoDB) provide full ACID compliance; many NoSQL databases relax these properties for better scalability (the BASE model).
Atomicity
Atomicity means a transaction is an indivisible unit: either all its operations succeed, or none take effect. If you transfer money between accounts and the system crashes mid-transfer, the partial change is rolled back. There is no "half-applied" state. This is enforced by the transaction log (WAL — Write-Ahead Log).
Consistency
Consistency means a transaction takes the database from one valid state to another. All constraints (primary keys, foreign keys, checks, triggers) must be satisfied after the transaction. If a transaction would violate a constraint, it is rolled back. Consistency is partly enforced by the database (constraints) and partly by the application (business rules).
Isolation
Isolation determines how concurrent transactions interact. The SQL standard defines four isolation levels:
- Read Uncommitted — can read uncommitted data (dirty reads); rarely used
- Read Committed — only sees committed data; default in PostgreSQL
- Repeatable Read — same query returns the same result within a transaction
- Serializable — transactions behave as if executed one at a time; strongest but slowest
Higher isolation means more safety but less concurrency and performance.
Durability
Durability means once a transaction is committed, its changes survive system crashes, power failures, and restarts. This is achieved by writing committed changes to non-volatile storage (disk or SSD) before acknowledging the commit. The write-ahead log (WAL) ensures that on restart, the database can replay committed transactions and undo uncommitted ones.
Example
-- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Read a product's stock
SELECT stock FROM products WHERE id = 42;
-- Another transaction cannot modify this row at
-- SERIALIZABLE level until we commit or rollback
-- Decrement stock and create an order
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;
INSERT INTO orders (product_id, user_id, total)
VALUES (42, 1, 19.99);
COMMIT;
-- Check current isolation level (PostgreSQL)
SHOW transaction_isolation;
-- Set default isolation level for the session
SET SESSION CHARACTERISTICS AS TRANSACTION
ISOLATION LEVEL READ COMMITTED;
Setting SERIALIZABLE isolation for a critical read-then-write transaction, checking the current level, and setting a session default.
Try it
- SQL Formatter
Format transaction isolation level statements.
- HTTP Status Codes
API responses often mirror transaction outcomes — 200 vs 409.
Common mistakes
The mistake
Using the default isolation level for critical read-then-write patterns
The fix
Read Committed (the default) allows non-repeatable reads. For check-then-act patterns (e.g. check stock then decrement), use REPEATABLE READ or SERIALIZABLE, or use SELECT ... FOR UPDATE.
The mistake
Assuming ACID means no concurrency issues
The fix
ACID guarantees per-transaction integrity, but concurrent transactions can still conflict. Choose the right isolation level and use explicit locks when needed.
Practice ACID Properties
3 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.