Transactions

Transactions ensure that a group of operations either all succeed or all fail. They are the foundation of data integrity in PostgreSQL. Without transactions, a partially completed operation could leave your database in an inconsistent state.

Why Transactions Matter

Imagine transferring money between two bank accounts. You need to subtract from one account and add to another. What happens if the system crashes after the subtraction but before the addition? Without transactions, you would lose money from thin air.

Transactions solve this by wrapping multiple operations in a single atomic unit:

BEGIN;

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

COMMIT;

Either both updates succeed (COMMIT) or neither takes effect (ROLLBACK).

COMMIT and ROLLBACK

BEGIN starts a transaction. Everything after BEGIN is part of that transaction.

COMMIT saves all changes made during the transaction. After committing, the changes are permanent.

ROLLBACK undoes all changes made during the transaction. It is like hitting the undo button:

BEGIN;

INSERT INTO students (first_name, last_name) VALUES ('Test', 'Student');
-- Oops, that was a mistake
ROLLBACK;

-- The student was never inserted

SAVEPOINT

SAVEPOINT lets you create intermediate checkpoints within a transaction:

BEGIN;

INSERT INTO students (first_name, last_name) VALUES ('Alice', 'Smith');
SAVEPOINT after_alice;

INSERT INTO students (first_name, last_name) VALUES ('Bob', 'Johnson');
-- Something went wrong with Bob
ROLLBACK TO after_alice;

-- Alice is still there, Bob is gone
COMMIT;

This is useful for complex transactions where some operations might fail but you do not want to undo everything.

Transaction Isolation

PostgreSQL supports different isolation levels that control how transactions interact with each other:

• READ COMMITTED — The default. Each statement sees only data committed before it started.
• REPEATABLE READ — The entire transaction sees a consistent snapshot of the database.
• SERIALIZABLE — The strictest level. Transactions behave as if they ran one after another.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Your transaction here

COMMIT;

Autocommit

By default, PostgreSQL runs each statement in its own transaction (autocommit mode). This means every INSERT, UPDATE, or DELETE is immediately committed unless you explicitly wrap it in BEGIN/COMMIT.

For single statements, autocommit is fine. For multi-step operations, always use explicit transactions.

Best Practices

Keep transactions short. Do not hold locks longer than necessary. Long transactions can cause performance issues and blocking for other users.

Always handle errors in application code. If a transaction fails, make sure to ROLLBACK before starting a new one. Leaked transactions can lock resources and degrade performance.