Transactions

A transaction is a sequence of SQL statements that PostgreSQL treats as a single unit of work. Either all statements succeed together, or none of them take effect. This all-or-nothing guarantee is the foundation of data integrity in relational databases.

ACID Properties

PostgreSQL transactions are fully ACID-compliant:

• Atomicity — All statements in the transaction succeed, or all are rolled back.
• Consistency — A transaction brings the database from one valid state to another.
• Isolation — Concurrent transactions do not see each other's intermediate state.
• Durability — Once committed, changes survive even a server crash.

Basic Transaction Syntax

BEGIN;

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

COMMIT;

If anything goes wrong before COMMIT, call ROLLBACK to undo all changes:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Something went wrong
ROLLBACK;

Savepoints

Savepoints let you roll back to a specific point within a transaction without abandoning the entire transaction:

BEGIN;

INSERT INTO orders (user_id, total) VALUES (42, 99.99);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id) VALUES (currval('orders_id_seq'), 999);
-- Product 999 does not exist — roll back to savepoint
ROLLBACK TO SAVEPOINT after_order;

-- The order row is still inserted; only the bad item is rolled back
COMMIT;

Isolation Levels

PostgreSQL supports four isolation levels that control how much one transaction can see of concurrent transactions:

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ COMMITTED (default)	No	Possible	Possible
REPEATABLE READ	No	No	No (in PG)
SERIALIZABLE	No	No	No

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Your statements here
COMMIT;

Autocommit

By default, every single SQL statement outside an explicit BEGIN block runs in its own implicit transaction and is committed immediately. This is called autocommit mode. Always use explicit transactions when multiple statements must succeed or fail together.

Understanding transactions ensures your application never leaves the database in a partially updated, inconsistent state.