SQL Mastery
Advanced 15 min read SQL Mastery

Transactions and Isolation

The concurrency contract your pipelines assume

BEGIN/COMMIT/ROLLBACK plus isolation levels (read committed, repeatable read, serializable) define what concurrent readers and writers can observe — the contract pipelines rely on.

transactionisolationMVCCcommitrollbacksnapshot
Prerequisites: ACID and Transactions
Before we start

What you will learn

  • Explain BEGIN/COMMIT/ROLLBACK semantics
  • Compare read committed vs serializable
  • Reason about locking and the trade-offs for batch loads
Introduction

What is this?

A transaction groups statements so they commit or roll back atomically. Isolation levels decide what a transaction sees of concurrent changes: READ COMMITTED sees each statement's latest committed data; REPEATABLE READ keeps one snapshot; SERIALIZABLE prevents whole classes of races. Data pipelines fight over this in incremental loads, dedup and reconcile jobs.

Motivation

Why do you need it?

  • Incremental loads must commit as a unit or a retry double-writes.
  • Two pipelines building the same table concurrently need a defined isolation story (usually 'last writer wins' or table-level lock).
  • Understanding snapshotting explains 'why did my read change mid-query'.
Think about it

Mental model

Imagine

Editing shared documents with track changes

Think of

Isolation levels

READ COMMITTED = every refresh shows the newest saved version. REPEATABLE READ = your copy stays frozen from when you opened it. SERIALIZABLE = like a turn-taking meeting where your edits can't surprise anyone.

Mechanics

How it works

  • BEGIN starts the transaction; statements accumulate; COMMIT makes them durable; ROLLBACK undoes them.
  • MVCC keeps multiple versions so readers never block writers.
  • READ COMMITTED (default in Postgres/MySQL) re-evaluates per statement: two selects can see different snapshots.
  • REPEATABLE READ freezes one snapshot for the lifetime of the transaction (Postgres).
  • SERIALIZABLE adds conflict detection; failures return 'could not serialize access' and require a retry.

Commit and rollback paths.

BEGIN
Statements
Validate
COMMIT
ROLLBACK
Hands-on

Examples

Atomic upsert idiom

Atomic upsert idiom
BEGIN;
DELETE FROM daily_sales WHERE day = '2026-01-10';
INSERT INTO daily_sales SELECT day, SUM(amount) FROM sales WHERE day = '2026-01-10' GROUP BY day;
COMMIT;

Delete-then-insert inside one transaction is atomic: readers see either the old or the new partition, never both.

Real world

In production

  • A nightly loader wraps TRUNCATE+INSERT in a transaction so the target never exposes a half-partition.
  • A reconciliation step runs at REPEATABLE READ so both SELECTs agree with each other.
  • A row-locking `SELECT ... FOR UPDATE` guards a counter being incremented by concurrent workers.
Guidelines

Best practices

  • Keep transactions short: long ones hold locks/snapshots and bloat MVCC.
  • Retry SERIALIZABLE failures with a bounded backoff — they are part of the contract, not a bug.
  • For bulk loads, prefer single big transaction per partition (fast) vs per-row (slow, yet safer) based on target.
  • Set an explicit timeout so a stalled transaction can't pin locks forever.
Watch out

Common mistakes

  • Committing row-by-row in a loop, losing atomicity on error mid-batch.
  • Forgetting ROLLBACK on exception, leaving a hanging transaction with locks.
  • Blaming 'race condition' when the real fix is an explicit isolation level or a lock.
  • Reading a fact while another session mid-commit and trusting a partial window.
Root cause analysis

Common issues

Error
Could not serialize access due to concurrent update
Root cause

Serializable conflict between two transactions touching the same rows

Fix

Retry the transaction; keep the window short; reduce contention.

Error
Table grows faster than rows explain
Root cause

MVCC bloat from long-running transactions holding old versions

Fix

Commit promptly, run VACUUM/reclaim, and cap transaction duration.

When things break

Debugging tips

  • pg_stat_activity shows idle-in-transaction sessions holding locks — kill them.
  • When a 'serializable' error appears, wrap in a retry helper and log which transaction conflicted.
  • LOCK monitoring (pg_locks) reveals who holds what — the deadlock story writes itself.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which statement makes a transaction's writes durable?

2

Under READ COMMITTED, two SELECTs in one transaction always agree.

Go deeper

Official documentation

PostgreSQL transactions
docs
PostgreSQL MVCC isolation
docs