SQL Transactions and ACID Properties Explained Simply

What is a transaction

BEGIN;

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

COMMIT;

These two UPDATE statements are the running example for the rest of this article: moving 100 from account 1 to account 2. Wrapped in BEGIN and COMMIT, the database treats them as one transaction — a single unit of work that either takes effect completely or not at all. If anything goes wrong before COMMIT, running ROLLBACK instead undoes every change the transaction made, as if it had never started.

Atomicity: all or nothing

Atomicity is the guarantee that a transaction's statements can't be left half-done. If the first UPDATE above succeeds and debits account 1, but the second fails — a constraint violation, a crash, a network drop — atomicity ensures the entire transaction rolls back, restoring account 1's balance too. Without atomicity, that 100 could vanish: debited from one account, never credited to the other.

Consistency: never an invalid state

Consistency means a transaction can only take the database from one valid state to another valid state — it can't leave behind a row that violates a constraint, a foreign key pointing at nothing, or a check constraint failure. If account balances have a CHECK (balance >= 0) constraint and the transfer would push account 1 negative, the whole transaction is rejected rather than committing a state the schema says shouldn't exist.

Isolation: concurrent transactions don't interfere

Isolation guarantees that two transactions running at the same time don't see each other's uncommitted, in-progress changes. If a second transaction reads account 2's balance in the middle of the transfer above — after the debit from account 1 but before the credit lands — a properly isolated database won't let it see that inconsistent in-between state; it will see either the balances from before the transfer started, or the balances after it fully completes, never something in between.

Durability: committed means committed

Durability guarantees that once COMMIT returns successfully, the change is permanent — even if the server crashes or loses power one second later. Databases achieve this by writing the change to a persistent transaction log before acknowledging the commit, so recovery after a crash can replay anything that was committed but not yet fully written to the main data files. Without durability, a "successful" transfer could silently disappear on restart.