Transactions Basics

9 examples to get you started with PostgreSQL transactions - 6 basic and 3 intermediate.

Prerequisites

Sample tables with foreign keys to observe rollback behavior.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. Explicit Transaction

BEGIN starts a block; COMMIT makes it durable.

BEGIN;
UPDATE app.accounts SET name = 'Acme Corp' WHERE id = 1;
COMMIT;

• All statements in block share one snapshot
• COMMIT flushes WAL per synchronous_commit
• Clients should use connection pool transaction APIs

2. Rollback on Failure

ROLLBACK discards uncommitted work.

BEGIN;
UPDATE app.accounts SET name = 'Bad' WHERE id = 1;
ROLLBACK;
SELECT name FROM app.accounts WHERE id = 1;

• Use try/catch in app to ROLLBACK
• Savepoint for partial undo
• Do not leave failed transactions open

3. Savepoint

Nested rollback point.

BEGIN;
INSERT INTO app.accounts (name) VALUES ('Temp');
SAVEPOINT sp1;
UPDATE app.accounts SET name = 'Oops' WHERE name = 'Temp';
ROLLBACK TO sp1;
COMMIT;

• RELEASE SAVEPOINT drops the marker
• Too many savepoints add overhead
• ORMs may not expose savepoints

4. Constraint Violation Rolls Back Statement

Single statement failure in autocommit aborts that statement only.

INSERT INTO app.accounts (id, name) VALUES (1, 'dup'); -- fails if id=1 exists

• In explicit TX, error aborts TX until ROLLBACK
• Use SAVEPOINT for retry within TX
• Handle SQLSTATE in application

5. Read Your Writes

Same session sees committed changes.

BEGIN;
INSERT INTO app.accounts (name) VALUES ('NewCo') RETURNING id;
-- same TX sees row
COMMIT;

• Other sessions see row after commit
• Isolation level affects concurrent reads
• RETURNING avoids second round trip

6. Deferrable Constraints

Check FK at commit time.

SET CONSTRAINTS ALL DEFERRED;
BEGIN;
-- reorder inserts within TX
COMMIT;

• Only DEFERRABLE constraints participate
• Rare; prefer correct insert order
• Document when used in bulk loads

Intermediate Examples

7. Application Transaction Pattern

Pseudo-code shape for services.

-- SQL session
BEGIN;
-- business statements
COMMIT;

• One business operation per transaction when possible
• Avoid user think time inside TX
• Set statement_timeout and lock_timeout

8. Two-Phase Implication

Prepared transactions for external coordinators.

PREPARE TRANSACTION 'txn_abc';
-- later: COMMIT PREPARED 'txn_abc';

• Rare outside XA integrations
• Prepared xacts block vacuum if abandoned
• Monitor pg_prepared_xacts

9. Observe Open Transactions

pg_stat_activity state.

SELECT pid, xact_start, state, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction','active');

• idle in transaction is a top incident cause
• idle_in_transaction_session_timeout mitigates
• Kill backends only with ops playbook

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.