Foundations Of Databases
ACID: the Promise a Database Makes
A transaction is a group of operations the database treats as a single indivisible unit: either all of it happens, or none of it does.
JrCodex·7 min read
Jr Codex DBMS Notes
Level: Beginner Prerequisites: Chapter 3: DBMS Architecture Time to complete: ~20 minutes
Table of Contents
- What a Transaction Is
- Atomicity
- Consistency
- Isolation
- Durability
- Seeing ACID Work
- Summary & Next Steps
1. What a Transaction Is
A transaction is a group of operations the database treats as a single indivisible unit: either all of it happens, or none of it does.
The Canonical Example
─────────────────────────────────────────
Transfer 100 from account A to account B.
UPDATE accounts SET balance = balance - 100
WHERE id = 'A';
UPDATE accounts SET balance = balance + 100
WHERE id = 'B';
These two statements MUST NOT be separable. Any
outcome where one ran and the other did not is
money created or destroyed.
─────────────────────────────────────────
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- both, or neitherACID names the four properties a database guarantees about that unit. Chapter 1 promised these; this chapter defines them, and Modules 7 and 8 explain how they are implemented.
2. Atomicity
All operations in a transaction succeed, or none of them do.
What It Protects Against
─────────────────────────────────────────
A crash, an error, or an explicit ROLLBACK
partway through.
Without atomicity:
UPDATE A ✓ (-100)
<< crash >>
UPDATE B ✗
Result: 100 has vanished.
With atomicity:
the incomplete transaction is UNDONE on
recovery, and the database returns to the state
before BEGIN.
─────────────────────────────────────────
How It Is Achieved
─────────────────────────────────────────
A LOG of every change is written BEFORE the
change itself. On restart, the database replays
the log and undoes anything that never committed.
──► Module 8, Chapter 1
─────────────────────────────────────────
3. Consistency
A transaction moves the database from one valid state to another valid state.
The Word "Consistency" Is Overloaded
─────────────────────────────────────────
ACID consistency = no declared CONSTRAINT is
violated when the
transaction commits.
Keys, foreign keys, CHECK,
NOT NULL, unique.
It does NOT mean the data is "correct" in a
business sense. If your code transfers money to
the wrong account, every constraint holds and
the database is perfectly consistent.
(A third meaning — consistency in distributed
systems, as in CAP — is different again. That
is Module 9.)
─────────────────────────────────────────
CREATE TABLE accounts (
id TEXT PRIMARY KEY,
balance NUMERIC NOT NULL CHECK (balance >= 0) -- the invariant
);
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 'A'; -- A only has 300
COMMIT;
-- CHECK constraint failed. The transaction ABORTS; nothing is applied.Who Is Responsible for What
─────────────────────────────────────────
YOU declare the rules that define validity
THE DBMS guarantees no transaction ever commits
a state that breaks them
Consistency is therefore only as strong as the
constraints you wrote. This is why Module 4
spends real time on getting them right.
─────────────────────────────────────────
4. Isolation
Concurrent transactions do not interfere with each other.
What Goes Wrong Without It
─────────────────────────────────────────
Two transfers from account A (balance 100),
running at the same time:
T1 reads balance = 100
T2 reads balance = 100 ← same value
T1 writes balance = 100 - 60 = 40
T2 writes balance = 100 - 50 = 50 ← overwrites
Result: two withdrawals of 60 and 50 from an
account holding 100, ending at 50. This is a
LOST UPDATE.
─────────────────────────────────────────
The Ideal, and the Reality
─────────────────────────────────────────
The ideal is SERIALIZABILITY: the result of
running transactions concurrently is identical to
running them one after another in SOME order.
In practice, full serialisability is expensive,
so databases offer ISOLATION LEVELS — a dial
trading correctness guarantees for concurrency.
Most databases DEFAULT to a level weaker than
serialisable. Knowing which one you are on is one
of the more consequential things in this
curriculum.
──► Module 7, Chapters 2-4
─────────────────────────────────────────
5. Durability
Once a transaction commits, its changes survive any subsequent failure.
What "Committed" Actually Means
─────────────────────────────────────────
Not "written to the table file" — that may
happen minutes later.
It means "written to the LOG, and the log is
flushed to durable storage".
The database can then reconstruct the change
after any crash, because the log survived.
─────────────────────────────────────────
The Cost, and the Temptation
─────────────────────────────────────────
Durability requires an fsync — genuinely waiting
for the storage device to confirm the write.
This is slow, and it bounds how many transactions
per second you can commit.
Every database offers a way to relax it
(asynchronous commit, fsync=off). It is a large
speedup, and it means a crash can lose recently
committed transactions.
Turn it off deliberately, for data you can
rebuild — never by accident.
──► Module 8
─────────────────────────────────────────
6. Seeing ACID Work
import sqlite3
conn = sqlite3.connect("bank.db", isolation_level=None) # explicit transaction control
conn.executescript("""
CREATE TABLE IF NOT EXISTS accounts (
id TEXT PRIMARY KEY,
balance NUMERIC NOT NULL CHECK (balance >= 0)
);
DELETE FROM accounts;
INSERT INTO accounts VALUES ('A', 300), ('B', 50);
""")
def transfer(conn, src, dst, amount):
try:
conn.execute("BEGIN")
conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))
conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
conn.execute("COMMIT") # ATOMIC + DURABLE from here
return True
except sqlite3.IntegrityError as e:
conn.execute("ROLLBACK") # ATOMICITY: undo the first UPDATE too
print("rejected:", e)
return False
print(transfer(conn, 'A', 'B', 100)) # True — A=200, B=150
print(transfer(conn, 'A', 'B', 5000)) # False — CHECK fails, and A is still 200
print(conn.execute("SELECT id, balance FROM accounts ORDER BY id").fetchall())
# [('A', 200), ('B', 150)] ← the failed transfer left NO traceRead the Second Transfer Carefully
─────────────────────────────────────────
The first UPDATE succeeded — A briefly went to
-4800 inside the transaction.
The second violated the CHECK, so the whole
transaction rolled back, and A is 200 again.
Atomicity is what makes the partial work
disappear. Consistency is what detected the
problem. Both were needed.
─────────────────────────────────────────
7. Summary & Next Steps
Key Takeaways
- A transaction is an indivisible unit of work; ACID names the four guarantees a database makes about it.
- Atomicity means all-or-nothing and is implemented by logging changes before applying them; durability means a commit survives a crash because the log was flushed.
- ACID consistency means no declared constraint is violated — not that the data is business-correct — so it is only as strong as the constraints you wrote.
- Full serialisable isolation is expensive, so most databases default to a weaker level; knowing which one you are running on matters.
Module 1 Complete — What's Next
You now know what a database guarantees and roughly how it is built. Module 2 makes the relational model precise: what a relation actually is, how keys and constraints work, and the algebra that SQL compiles down to.
Concept Check
- Distinguish the three different meanings of "consistency" this chapter mentions.
- Why does "committed" mean written to the log rather than written to the table?
- In the failed 5000 transfer, account A briefly held a negative balance inside the transaction. Which two ACID properties combined to make that invisible?
Next Module
→ Module 2: The Relational Model
Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index