ACID and Transaction Control

Learning Focus

Use this lesson to group changes into transactions in PostgreSQL so multi-step operations succeed or fail together, and to apply safe transaction control patterns (savepoints, timeouts, and verification queries).

Concept Overview

A transaction is a set of SQL statements treated as a single unit of work.

If the transaction commits, all changes become visible. If it rolls back, none of the changes remain.

PostgreSQL transactions follow ACID:

• Atomicity: all-or-nothing
• Consistency: constraints and invariants remain valid
• Isolation: concurrent transactions interact predictably (MVCC)
• Durability: committed changes persist (WAL)

Why is it important?

• Correctness: prevent partial updates (e.g., debit succeeds, credit fails)
• Safety: enforce invariants under concurrent writers
• Recoverability: failures don’t corrupt your logical state
• Operational predictability: short transactions reduce lock contention and vacuum pressure

Where does it fit?

Transactions are the foundation for:

• multi-table writes (orders + items + inventory)
• migrations/backfills
• locking patterns (SELECT ... FOR UPDATE)
• isolation level choices (READ COMMITTED, REPEATABLE READ, SERIALIZABLE)

---

Syntax & Rules

Core Syntax

BEGIN;

-- statements

COMMIT;
-- or
ROLLBACK;

Savepoints (partial rollback inside a transaction):

BEGIN;
SAVEPOINT sp1;

-- risky statements

ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

COMMIT;

Available Options / Parameters

Statement	Example	Meaning / Notes
Begin	BEGIN	start a transaction (same as START TRANSACTION)
Commit	COMMIT	persist changes
Rollback	ROLLBACK	discard changes
Savepoint	SAVEPOINT s	create a checkpoint
Rollback to savepoint	ROLLBACK TO SAVEPOINT s	undo to checkpoint
Release savepoint	RELEASE SAVEPOINT s	remove the checkpoint
Isolation level	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE	affects visibility/serialization behavior
Lock timeout	SET LOCAL lock_timeout = '2s'	abort if lock acquisition takes too long
Statement timeout	SET LOCAL statement_timeout = '30s'	abort long queries in this transaction

Key Rules and Considerations

• By default, PostgreSQL runs each statement in its own implicit transaction (autocommit behavior in most clients).
• Once an error occurs in a transaction, PostgreSQL marks the transaction as failed; you must ROLLBACK (or roll back to a savepoint) before continuing.
• Most PostgreSQL DDL is transactional (can be rolled back). Some operations are not allowed inside transactions (e.g., CREATE DATABASE, VACUUM, CREATE INDEX CONCURRENTLY).
• Keep transactions short: long transactions keep old row versions alive and can block vacuum from reclaiming space.

---

Step-by-Step Examples

Example 1: Basic Commit vs Rollback (Beginner)

Setup:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  account_id bigint PRIMARY KEY,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (account_id, balance) VALUES
  (1, 5000.00),
  (2, 3000.00);

SELECT account_id, balance
FROM accounts
ORDER BY account_id;

Expected output:

DROP TABLE
CREATE TABLE
INSERT 0 2
 account_id | balance
------------+---------
 1          | 5000.00
 2          | 3000.00
(2 rows)

Rollback example:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

ROLLBACK;

SELECT account_id, balance
FROM accounts
ORDER BY account_id;

Expected output:

BEGIN
UPDATE 1
UPDATE 1
ROLLBACK
 account_id | balance
------------+---------
 1          | 5000.00
 2          | 3000.00
(2 rows)

Explanation:

• The updates happened, but ROLLBACK discarded them.

---

Example 2: Use SAVEPOINT to Recover from a Failed Step (Intermediate)

Setup:

DROP TABLE IF EXISTS emails;

CREATE TABLE emails (
  email text PRIMARY KEY
);

INSERT INTO emails (email) VALUES ('a@example.com');

Transaction with a safe fallback:

BEGIN;

SAVEPOINT s1;

-- This will fail (duplicate primary key)
INSERT INTO emails (email) VALUES ('a@example.com');

-- Recover without losing the whole transaction
ROLLBACK TO SAVEPOINT s1;

-- Continue doing useful work
INSERT INTO emails (email) VALUES ('b@example.com');

COMMIT;

SELECT email FROM emails ORDER BY email;

Expected output:

DROP TABLE
CREATE TABLE
INSERT 0 1
BEGIN
SAVEPOINT
ERROR:  duplicate key value violates unique constraint "emails_pkey"
ROLLBACK
INSERT 0 1
COMMIT
     email
---------------
 a@example.com
 b@example.com
(2 rows)

Explanation:

• Without a savepoint, the first error would force a full ROLLBACK.
• Rolling back to the savepoint clears the error state and lets you continue.

---

Example 3: Multi-Table Write with Verification and RETURNING (Intermediate)

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(order_id),
  sku text NOT NULL,
  qty integer NOT NULL CHECK (qty > 0)
);

BEGIN;

WITH new_order AS (
  INSERT INTO orders (status)
  VALUES ('pending')
  RETURNING order_id
)
INSERT INTO order_items (order_id, sku, qty)
SELECT order_id, 'A1', 2 FROM new_order
UNION ALL
SELECT order_id, 'B2', 1 FROM new_order;

SELECT o.order_id, o.status, COUNT(*) AS item_count
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.order_id, o.status
ORDER BY o.order_id;

COMMIT;

Expected output (example):

DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
BEGIN
INSERT 0 2
 order_id | status  | item_count
----------+---------+-----------
 1        | pending | 2
(1 row)

COMMIT

Explanation:

• RETURNING lets you wire multi-step inserts safely.
• The verification SELECT inside the transaction lets you confirm results before committing.

---

Example 4: Transaction-Scoped Timeouts (Advanced)

BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5s';

-- your statements here

COMMIT;

Expected output:

BEGIN
SET
SET
COMMIT

Explanation:

• SET LOCAL applies only for the current transaction.
• Timeouts are a practical guardrail against unexpected lock waits.

---

Practical Use Cases

1) Money Transfers / Ledger Updates

Context: debit + credit must happen together.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

2) Backfills in Batches

Context: update a large table safely.

BEGIN;
SET LOCAL statement_timeout = '30s';
UPDATE big_table SET new_col = ... WHERE new_col IS NULL AND id BETWEEN 1 AND 100000;
COMMIT;

3) Multi-Table “Create Order” Workflow

Context: ensure order + items are consistent.

BEGIN;
-- insert order
-- insert items
-- verify
COMMIT;

4) Safe “Try Step” Patterns

Context: attempt a risky step but keep going.

SAVEPOINT s;
-- risky step
ROLLBACK TO SAVEPOINT s;

---

Common Mistakes & Troubleshooting

Mistake 1: Leaving Transactions Open (“idle in transaction”)

Wrong pattern:

BEGIN;
-- user goes to lunch

What happens: locks and snapshots stay open; vacuum can be blocked.

Fix: keep transactions short; in ops, monitor:

SELECT pid, usename, state, xact_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

---

Mistake 2: Forgetting to Handle an Error State

What happens: after an error, every subsequent statement fails until rollback.

Fix: use savepoints for recoverable steps, otherwise ROLLBACK and restart the transaction.

---

Mistake 3: Running Non-Transactional Commands in Transactions

Wrong:

BEGIN;
VACUUM public.orders;
COMMIT;

Typical error:

ERROR:  VACUUM cannot run inside a transaction block

Fix: run these commands outside a transaction (or schedule them as standalone statements).

Debugging tips:

1. If you see “current transaction is aborted”, roll back (or roll back to a savepoint).
2. Inspect open transactions with pg_stat_activity.
3. Add lock_timeout to avoid surprise lock waits.
4. Verify target rows with SELECT before you write.

---

Best Practices

• ✅ Keep transactions short and focused; ❌ hold transactions open while waiting on user input.
• ✅ Use savepoints for recoverable steps; ❌ force full rollbacks for small failures.
• ✅ Use RETURNING to avoid re-query races; ❌ re-select rows with weak predicates.
• ✅ Set local timeouts (lock_timeout, statement_timeout); ❌ allow unbounded lock waits.
• ✅ Verify important writes inside the transaction before commit; ❌ commit blindly.

---

Hands-On Practice

Exercise 1 (Easy): Basic rollback

Task: In a transaction, update a row, then roll back and confirm the change did not persist.

-- Your SQL here

Solution:

BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE account_id = 1;
ROLLBACK;
SELECT balance FROM accounts WHERE account_id = 1;

---

Exercise 2 (Medium): Use a savepoint

Task: Create a savepoint, run a statement that fails, roll back to the savepoint, and continue.

-- Your SQL here

Solution:

BEGIN;
SAVEPOINT s;
INSERT INTO emails (email) VALUES ('a@example.com');
ROLLBACK TO SAVEPOINT s;
INSERT INTO emails (email) VALUES ('c@example.com');
COMMIT;

---

Exercise 3 (Advanced): Add transaction-local timeouts

Task: Start a transaction and set lock_timeout and statement_timeout locally.

-- Your SQL here

Solution:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
COMMIT;

---

Connection to Other Concepts

Concept	Why it matters
Isolation levels	control visibility and anomalies under concurrency
Row locking	safe read-modify-write patterns use locks
Triggers	trigger failures abort the statement/transaction
Scheduling	maintenance jobs often rely on transactional boundaries
Backup and recovery	durability complements recovery procedures

---

Visual Learning Diagram

---

Common Pitfalls

Pitfall	Consequence	Prevention
Idle transactions	vacuum blocked, lock bloat	keep transactions short, monitor activity
Missing savepoints	full rollback on small errors	use savepoints strategically
No timeouts	stuck sessions on locks	set lock_timeout/statement_timeout
Weak verification	wrong rows updated	verify with SELECT and stable keys
Multi-step writes without transaction	partial state	wrap related steps in a transaction

---

Quick Reference

BEGIN;
COMMIT;
ROLLBACK;
SAVEPOINT s; ROLLBACK TO SAVEPOINT s;
SET LOCAL lock_timeout = '2s';

What's Next

• Next: Isolation Levels and Locking - Control visibility and apply row locks safely.
• Module Overview - Return to Transactions and Concurrency overview.