Transactions and Savepoints

PostgreSQL fully supports ACID transactions. Every statement that is not explicitly wrapped in a transaction runs in an implicit single-statement transaction. You begin an explicit transaction with BEGIN (or START TRANSACTION), commit it with COMMIT, and roll it back with ROLLBACK. Savepoints allow partial rollbacks within a transaction — you can roll back to a named savepoint without aborting the entire transaction, which is useful for error handling in loops. PostgreSQL uses MVCC (multiversion concurrency control) for isolation, meaning readers never block writers and writers never block readers. The default isolation level is READ COMMITTED; REPEATABLE READ and SERIALIZABLE provide stronger guarantees at the cost of potential serialisation failures that require retry logic.

Example

-- Explicit transaction
BEGIN;

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

  -- Check for problems before committing
  DO $$
  BEGIN
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;
  END $$;

COMMIT;

-- ROLLBACK on error
BEGIN;
  DELETE FROM orders WHERE id = 99;
  -- Whoops — wrong query
ROLLBACK;

-- Savepoints — partial rollback
BEGIN;

  INSERT INTO orders (user_id, total) VALUES (1, 100) RETURNING id;
  SAVEPOINT after_order;

  INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 99, 5);
  -- This item caused a problem
  ROLLBACK TO SAVEPOINT after_order;

  -- Continue with a different item
  INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 12, 5);

COMMIT;

-- Serializable isolation for strict consistency
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE id = 1;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned