TopolearnAny change spanning more than one statement, like moving stock or money, is wrong without a transaction.
A transaction groups statements into one all-or-nothing unit. Placing an order means inserting the order, inserting its items and decrementing stock; if the server crashes halfway, you must not end up with an order and no items.
BEGIN;
INSERT INTO orders ...;
INSERT INTO order_items ...;
UPDATE products SET stock = stock - 1 WHERE id = 3;
COMMIT;COMMIT makes the changes permanent and visible to others; ROLLBACK discards them. The guarantees are summarised as ACID:
- Atomicity: all statements take effect, or none do.
- Consistency: constraints hold at commit.
- Isolation: concurrent transactions do not see each other's half-finished work.
- Durability: once
COMMITreturns, the data survives a crash.
Outside an explicit BEGIN, PostgreSQL runs each statement in its own transaction (autocommit). Most drivers follow this, though some, such as Python's psycopg, open a transaction implicitly and wait for you to commit.
In PostgreSQL, an error inside a transaction aborts it: every later statement fails with "current transaction is aborted" until you ROLLBACK. If you want to recover from an expected error partway through, use a savepoint: SAVEPOINT s1; ... ROLLBACK TO SAVEPOINT s1; undoes back to that point while keeping earlier work.
Keep transactions short. An open transaction holds locks that block other writers and, in PostgreSQL, prevents cleanup of old row versions. Never wait for user input or a slow HTTP call while a transaction is open; the state "idle in transaction" in pg_stat_activity is a classic production problem.
Example
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (4, 19.50)
RETURNING id; -- 106
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (106, 2, 1, 19.50);
COMMIT; -- both rows, or neither
-- Recovering from an expected failure with a savepoint
BEGIN;
UPDATE customers SET city = 'Berlin' WHERE id = 4;
SAVEPOINT before_email;
UPDATE customers SET email = 'ana@example.com' WHERE id = 4;
-- ERROR: duplicate key value violates unique constraint
ROLLBACK TO SAVEPOINT before_email; -- city change kept
COMMIT;
-- Without the savepoint, any statement after the error fails with:
-- ERROR: current transaction is aborted, commands ignored until end
-- of transaction blockRemember
- BEGIN ... COMMIT makes several statements atomic; ROLLBACK undoes them
- ACID: atomicity, consistency, isolation, durability
- In PostgreSQL an error aborts the whole transaction unless you use a savepoint
- Keep transactions short; never hold one open while waiting on users or networks
Watch out for
- Leaving connections 'idle in transaction', which holds locks and blocks vacuum.