Transactions and ACID Guarantees
Grouping several statements into one all-or-nothing unit, and the four ACID properties that make a transaction a genuine guarantee rather than a convention.
What you'll learn
- Explain what BEGIN/COMMIT/ROLLBACK actually guarantee for a group of statements
- Define each of the four ACID properties in your own terms
- Identify a scenario where skipping a transaction would leave data in an inconsistent state
Prerequisites
Explanation
This lesson's code is real PostgreSQL syntax and behavior, shown for reading, not executed here. SQLite's transaction model exists but genuinely differs in important ways (locking granularity, concurrent-connection behavior) from PostgreSQL's — demonstrating transaction semantics honestly needs a real, multi-connection PostgreSQL instance, which is exactly what this module's guided local lab provides.
A transaction groups multiple SQL statements into one all-or-nothing unit: BEGIN; starts it, every statement afterward is provisional until COMMIT; makes all of them permanent together, or ROLLBACK; discards every one of them as if none had ever run — there is no partial state where some statements in the transaction took effect and others didn't. This matters enormously the moment an operation genuinely requires more than one statement to stay consistent: transferring money between two accounts is UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; — two separate statements that must either both succeed or both fail together, since a crash between them (with only the first statement applied) would make money vanish from the system entirely.
ACID names the four guarantees a transaction provides. Atomicity is exactly the all-or-nothing property just described. Consistency means a transaction can only move the database from one valid state (satisfying every constraint — foreign keys, checks, uniqueness) to another valid state; a transaction that would violate a constraint is rejected entirely, not partially applied. Isolation means concurrent transactions don't see each other's uncommitted, in-progress changes (the exact behavior explored in depth in the next lesson) — without it, one transaction could read another's half-finished work and act on data that's about to be rolled back and never actually existed. Durability means once COMMIT succeeds, the change survives — a server crash one millisecond later cannot undo a committed transaction, because PostgreSQL has already written it to durable storage (the write-ahead log) before confirming the commit.
The honest, important caveat: ACID guarantees correctness properties of a single database's transactions — it does not eliminate every category of concurrency bug on its own (the next lesson covers specific anomalies that can still occur depending on the chosen isolation level), and it says nothing at all about coordinating a transaction that spans multiple, separate databases or services (a genuinely harder problem with its own separate body of technique). ACID is a precise, powerful guarantee about what a single transaction against a single database will and won't do — not a claim that every possible correctness problem in a distributed system disappears once you wrap statements in BEGIN/COMMIT.
Example
Real PostgreSQL transaction syntax and its ACID guarantees, shown for reading -- genuinely testable only against a real, multi-connection PostgreSQL server, which this module's guided local lab provides.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If BOTH statements succeeded and the constraints all hold, make it permanent:
COMMIT;
-- If anything went wrong (a constraint violation, an application-detected error,
-- or you simply change your mind before committing), discard everything since BEGIN:
-- ROLLBACK;
-- A constraint violation forces an automatic rollback of the whole transaction:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- succeeds
UPDATE accounts SET balance = balance + 100 WHERE id = 999; -- fails: id 999 has a CHECK violation
-- The ENTIRE transaction is now aborted -- the first UPDATE is also rolled back,
-- even though it individually would have succeeded on its own.
ROLLBACK;Guided exercise
Guided exercise
Write runTransaction(statements, apply, validate) modeling atomicity: apply each statement in order (via apply(state, statement), which returns a new state), but if validate(finalState) returns false at the end, discard ALL changes and return the ORIGINAL state (a rollback) instead of the modified one.
Checks: commits a valid transaction, applying every statement · rolls back to the exact original state when validation fails, not a partially-applied state
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write transferFunds(accounts, fromId, toId, amount) modeling a real money-transfer transaction: it must throw (leaving accounts COMPLETELY UNCHANGED) if fromId or toId don't exist, if amount <= 0, or if the source account's balance would go negative -- and only apply BOTH balance changes together if every check passes.
Checks: a valid transfer correctly updates both accounts · insufficient funds throws and leaves the original data completely untouched · a missing account throws · a non-positive amount throws
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Running two statements that must stay consistent (like a two-account transfer) as separate, un-transacted operations -- a crash or error between them can leave the data in a state that violates the business rule they were supposed to jointly enforce.
- Assuming ACID alone eliminates every concurrency bug -- Atomicity, Consistency, Isolation, and Durability are precise, real guarantees, but Isolation specifically has multiple levels with different tradeoffs (covered in the next lesson), and not every isolation level prevents every possible anomaly.
- Assuming a transaction against one database automatically extends to coordinate changes across a completely separate database or service -- ACID guarantees apply to a single database's transaction; coordinating multiple, independent systems is a separate, harder problem.
Knowledge check
Takeaway
A transaction's atomicity guarantees a group of statements succeeds or fails as one indivisible unit — never partially applied — and ACID as a whole is a precise, powerful, but bounded set of guarantees about a single database's transactions, not an automatic solution to every concurrency or distributed-systems problem.
Summary
BEGIN/COMMIT/ROLLBACK group statements into an all-or-nothing transaction. Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't see each other's uncommitted work), and Durability (a commit survives a crash) are the four ACID guarantees — precise properties of a single database's transactions, not a blanket immunity to every concurrency bug.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.