Transactions

A transaction groups statements so the database applies all of them or none of them.

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

If the second update fails, ROLLBACK leaves the first as if it never happened. Money does not evaporate between two statements.

BEGIN is what PostgreSQL and MySQL accept; the standard spelling, START TRANSACTION, works in both, and SQL Server writes BEGIN TRANSACTION.

ACID, in one line each

• Atomic — the whole transaction applies, or none of it does.
• Consistent — constraints hold before and after; a transaction cannot leave the database in a state the schema forbids.
• Isolated — concurrent transactions do not see each other's half-finished work.
• Durable — once COMMIT returns, the change survives the power going out.

What isolation is actually for

Isolation levels are defined by the anomalies they forbid:

• Dirty read — you see another transaction's uncommitted change. Every level above READ UNCOMMITTED forbids this, and PostgreSQL never allows it.
• Non-repeatable read — you read a row twice in one transaction and get two different values, because somebody committed in between. REPEATABLE READ forbids it.
• Phantom read — you run the same WHERE twice and the second run has extra rows. SERIALIZABLE forbids it.

PostgreSQL defaults to READ COMMITTED: each statement sees a snapshot taken when that statement started. Two statements in the same transaction can therefore disagree, which surprises people who assumed the transaction froze the world.

Try it

1CREATE TABLE accounts (2  id      int PRIMARY KEY,3  owner   text NOT NULL,4  balance int  NOT NULL CHECK (balance >= 0)5);6INSERT INTO accounts VALUES (1, 'Nino', 300), (2, 'Ana', 50);

1BEGIN;2UPDATE accounts SET balance = balance - 100 WHERE id = 1;3UPDATE accounts SET balance = balance + 100 WHERE id = 2;4ROLLBACK;5 6SELECT owner, balance FROM accounts ORDER BY id;

One statement is already atomic

A single UPDATE changes all the rows it matches or none of them, and everything it reads — subqueries included — sees the data as it was when the statement started. Split a check and two changes across separate statements and each one sees what the previous one left behind — inside a transaction or not:

1CREATE TABLE accounts (2  id      int PRIMARY KEY,3  owner   text NOT NULL,4  balance int  NOT NULL5);6INSERT INTO accounts VALUES (1, 'Nino', 150), (2, 'Ana', 50);

1-- Move 100 from Nino to Ana, if Nino can afford it.2UPDATE accounts SET balance = balance - 1003WHERE id = 1 AND balance >= 100;4 5UPDATE accounts SET balance = balance + 1006WHERE id = 27  AND (SELECT balance FROM accounts WHERE id = 1) >= 100;8 9SELECT owner, balance FROM accounts ORDER BY id;

The first statement took 100 from Nino. By the time the second one checked his balance it was 50, so Ana was never credited and 100 simply vanished. Either check once, up front, inside a transaction and roll back if the check fails, or make the check and both changes one statement.

The practical warning

A transaction holds locks and a database connection for as long as it is open. Doing anything slow inside one — an HTTP call to a payment provider, a large file write, waiting on a user — holds that connection while it happens, and a pool of twenty connections drains in seconds under load. Keep transactions short and keep network calls outside them.

Common mistakes

• Read, compute in the application, write back. Two requests both read a balance of 300, both subtract 100 in code, both write 200: one withdrawal is lost. Let the database do the arithmetic (SET balance = balance - 100), or lock the row you read with SELECT ... FOR UPDATE.
• Assuming the default level freezes the data. Under READ COMMITTED each statement takes a fresh snapshot, so two reads in one transaction can see different committed data.
• Carrying on after an error. In PostgreSQL a failed statement aborts the whole transaction; every later statement fails with "current transaction is aborted, commands ignored until end of transaction block" until you ROLLBACK. Code that swallows the first error and continues gets a string of confusing failures instead.

What interviewers ask

• What does each letter of ACID guarantee? All-or-nothing; constraints hold at commit; concurrent transactions do not see each other's uncommitted work; committed data survives a crash.
• Name the read anomalies and the level that prevents each. Dirty read (READ COMMITTED), non-repeatable read (), phantom ( in the standard).