Basic Transactions and ACID

A transaction groups multiple SQL statements into a single unit: either every statement in it succeeds and its changes are saved, or none of them are — there's no in-between state where only some of the statements took effect.

Why this matters: a bank transfer

Moving money between two accounts needs two updates — debit one, credit the other:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If the connection drops, the server crashes, or an error occurs between those two UPDATE statements, you do not want the first one to have already taken effect — money would vanish, debited from one account and never credited to the other. BEGIN starts the transaction; COMMIT makes every change within it permanent, together, or not at all.

ROLLBACK: undoing a transaction in progress

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- something went wrong — insufficient funds, an application error, etc.
ROLLBACK;

ROLLBACK discards every change made since BEGIN, as if none of it had happened. Application code typically wraps a transaction in a try/catch: COMMIT on success, ROLLBACK in the catch block if anything fails partway through.

ACID: the guarantees a transaction provides

• Atomicity — the transaction happens entirely or not at all; this is the property the bank transfer example above depends on.
• Consistency — a transaction can only move the database from one valid state to another; constraints (from the earlier lesson), foreign keys, and other rules are never left violated once a transaction commits.
• Isolation — concurrent transactions don't see each other's uncommitted, in-progress changes. Without isolation, a second transaction reading accounts mid-transfer could see the debit applied but not yet the credit — an inconsistent snapshot that should never be externally visible.
• Durability — once a transaction commits, the change survives, even if the database crashes immediately afterward. Committed data is written to durable storage, not just held in memory.

Most relational databases (PostgreSQL, MySQL with the InnoDB engine, SQL Server) provide full ACID guarantees by default for standard operations — it's a foundational reason to reach for a relational database when correctness under concurrent writes matters, which is one of the points of comparison in the next lesson.

The final lesson of this course steps back to compare SQL databases against NoSQL alternatives — where each model fits, and where ACID guarantees like these matter most.