Transactions in PostgreSQL

Transactions group statements into atomic units—ACID guarantees keep balances and inventory consistent when concurrent sessions write data.

BEGIN, COMMIT, ROLLBACK

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

-- On error:
ROLLBACK;

Practice: Run on a local Postgres instance you own. Avoid changing production cluster settings.

Savepoints

BEGIN;
INSERT INTO audit_log (msg) VALUES ('start');
SAVEPOINT sp1;
INSERT INTO risky (data) VALUES ('maybe bad');
ROLLBACK TO sp1;
COMMIT;

App integration

Django transaction.atomic() and Python psycopg connections use the same BEGIN/COMMIT semantics—one transaction per request is a common pattern.

Important interview questions and answers

1. Q: Autocommit default?
   A: Each statement commits alone unless wrapped in BEGIN.
2. Q: DDL transactional?
   A: Most DDL can run in transactions in Postgres—unlike some MySQL engines historically.

Self-check

1. What happens to changes after ROLLBACK?
2. What is a savepoint for?

Tip: Django transaction.atomic() maps to the same BEGIN/COMMIT semantics.