Isolation, Locks & Concurrency Control — SQL Course

What you'll learn

Reason about concurrent anomalies, isolation levels, MVCC, row locks, deadlocks, and safe retry boundaries. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply MVCC to a realistic data question
Apply Read Committed to a realistic data question
Apply Repeatable Read to a realistic data question
Apply Serializable to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
MVCC	Readers observe versioned snapshots while writers create new versions	Reason about visibility separately from physical locking
Serializable	Execution behaves like some serial order	Retry serialization failures around the complete transaction
Row lock	A lock coordinating conflicting row changes	Lock in consistent order and keep the transaction short

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the concurrent transaction policy question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Claim available work safely

SKIP LOCKED lets concurrent workers claim different rows without waiting on already claimed jobs.

claim_jobs.sql

BEGIN;
WITH claimed AS (
  SELECT id FROM jobs.queue
  WHERE status = 'ready'
  ORDER BY priority DESC, id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs.queue AS q
SET status = 'running', started_at = now()
FROM claimed
WHERE q.id = claimed.id
RETURNING q.*;
COMMIT;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Retries inside a transaction do not reset its failed state. Retry the complete transaction from a clean boundary with a limit.

Independent workshop

Build a review-ready concurrent transaction policy lab against the course commerce dataset.

Your finished workshop must include:

MVCC
Read Committed
Repeatable Read
Serializable
Row-level locks
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

MVCC: Reason about visibility separately from physical locking
Serializable: Retry serialization failures around the complete transaction
Row lock: Lock in consistent order and keep the transaction short

Quick check

1. Which rule best applies to MVCC?

2. Which rule best applies to Serializable?

3. Which rule best applies to Row lock?

Next: Roles, Privileges & Row-Level Security