What does ACID stand for in databases?

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means all-or-nothing execution, consistency means transactions preserve valid state and constraints, isolation means concurrent transactions do not interfere, and durability means committed changes survive crashes.

What is the difference between COMMIT and ROLLBACK?

COMMIT makes all the changes in the current transaction permanent and visible to others. ROLLBACK discards every change made since BEGIN, returning the database to its state before the transaction started. A transaction ends with one or the other.

What is the default isolation level in PostgreSQL?

PostgreSQL defaults to READ COMMITTED, where each statement sees only data that was committed before it began. You can raise it to REPEATABLE READ or SERIALIZABLE per transaction when you need stronger guarantees, at the cost of more potential conflicts.

Intermediate

SQL Transactions & ACID Properties Explained

A transaction groups several statements into one all-or-nothing unit. Learn BEGIN/COMMIT/ROLLBACK, what the four ACID guarantees actually mean, and how isolation levels prevent anomalies.

A transaction bundles several SQL statements into a single all-or-nothing unit of work. The classic example is a bank transfer: debit one account, credit another. If the second step fails, you must not be left with money debited and never credited. Transactions guarantee exactly that. Keep the SQL cheat sheet open as you read.

What Is a Transaction?

A transaction is a sequence of statements that either all succeed and are made permanent together, or all fail and leave the database exactly as it was. There is no half-way state visible to anyone else. This is what lets you make complex changes without risking a corrupt, partially-updated database.

BEGIN, COMMIT & ROLLBACK

Start a transaction with BEGIN, make your changes, then either COMMIT to make them permanent or ROLLBACK to discard them all. The transfer below either moves the money completely or not at all.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- both updates become permanent together

-- If anything looks wrong before COMMIT:
-- ROLLBACK;  -- discards every change since BEGIN

The Four ACID Properties

ACID is the set of guarantees a reliable transactional database makes. They are worth knowing precisely — they come up in interviews and they explain why databases behave the way they do under concurrency and crashes.

Atomicity — all statements in the transaction happen, or none do; there is no partial result
Consistency — a transaction moves the database from one valid state to another, never breaking constraints like foreign keys or checks
Isolation — concurrent transactions do not see each other’s uncommitted changes; the result is as if they ran one after another
Durability — once a transaction commits, its changes survive a crash or power loss

Savepoints

A savepoint is a checkpoint inside a transaction. You can roll back to a savepoint to undo part of the work without abandoning the whole transaction — useful when one optional step fails but the rest should still commit.

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 50);
  SAVEPOINT after_order;
  INSERT INTO order_notes (order_id, note) VALUES (999, 'gift');
  -- that note failed? undo just it, keep the order
  ROLLBACK TO SAVEPOINT after_order;
COMMIT;

Isolation Levels

Perfect isolation is expensive, so SQL defines levels that trade strictness for concurrency. Lower levels allow certain anomalies: a dirty read (seeing uncommitted data), a non-repeatable read (a row changes between two reads), and a phantom read (new rows appear between two reads).

READ COMMITTED — sees only committed data; PostgreSQL’s default and a sensible choice for most apps
REPEATABLE READ — the same query returns the same rows for the whole transaction
SERIALIZABLE — the strictest level; transactions behave as if run one at a time

-- Bump isolation for a transaction that must be consistent
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- ... your statements ...
COMMIT;

Common Mistakes & Tips

Leaving a transaction open (no COMMIT or ROLLBACK), which holds locks and blocks others
Doing slow work — network calls, user prompts — while a transaction is open
Assuming a single UPDATE needs an explicit transaction; statements are already atomic on their own
Wrapping unrelated changes in one giant transaction, increasing lock contention
Raising the isolation level without handling the serialization failures it can produce

Transactions are what make a database trustworthy under failure and concurrency. Practice BEGIN/COMMIT/ROLLBACK in the writable sandbox to see all-or-nothing behaviour first-hand.