PostgreSQL Transactions

A transaction is a group of SQL statements treated as a single unit. Either all statements in the group succeed together, or none of them take effect. This all-or-nothing behavior protects data integrity when multiple related changes must be made at the same time.

The Classic Bank Transfer Example

Imagine transferring money between two bank accounts:

1. Deduct $500 from Account A
2. Add $500 to Account B

If step 1 succeeds but the system crashes before step 2, $500 disappears. A transaction prevents this: either both steps complete, or neither does. If anything goes wrong between steps, the entire operation is reversed.

ACID Properties

Transactions in PostgreSQL follow the ACID model:

Transaction Control Commands

Basic Transaction: BEGIN and COMMIT

BEGIN;

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

COMMIT;

Both updates are saved together when COMMIT is executed. Before COMMIT, no other session can see these changes (depending on the isolation level).

Rolling Back a Transaction

BEGIN;

DELETE FROM orders WHERE customer_id = 10;

-- Realized this was a mistake
ROLLBACK;

ROLLBACK undoes the DELETE as if it never happened. This is an invaluable safety net when testing destructive operations.

Auto-Commit Behavior

In psql, every statement that is not inside an explicit BEGIN/COMMIT block is automatically wrapped in its own transaction and committed immediately. This is called auto-commit mode. To group multiple statements, an explicit BEGIN must be started.

Savepoints

A savepoint marks a point within a transaction to which operations can be partially rolled back without canceling the entire transaction.

BEGIN;

INSERT INTO orders (customer_id, product) VALUES (1, 'Laptop');

SAVEPOINT after_laptop;

INSERT INTO orders (customer_id, product) VALUES (1, 'Invalid Product');

-- Something went wrong with the second insert
ROLLBACK TO after_laptop;

-- The laptop insert is still intact; continue with the transaction
INSERT INTO orders (customer_id, product) VALUES (1, 'Mouse');

COMMIT;

The laptop and mouse inserts are committed. The "Invalid Product" insert was rolled back to the savepoint. Savepoints allow fine-grained error recovery within a long transaction.

Transaction Isolation Levels

Isolation levels control how much one transaction can see of other concurrent transactions' in-progress changes.

Read Uncommitted

Not fully supported by PostgreSQL — it behaves like Read Committed instead.

Read Committed (Default)

Each statement within the transaction sees only data that was committed before that statement began. This is the default in PostgreSQL.

Repeatable Read

All statements in the transaction see the same snapshot of data from the transaction's start. Prevents "non-repeatable reads" where the same row returns different values when read twice.

Serializable

The strictest level. Transactions behave as if they ran one after another in a serial order. Prevents all concurrency anomalies but may reduce throughput on high-concurrency workloads.

-- Set isolation level for the current transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;
-- ... other operations ...
SELECT balance FROM accounts WHERE id = 1;
-- Both SELECT statements return the same value

COMMIT;

Handling Errors in Transactions

If any statement in a transaction causes an error, PostgreSQL marks the transaction as "aborted". No further statements (except ROLLBACK) can be executed until the transaction is rolled back.

BEGIN;

INSERT INTO students (name, age) VALUES ('Alice', 20);

-- This causes a duplicate key error
INSERT INTO students (id, name, age) VALUES (1, 'Duplicate', 25);

-- Transaction is now in an aborted state
-- This SELECT will fail too
SELECT * FROM students;

ROLLBACK;  -- Must rollback to continue

Transactions in PL/pgSQL Functions vs Procedures

Regular PL/pgSQL functions run within the caller's transaction — they cannot issue COMMIT or ROLLBACK. Stored procedures (introduced in PostgreSQL 11) can manage their own transactions:

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account INT,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

CALL transfer_funds(1, 2, 500.00);

The EXCEPTION block catches any error, rolls back the transaction, and re-raises the error.

Key Points

• A transaction groups multiple SQL statements into an all-or-nothing unit.
• BEGIN starts a transaction; COMMIT saves changes; ROLLBACK cancels them.
• In psql, each statement is auto-committed unless wrapped in BEGIN/COMMIT.
• Savepoints allow partial rollbacks within a transaction without losing prior changes.
• The default isolation level is Read Committed; higher levels provide stronger data consistency guarantees.
• An error inside a transaction puts it in an aborted state — ROLLBACK is required before continuing.
• Stored procedures (not functions) can control transactions with COMMIT and ROLLBACK internally.