SQL / TRANSACTIONS AND CONCURRENCY
ACID and the promises every transaction makes
Explain what atomicity, consistency, isolation and durability each guarantee, where each promise begins, and which letter a given bug belongs to.
What you will learn
- Say exactly what each ACID letter guarantees, and what it deliberately does not
- Encode invariants as CHECK, UNIQUE, and FOREIGN KEY so consistency is enforceable
- Treat a returned COMMIT as the only proof that a change survived
- Defer constraint checks to COMMIT when a valid state is broken mid-transaction
Understanding ACID and the promises every transaction makes
ACID is not one guarantee but four, and they answer different questions. Atomicity and durability are promises about failure: a transaction leaves either all of its changes or none of them, and once COMMIT returns, those changes survive a crash of the database process or the machine. Consistency is a promise about your rules: no transaction can commit a state that violates a constraint the schema declares. Isolation is a promise about the other sessions, and it is the one letter you can dial down, since every level below serializable trades away some of it for throughput.
Consistency is the letter that gets over-read. The engine has no opinion about what your data means; it enforces exactly the NOT NULL, CHECK, UNIQUE, FOREIGN KEY and trigger logic you wrote and nothing else, so a transaction that credits the wrong account is perfectly ACID-compliant as long as no declared rule breaks. The promise is also scoped to the transaction boundary rather than to every instant inside it: swapping two values that must stay unique necessarily passes through an illegal state, which is why constraints can be marked DEFERRABLE INITIALLY DEFERRED and checked only when COMMIT runs.
All four promises hang off the same moment. A statement that returned success proves only that it was legal against the uncommitted state; nothing is guaranteed until COMMIT returns, because the engine flushes the transaction's write-ahead log record to durable storage at that point, and recovery replays exactly those transactions whose commit record reached disk. That is why ordering in the surrounding code matters: send the receipt, return 200, or enqueue the job after COMMIT succeeds, and if the connection dies while COMMIT is in flight, treat the outcome as genuinely unknown and go check.
The practical value of the acronym is triage. A row that vanished after the client saw success is a durability question; a half-applied change is atomicity; a total that no single transaction ever computed is isolation; nonsense data that every constraint permits is not a database failure at all, it is a missing constraint.
-- PostgreSQL (psql). numeric keeps the money arithmetic exact.
CREATE TABLE accounts (
id integer PRIMARY KEY,
balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 100.00), (2, 50.00);
-- Valid move between two legal states: total stays 150.00
BEGIN;
UPDATE accounts SET balance = balance - 40 WHERE id = 1;
UPDATE accounts SET balance = balance + 40 WHERE id = 2;
COMMIT;
-- Illegal move: account 1 only holds 60.00 now
BEGIN;
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
UPDATE accounts SET balance = balance + 80 WHERE id = 2;
COMMIT;
SELECT id, balance FROM accounts ORDER BY id;Each ACID letter is a separate guarantee scoped to one transaction and anchored to one event, a COMMIT that returns successfully, and consistency covers only the invariants you actually declared.
Worked examples
Consistency is checked at the boundary
A deferred UNIQUE constraint lets a transaction pass through an illegal state, as long as the state is legal again when COMMIT runs.
CREATE TABLE seats (
id integer PRIMARY KEY,
seat integer NOT NULL,
CONSTRAINT seats_seat_key UNIQUE (seat) DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO seats VALUES (1, 12), (2, 14);
-- Swap the two seats: unavoidably duplicated in the middle
BEGIN;
UPDATE seats SET seat = 14 WHERE id = 1;
UPDATE seats SET seat = 12 WHERE id = 2;
COMMIT;
-- Still duplicated when COMMIT arrives
BEGIN;
UPDATE seats SET seat = 12 WHERE id = 1;
COMMIT;
SELECT id, seat FROM seats ORDER BY id;Example explained
Line 1DEFERRABLE INITIALLY DEFERRED moves the uniqueness test from each statement to the end of the transaction.
Line 2After the first UPDATE both rows hold seat 14, a state that exists only inside the transaction, which is why isolation has to hide it from other sessions.
Line 3COMMIT re-checks the constraint, finds 12 and 14 used once each, and succeeds, so the invariant held at both boundaries even though it failed in between.
Line 4In the second transaction the UPDATE reports success and the error surfaces at COMMIT instead, and that failed COMMIT ends the transaction by rolling it back.
Durability is a promise about the log flush
synchronous_commit shows that D is the guarantee you can weaken for a single transaction without touching A, C or I.
SHOW synchronous_commit;
BEGIN;
SET LOCAL synchronous_commit = off;
CREATE TABLE ping (note text NOT NULL);
INSERT INTO ping VALUES ('cheap commit');
COMMIT;
SHOW synchronous_commit;
SELECT count(*) FROM ping;Example explained
Line 1The first SHOW reports the shipped default: on means COMMIT does not return until the transaction's WAL record is flushed to durable storage.
Line 2SET LOCAL applies only until this transaction ends, so this one COMMIT returns as soon as the record is written to the WAL buffers, not to disk.
Line 3The write is still atomic, still constraint-checked and still isolated; only the crash window changes, and a crash just after this COMMIT can lose the whole transaction but never half of it.
Line 4The second SHOW is back to on, which is the evidence that the relaxed setting was scoped to the transaction and not to the session.
Important notes
Durability covers a crash of the database process or the operating system, not a failed disk, a dropped table or a bad migration; those need replication and backups, and durability can also be traded away deliberately with settings like synchronous_commit = off or UNLOGGED tables.
Defaults do not always give you all four letters: SQLite is ACID but silently ignores FOREIGN KEY unless PRAGMA foreign_keys = ON, and MySQL only offers transactions on transactional engines such as InnoDB.
Common mistakes
Expecting the C in ACID to catch bad data by itself: with no CHECK or FOREIGN KEY declared, a transaction that stores a negative balance or an order pointing at a nonexistent customer commits cleanly, and the engine has broken no promise.
Treating the last successful UPDATE as the point of no return: the response goes out, a later statement in the same transaction errors, everything rolls back, and the user now holds a receipt for a transfer the database never made.
Assuming ACID alone makes concurrent work correct: at the usual default isolation level two transactions can each satisfy every constraint and still interleave into a total that neither of them computed.
Try it yourself
Change, predict, then run
Create accounts(id integer PRIMARY KEY, balance numeric(10,2) NOT NULL CHECK (balance >= 0)) with two funded rows, run one transfer that fits and one that overdraws, then check with SELECT sum(balance) that the total is untouched. Drop the CHECK constraint, rerun the overdraw, and watch the identical SQL commit a negative balance: that difference is the entire consistency promise.
Open the SQL workspaceCheck your understanding
An INSERT stores an order whose customer_id matches no row in customers. No foreign key is defined, and the transaction commits. Which statement is accurate?
- Consistency was violated, because a database is supposed to keep related rows in agreement
- This is an isolation failure: another transaction must have deleted the customer row
- ACID still holds, because consistency only promises that the constraints you declared are satisfied at commit
- The row is durable but inconsistent, so crash recovery will discard it later
Show answer
Consistency is defined relative to the rules written into the schema, so with no foreign key the engine has no notion that an order needs a real customer and the commit breaks no promise. Option 1 is tempting because 'consistent' in English suggests data that makes sense, but the engine's contract covers only declared constraints and triggers; and crash recovery replays committed work rather than re-judging it, so option 4 is wrong as well.