Transactions

A transaction is a group of SQL statements that execute as a single unit. Either all of them succeed, or none of them do. Without transactions, a server crash or network failure in the middle of a multi-step operation — like a bank transfer — could leave your data in an inconsistent state. Transactions are what make relational databases reliable for the operations that matter most.

ACID Properties

Every PostgreSQL transaction guarantees four properties that together define what “reliable” means for a database:

• Atomicity — all statements in a transaction commit together or roll back together. There is no partial success.
• Consistency — a transaction brings the database from one valid state to another. Constraints, foreign keys, and rules are enforced at commit time.
• Isolation — concurrent transactions don’t interfere with each other. Each transaction sees a consistent snapshot of the data.
• Durability — once a transaction commits, the changes survive crashes and power failures because PostgreSQL writes to the WAL (Write-Ahead Log) before confirming success.

BEGIN, COMMIT, and ROLLBACK

Wrap related statements in a transaction block with BEGIN and COMMIT. If anything goes wrong before COMMIT, issue a ROLLBACK to undo every statement back to the BEGIN — leaving the database exactly as it was before the transaction started.

-- Both updates succeed together, or neither applies
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- debit sender
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- credit recipient

COMMIT;

-- If a problem is detected before COMMIT, roll back to undo everything
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Discover the recipient account doesn't exist — abort cleanly
ROLLBACK;  -- The debit is reversed; no money lost

Autocommit Behavior

By default, PostgreSQL runs every statement in its own implicit transaction — this is autocommit mode. A bare UPDATE without a BEGIN is automatically committed the moment it succeeds. Wrapping statements in BEGIN...COMMIT groups them so they succeed or fail together.

Most client libraries (psycopg2, node-postgres, JDBC) expose an autocommit setting. Turning it off means every statement is part of an explicit transaction until you call commit() or rollback() on the connection object.

Savepoints

Savepoints let you mark a point inside a transaction that you can roll back to without abandoning the entire transaction. This is useful when you want to attempt an operation that might fail — like inserting a row that might violate a constraint — and recover from that failure without losing earlier work in the same transaction.

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (7, 199.99);
SAVEPOINT after_order;  -- mark a recovery point

INSERT INTO order_items (order_id, product_id, qty) VALUES (currval('orders_id_seq'), 999, 1);
-- product_id 999 doesn't exist, triggers a foreign key violation

ROLLBACK TO SAVEPOINT after_order;
-- The order row is still intact; only the failed item insert was undone

INSERT INTO order_items (order_id, product_id, qty) VALUES (currval('orders_id_seq'), 12, 1);

COMMIT;

Release a savepoint when you no longer need the rollback point (frees internal resources):

RELEASE SAVEPOINT after_order;

Isolation Levels

Different applications have different tolerance for seeing data that another concurrent transaction is in the middle of changing. PostgreSQL supports four isolation levels to let you tune this tradeoff between consistency and throughput.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- statements
COMMIT;

• Dirty read — reading uncommitted changes from another transaction. PostgreSQL never allows this, even at READ COMMITTED.
• Non-repeatable read — reading the same row twice within a transaction and getting different values because another transaction committed a change in between.
• Phantom read — running the same range query twice and getting different sets of rows because another transaction inserted or deleted rows in between.

SERIALIZABLE makes transactions behave as if they ran one at a time. PostgreSQL uses Serializable Snapshot Isolation (SSI), which detects conflicts and aborts one transaction rather than using heavy locking.

Practical Example: Bank Transfer

The bank transfer is the canonical transaction example because it illustrates all four ACID properties: the two updates must be atomic, they must leave balances consistent, they must be isolated from concurrent transfers, and once committed the change must survive a crash.

BEGIN;

-- Lock both rows to prevent concurrent modifications to the same accounts
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

-- Check sufficient funds before debiting
DO $$
DECLARE
  src_balance NUMERIC;
BEGIN
  SELECT balance INTO src_balance FROM accounts WHERE id = 1;
  IF src_balance < 500 THEN
    RAISE EXCEPTION 'Insufficient funds';
  END IF;
END $$;

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

-- Record the transfer for auditing purposes
INSERT INTO transfer_log (from_id, to_id, amount, transferred_at)
VALUES (1, 2, 500, NOW());

COMMIT;

The FOR UPDATE locks prevent two concurrent transfers from both reading the same balance before either has written its debit.

Advisory Locks

Advisory locks are application-level locks that PostgreSQL manages but doesn’t enforce automatically. They are useful for coordinating work between application processes — for example, ensuring only one worker processes a given job at a time, without the overhead of modifying the row being protected.

-- Try to acquire lock with key 12345 (non-blocking: returns false if already held)
SELECT pg_try_advisory_lock(12345);

-- Do the work if lock was acquired...

-- Release when done
SELECT pg_advisory_unlock(12345);

Advisory locks acquired within a transaction are automatically released at COMMIT or ROLLBACK. Session-level advisory locks persist until explicitly released or the connection closes.

Deadlock Avoidance

A deadlock happens when transaction A holds a lock that transaction B wants, and transaction B holds a lock that transaction A wants. PostgreSQL detects the cycle and aborts one transaction with an error. The reliable prevention strategy is to always lock resources in the same order across all transactions.

-- Always lock the lower account ID first so two concurrent transfers
-- involving the same two accounts can never deadlock each other
SELECT * FROM accounts WHERE id = LEAST(1, 2) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST(1, 2) FOR UPDATE;