Transactions & ACID

A transaction is a group of statements that succeed or fail as one unit.

ACID

Property	Meaning
Atomicity	All statements apply, or none
Consistency	Constraints hold before and after
Isolation	Concurrent transactions do not see each other's unfinished work
Durability	Once committed, changes survive a crash

The basics

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

BEGIN;
  DELETE FROM students WHERE department = 'CSE';
  -- 412 rows?! not what I expected
ROLLBACK;

BEGIN and START TRANSACTION are equivalent. By default psql and most drivers run in autocommit mode, so each statement is its own transaction until you open one explicitly.

DDL is transactional

This is a major practical advantage over MySQL. Schema changes roll back:

BEGIN;
  CREATE TABLE new_thing (id int);
  ALTER TABLE students ADD COLUMN city text;
  -- migration step 3 fails
ROLLBACK;   -- the table and the column never existed

It means a failed migration leaves the database exactly as it was, which is why Postgres migrations are so much less stressful.

Savepoints

BEGIN;
  INSERT INTO orders (customer_id, total) VALUES (1, 2500) RETURNING id;
  SAVEPOINT order_created;

  INSERT INTO order_items (order_id, product_id, qty) VALUES (currval('orders_id_seq'), 9, 2);
  -- that product is discontinued
  ROLLBACK TO SAVEPOINT order_created;

  INSERT INTO order_items (order_id, product_id, qty) VALUES (currval('orders_id_seq'), 12, 2);
COMMIT;

The aborted-transaction state

An important difference from MySQL: once any statement in a transaction raises an error, Postgres puts the whole transaction into an aborted state and refuses everything else until you roll back:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

Recover with ROLLBACK, or protect individual statements with a savepoint:

BEGIN;
  SAVEPOINT sp1;
  INSERT INTO students (roll_no) VALUES ('duplicate');   -- fails
  ROLLBACK TO sp1;                                       -- transaction is usable again
  INSERT INTO students (roll_no) VALUES ('CSE-999');
COMMIT;

Transaction modes

BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN READ ONLY;
BEGIN DEFERRABLE READ ONLY ISOLATION LEVEL SERIALIZABLE;   -- ideal for long reports

A read-only transaction lets Postgres skip some bookkeeping and makes your intent explicit.

Durability and WAL

Every change is written to the write-ahead log before the data files, which is what makes crash recovery, replication and point-in-time recovery possible.

SHOW synchronous_commit;   -- on

• on (default) — wait for the WAL to reach durable storage. Fully safe.
• off — return immediately; a crash can lose a fraction of a second of committed transactions, but the database is never corrupted.

synchronous_commit = off is a legitimate, dramatic speed-up for bulk loading or non-critical data, and it can even be set per transaction.

Practical advice

Keep transactions short. A long-running transaction holds locks and, more importantly in Postgres, prevents autovacuum from cleaning up dead rows anywhere in the database — the usual cause of mysterious table bloat. Never leave a transaction open across a user interaction or an external API call.

SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY xact_start;