Transactions Basics

Introduction

A transaction groups SQL statements into one atomic unit—either all succeed or all roll back. That is how transfers, checkout, and multi-step migrations avoid half-updated state. This chapter covers ACID, MVCC intuition, BEGIN/COMMIT/ROLLBACK, SAVEPOINT, and how autocommit behaves in psql and application drivers. Compare with MySQL transactions.

Prerequisites

Constraints and Keys
Tables in your practice database (InnoDB-style integrity applies to PostgreSQL tables by default)

ACID Overview

Property	Meaning
Atomicity	All or nothing
Consistency	Constraints hold after commit
Isolation	Concurrent sessions do not corrupt each other
Durability	Committed data survives crash (WAL flush)

MVCC Intuition

PostgreSQL uses Multi-Version Concurrency Control—readers do not block writers and writers do not block readers under normal SELECTs. Each transaction sees a snapshot of committed data as of a point in time (exact rules depend on isolation level—Isolation and Locking).

Code explanation:

UPDATE creates new row versions; old versions remain until VACUUM reclaims space—Performance, VACUUM, and Monitoring
Unlike MySQL InnoDB clustered PK, PostgreSQL stores rows in heap order with indexes pointing to row locations

autocommit in psql

By default, each statement auto-commits on success:

sql

SHOW autocommit;
-- on

Single insert commits immediately.

Turn off for manual control (session only):

sql

\set autocommit off

Or in SQL (works in psql and many drivers):

sql

BEGIN;
-- statements...
COMMIT;

Tip

Application drivers

psycopg, JDBC, and ORMs usually wrap each statement in autocommit unless you call begin(). Explicit transactions in app code match BEGIN in psql.

Setup: Accounts Demo

sql

CREATE TABLE accounts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  balance NUMERIC(12, 2) NOT NULL DEFAULT 0
);
 
INSERT INTO accounts (name, balance) VALUES
  ('Alice', 1000.00),
  ('Bob', 500.00);

BEGIN and COMMIT

Successful transfer:

sql

BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
 
COMMIT;

Verify:

sql

SELECT name, balance FROM accounts ORDER BY name;

Code explanation:

BEGIN, START TRANSACTION, and BEGIN TRANSACTION are equivalent in PostgreSQL
Other sessions typically see old balances until COMMIT (default READ COMMITTED)

ROLLBACK

sql

BEGIN;
 
UPDATE accounts SET balance = balance - 50 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 50 WHERE name = 'Bob';
 
ROLLBACK;

Balances unchanged from before the transaction.

ROLLBACK on Error

PostgreSQL aborts the transaction on many errors—you must ROLLBACK before new commands:

sql

BEGIN;
 
UPDATE accounts SET balance = balance - 2000 WHERE name = 'Alice';
 
-- Simulate insufficient funds check
DO $$
BEGIN
  IF (SELECT balance FROM accounts WHERE name = 'Alice') < 0 THEN
    RAISE EXCEPTION 'Insufficient funds';
  END IF;
END $$;

If an error occurs:

sql

ROLLBACK;

Apps should validate before commit; SELECT ... FOR UPDATE locks rows inside the transaction—see Isolation and Locking.

SAVEPOINT

Partial rollback within a transaction:

sql

Code explanation:

ROLLBACK TO sp2 undoes work after sp2 but keeps changes before sp1
RELEASE SAVEPOINT sp1 removes a savepoint when no longer needed

Transaction and Constraints

Constraints are checked at statement end by default; deferrable FKs wait until COMMIT—Constraints and Keys.

sql

BEGIN;
 
INSERT INTO accounts (name, balance) VALUES ('Carol', 200.00);
INSERT INTO accounts (name, balance) VALUES ('Carol', 300.00);  -- fails UNIQUE
 
-- Transaction is now aborted until ROLLBACK
ROLLBACK;

Read-Only Transactions

Heavy reporting can mark a transaction read-only:

sql

BEGIN TRANSACTION READ ONLY;
 
SELECT name, balance FROM accounts;
 
COMMIT;

Hints to the planner and prevents accidental writes in long reports.

Common Patterns

Pattern	SQL
Single unit of work	`BEGIN;` … `COMMIT;`
Cancel everything	`ROLLBACK;`
Try step, undo step	`SAVEPOINT` / `ROLLBACK TO`
Read-only report	`BEGIN READ ONLY;`

FAQ

BEGIN vs START TRANSACTION?

Same in PostgreSQL. psql and docs use BEGIN frequently.

What happens if I disconnect mid-transaction?

Uncommitted work is rolled back when the session ends.

autocommit off and I forget COMMIT?

The session holds locks and old row versions—bad for production. Always COMMIT or ROLLBACK.

Does every statement need a transaction?

No—single-statement autocommit is fine for one-off fixes. Multi-step business logic should use explicit transactions.

PostgreSQL vs MySQL transactions?

Both are ACID. PostgreSQL default isolation is READ COMMITTED; MySQL InnoDB default is REPEATABLE READ—next chapter.

ORM and transactions?

Frameworks call commit() / rollback() on a connection or session—same semantics as COMMIT / ROLLBACK.