SQL Transactions and ACID Properties Explained

A transaction groups one or more SQL statements into a single, all-or-nothing unit of work. Either every statement in the transaction succeeds and its changes are saved permanently, or something goes wrong and the entire group is undone as if none of it had happened. This matters enormously any time an operation needs more than one statement to complete a single real-world action — like moving money between two bank accounts.

BEGIN, COMMIT, and ROLLBACK

Three commands control a transaction's lifecycle: BEGIN (or START TRANSACTION) marks where it starts, COMMIT saves everything done since then permanently, and ROLLBACK undoes everything since the transaction began.

Here's the classic example — transferring $200 from one account to another. Both the debit and the credit need to happen together, or neither should happen at all:

BEGIN;

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

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

If anything fails between the two UPDATE statements — a crash, a constraint violation, a disconnected connection — the database can roll everything back, and account 1 never actually loses its $200. Without wrapping both statements in a transaction, you'd risk the first update succeeding and the second failing, leaving $200 that simply vanished from the system.

If something does go wrong and you catch it yourself, you issue ROLLBACK explicitly:

BEGIN;

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

-- Something looks wrong — undo everything since BEGIN
ROLLBACK;

What ACID Actually Means

ACID is the set of guarantees a proper transaction gives you, and each letter answers a different question:

Atomicity — all statements in the transaction succeed together, or none of them do. No partial transfers.

Consistency — a transaction can only move the database from one valid state to another; it can't leave data violating constraints like a foreign key reference or a CHECK rule.

Isolation — transactions running at the same time don't see each other's half-finished work; from each transaction's point of view, it's as if it's running alone.

Durability — once a transaction is committed, it survives a crash immediately after. The data is safely written, not sitting in memory waiting to be lost.

When to Reach for a Transaction

Any time a single logical action requires more than one SQL statement to complete — transferring funds, placing an order that both inserts a row and decrements inventory, creating a user along with their default settings — wrap it in a transaction. Most application frameworks and database drivers support this directly, often with a simple "transaction" or "unit of work" wrapper that calls BEGIN/COMMIT/ROLLBACK for you behind the scenes.

---