What Is a Transaction in SQL?

A transaction is a group of statements the database treats as one unit of work. Either all of them take effect or none of them do, and while the transaction is open nobody else sees a half-finished version of the data. That is the whole idea: the unit of atomicity is the transaction, not the statement.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  owner VARCHAR(120) NOT NULL,
  balance_cents INTEGER NOT NULL
);

INSERT INTO accounts (id, owner, balance_cents) VALUES
  (1, 'Ada Lovelace', 5000),
  (2, 'Grace Hopper', 2000);

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 1500 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 1500 WHERE id = 2;
SELECT id, owner, balance_cents FROM accounts ORDER BY id;
COMMIT;

SELECT id, owner, balance_cents FROM accounts ORDER BY id;

Both SELECTs return the same two rows: Ada with 3500 cents and Grace with 3500 cents. The SELECT inside the transaction is what shows the intermediate state — the transfer is complete but not yet durable — and the SELECT after the COMMIT shows the same thing, now committed.

Four properties are usually associated with a transaction, and they are worth stating separately. Atomicity means the statements succeed or fail together. Consistency means the data ends up satisfying its constraints. Isolation means concurrent transactions do not see each other's intermediate work. Durability means a committed transaction survives a crash.

The isolation level is the part that is usually under-specified. Read Committed is the default in most engines: a transaction sees rows that were committed before it started, and it can be blocked by a writer holding a lock on the same row. Repeatable Read and Serializable give stronger guarantees — a stable snapshot, or full serialisability — at the cost of more retries and more deadlocks. In PostgreSQL, Repeatable Read can fail with a serialization error that the application has to retry, and that is the correct design rather than a bug.

The practical rule is that a transaction should be as short as possible. Long transactions hold locks, block other writers, and in PostgreSQL prevent vacuum from cleaning up rows that nobody can see any more. Never make a transaction span a network call to a third-party service, because the lock is held while you wait for a response that may never come.

The rollback is what proves the atomicity. If the data returns to exactly its previous state, then the statements inside the transaction were never observable outside it.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  owner VARCHAR(120) NOT NULL,
  balance_cents INTEGER NOT NULL
);

INSERT INTO accounts (id, owner, balance_cents) VALUES
  (1, 'Ada Lovelace', 5000),
  (2, 'Grace Hopper', 2000);

SELECT id, balance_cents AS before_transfer FROM accounts ORDER BY id;

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 1500 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 1500 WHERE id = 2;
SELECT id, balance_cents AS inside_transaction FROM accounts ORDER BY id;

ROLLBACK;

SELECT id, balance_cents AS after_rollback FROM accounts ORDER BY id;

Three result sets come back. Before the transaction, Ada has 5000 and Grace has 2000. Inside the transaction, both have 3500 — the transfer has happened as far as this connection is concerned. After the ROLLBACK, Ada has 5000 and Grace has 2000 again, byte for byte as they were.

The middle result set is the interesting one and it is worth being precise about what it shows. That state was visible to this connection and, in a real system, to nobody else. Other transactions reading the accounts table either saw the old values or were blocked — they could never see a state where money had left Ada's account but had not yet arrived in Grace's. That invisibility is the isolation property, and it is what makes the transfer safe to run concurrently with other transfers.

Note also what the rollback did not undo. If the transaction had inserted a row into an audit table, that insert would have been rolled back too, because it happened inside the same transaction. That is usually what you want, and it is occasionally a surprise: an audit trail written inside a transaction that later rolls back leaves no trace that the attempt happened.

The general shape to take away: put the unit of work inside BEGIN and COMMIT, read the intermediate state only if you need it, and use ROLLBACK as a deliberate abort rather than as an error path you discover afterwards.