Transactions, Concurrency, and LockingLesson 5.1

How database transactions work in PostgreSQL: ACID explained

BEGIN COMMIT ROLLBACK, ACID properties, atomicity isolation consistency durability, transaction blocks, SAVEPOINT, autocommit

Transactions and ACID

A transaction is a unit of work that either fully succeeds or fully fails. ACID guarantees are what make databases reliable for business data.

Transaction block

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;  -- both updates apply, or:
ROLLBACK; -- neither applies

ACID properties

Atomicity: all operations in a transaction succeed or none do. Consistency: the database moves from one valid state to another. Isolation: concurrent transactions do not see each other's partial changes. Durability: committed data survives a crash.

SAVEPOINT

BEGIN;
INSERT INTO orders ...;
SAVEPOINT after_order;
INSERT INTO payments ...;  -- this fails
ROLLBACK TO SAVEPOINT after_order;
-- orders insert is still pending
COMMIT;

SAVEPOINTs allow partial rollback within a transaction. Useful when you want to retry a sub-operation without abandoning the whole transaction.

PostgreSQL is always in autocommit mode outside a transaction block — every statement is its own transaction. Use BEGIN explicitly whenever you need multiple statements to be atomic.