ACID Properties and Anomalies
Recap atomicity, consistency, isolation, durability, and the four classical anomalies: dirty read, non-repeatable read, phantom, lost update.
ACID Properties and Anomalies is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
ACID
A transaction is reliable when it is:
- Atomic — all or nothing
- Consistent — moves the DB from one valid state to another
- Isolated — concurrent transactions don't step on each other
- Durable — once committed, data survives crashes
A Transaction in SQL
Wrap operations in BEGIN ... COMMIT:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If anything fails, ROLLBACK leaves both rows unchanged.Atomicity in Action
If the second UPDATE fails, the first is rolled back — money isn't lost halfway:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Crash here, or ROLLBACK: account 1 is unchanged.Consistency
Constraints (NOT NULL, CHECK, FK) are checked at commit. A transaction that would leave the DB in an invalid state fails:
BEGIN;
INSERT INTO orders (user_id, total) VALUES (999, 100); -- FK fails on commit if user 999 doesn't exist.
COMMIT;
-- ERROR: insert or update violates foreign key constraint
-- ROLLBACK happens automatically.Isolation: Why It Matters
Two concurrent transactions can corrupt each other's view without isolation. Classic anomalies:
- Dirty read — read uncommitted data
- Non-repeatable read — same query gives different rows
- Phantom — new rows appear mid-transaction
- Lost update — two updates clobber each other
Dirty Read
Reading a row that another transaction has changed but not committed. PostgreSQL prevents this at all isolation levels.
Non-Repeatable Read
You read row 1, another transaction commits an update to row 1, you re-read — different value.
Phantom Read
You run SELECT COUNT(*) twice in one transaction. Between the calls, another transaction commits an INSERT — your second count is bigger.
Lost Update
Two transactions both read balance=100, both write balance=balance-50, both commit — one of the -50 is lost.
Durability
Once COMMIT returns, the change is on disk (or replicated, depending on settings). A crash a millisecond later doesn't lose it. PostgreSQL achieves durability via WAL (Write-Ahead Logging).
Autocommit
By default, each statement is its own transaction:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Implicitly: BEGIN; statement; COMMIT;SAVEPOINT
Partial rollback within a transaction:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_step2;
UPDATE accounts SET balance = balance + 100 WHERE id = 999;
-- ERROR — but we can recover:
ROLLBACK TO SAVEPOINT before_step2;
-- continue
COMMIT;Recap
Transactions are the safety net of relational databases.
- ACID: atomic, consistent, isolated, durable
- Four classic anomalies
- BEGIN ... COMMIT or ROLLBACK
- SAVEPOINT for partial rollback
Quick Check
What anomaly occurs when two transactions both read the same value, modify it, and both commit?
Frequently asked questions
Is the “ACID Properties and Anomalies” lesson free?
Yes — the full text of “ACID Properties and Anomalies” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “ACID Properties and Anomalies”?
Recap atomicity, consistency, isolation, durability, and the four classical anomalies: dirty read, non-repeatable read, phantom, lost update. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “ACID Properties and Anomalies” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.