Transactions and ACID

After this lesson, you will be able to: Use BEGIN/COMMIT/ROLLBACK, understand ACID, and pick the right isolation level for your workload.

Transactions are how databases give you safety in concurrent environments. Get ACID + isolation levels right or get random bugs at scale.

Prerequisites:Indexes

ACID

Atomicity: all operations in a transaction succeed, or none do. Consistency: every transaction takes the DB from one valid state to another (constraints enforced). Isolation: concurrent transactions can't see each other's in-progress work. Durability: once committed, the data survives crashes (written to disk).

Basic transaction syntax

BEGIN / COMMIT / ROLLBACK.

sql

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

-- If anything between BEGIN and COMMIT fails:
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- something goes wrong
ROLLBACK;       -- balance change undone

-- Auto-commit: many DB clients (psql by default) commit each statement separately.
-- If you need atomicity, wrap explicitly in BEGIN/COMMIT.

Isolation levels

READ UNCOMMITTED: see other tx's uncommitted writes (Postgres treats as READ COMMITTED). READ COMMITTED (Postgres default): only see committed data; non-repeatable reads possible. REPEATABLE READ: snapshot at tx start; safe from non-repeatable reads, but phantom inserts possible. SERIALIZABLE: full isolation as if transactions ran one at a time. Highest safety, lower throughput.

Setting isolation level

Per-transaction or session.

tsx

BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- the rest of the transaction
COMMIT;

-- Common pattern: SERIALIZABLE for financial logic; READ COMMITTED for everything else.
-- Postgres detects serialization conflicts and aborts one of the conflicting transactions.
-- Your app code must catch the serialization_failure error and RETRY.

Optimistic vs pessimistic locking

Two strategies for concurrent updates.

sql

-- Pessimistic: lock the row
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Optimistic: version column + retry
UPDATE accounts SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;
-- If rows affected = 0, someone else changed it; retry

-- Pick pessimistic when contention is high. Optimistic when most updates don't conflict.

Common mistakes

Forgetting to wrap multi-step writes in BEGIN/COMMIT (correctness bug under failure). Holding transactions open too long (blocks other writers; explodes connection pool). Not handling serialization conflicts in SERIALIZABLE (silent data loss). Mixing schema changes (DDL) into transactions (works in Postgres, dangerous in others).

←Indexes and Query Performance

Back to SQL

Database Design and Normalization→