PostgreSQL Transactions

A transaction groups statements so they either all succeed or all fail.

BEGIN;

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

COMMIT;

If anything fails between BEGIN and COMMIT, ROLLBACK undoes everything — money never vanishes halfway through a transfer.

BEGIN;
DELETE FROM books WHERE price < 100;
-- check the result, then decide
ROLLBACK;   -- or COMMIT;

Running a risky DELETE inside an explicit transaction, checking the row count, then committing is a habit worth building.

ACID

• Atomicity — all or nothing.
• Consistency — constraints always hold.
• Isolation — concurrent transactions don't see each other's half-finished work.
• Durability — once committed, it survives a crash.

Savepoints

Partial rollback within a transaction:

BEGIN;
INSERT INTO books (title) VALUES ('First');

SAVEPOINT sp1;
INSERT INTO books (title) VALUES ('Second');
ROLLBACK TO sp1;         -- undoes only "Second"

COMMIT;                   -- "First" is saved

Isolation levels

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

SERIALIZABLE is the safest and can fail with a serialization error — your application must be prepared to retry the transaction.

Row locking

The read-modify-write race is a classic bug:

-- two sessions both read stock = 1 and both sell it
SELECT stock FROM books WHERE id = 1;
UPDATE books SET stock = stock - 1 WHERE id = 1;

Lock the row while you work:

BEGIN;
SELECT stock FROM books WHERE id = 1 FOR UPDATE;   -- blocks other writers
UPDATE books SET stock = stock - 1 WHERE id = 1;
COMMIT;

Better still, let the database do the arithmetic atomically and let a constraint enforce the rule:

UPDATE books SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- rowcount 0 means it was out of stock

Deadlocks

Two transactions each holding what the other wants. Postgres detects this and kills one with a deadlock error. Prevent it by always locking rows in a consistent order (e.g. ascending id).

Keep transactions short

A long-running transaction holds locks and prevents cleanup of old row versions, which bloats tables. Never leave one open while waiting for user input or an external API call.