Module 17: Transactions & Concurrency Control · Lesson 2 of 7
Isolation (Lesson 01, §5.3) is a spectrum, not a switch. The SQL standard defines four isolation levels, each permitting or preventing specific “read phenomena” — ways one transaction’s view of the data can be affected by another concurrent transaction. This lesson names each phenomenon precisely and shows what MySQL actually does at each level, because “isolation” without a level attached is an incomplete claim.
Full serializability requires that the observed results of concurrent transactions be equivalent to the results produced by some serial ordering of those transactions; it does not require that transactions never overlap in time. In practice, database engines may execute transactions concurrently while ensuring their effects match a particular serial schedule. Serializability is therefore a correctness property about the outcome (equivalence to a serial order), not a prohibition on temporal overlap. Different engines implement serializability using different mechanisms (strict locking, optimistic / SSI techniques, etc.), and their performance and retry behaviour under contention vary.
A reporting dashboard that reads “current headcount by department” doesn’t need the same isolation guarantees as a payroll job computing “total salary to disburse this cycle” — a dashboard reading a slightly stale but internally consistent snapshot is usually fine; a payroll run double-counting or missing a row because of a phantom read is a real financial defect. Picking the isolation level deliberately, per workload, is how you get both: fast dashboards and correct payroll.
Transaction A reads a row that Transaction B has modified but not yet committed. If B then rolls back, A acted on data that was never actually true.
-- Session A -- Session B
START TRANSACTION;
UPDATE employes SET salary = 999999.00
WHERE emp_id = 12;
SET SESSION TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED;
START TRANSACTION;
SELECT salary FROM employes WHERE emp_id = 12;
-- Under READ UNCOMMITTED: sees 999999.00 — B's UNCOMMITTED change.
COMMIT;
ROLLBACK;
-- Session A already acted on a value that never actually existed
-- from the database's permanent point of view.
Transaction A reads the same row twice within one transaction, and gets different values, because Transaction B committed a change to that row in between A’s two reads.
-- Session A -- Session B
START TRANSACTION;
SELECT salary FROM employes WHERE emp_id = 12; -- e.g. 82000.00
START TRANSACTION;
UPDATE employes SET salary = 88000.00
WHERE emp_id = 12;
COMMIT;
SELECT salary FROM employes WHERE emp_id = 12; -- under READ COMMITTED: 88000.00 now
COMMIT;
Transaction A runs the same range-based query twice, and the set of rows returned changes, because Transaction B committed an insert or delete matching that range in between.
-- Session A -- Session B
START TRANSACTION;
SELECT COUNT(*) FROM employes WHERE dept_id = 4; -- e.g. 8
START TRANSACTION;
INSERT INTO employes
(emp_id, emp_name, dept_id,
manager_id, hire_date, salary)
VALUES (9002, 'New Hire', 4,
3, '2025-01-10', 60000.00);
COMMIT;
SELECT COUNT(*) FROM employes WHERE dept_id = 4; -- under READ COMMITTED: 9 now
COMMIT;
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
READ UNCOMMITTED |
Possible | Possible | Possible |
READ COMMITTED |
Prevented | Possible | Possible |
REPEATABLE READ (MySQL default) |
Prevented | Prevented | Prevented by MVCC snapshot* |
SERIALIZABLE |
Prevented | Prevented | Prevented |
*For plain (nonlocking) SELECTs, MySQL’s REPEATABLE READ reads from a
consistent snapshot, so rows inserted by other committed transactions are
not visible — stronger than the SQL standard strictly requires at this
level. Locking reads and writes (SELECT ... FOR UPDATE, UPDATE,
DELETE) don’t use the snapshot: they read the latest committed data and,
under REPEATABLE READ, use next-key locks on the index ranges they scan,
which blocks concurrent inserts into those ranges. Which locks are taken
depends on the statement, the index used, and the search condition
(Lesson 03). A locking read can therefore return a newer committed row
version than an earlier plain SELECT in the same transaction — two
different read mechanisms, not a failure of the isolation level.
-- Setting isolation level for the NEXT transaction only:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
...
COMMIT;
-- Setting it for the whole SESSION (persists across transactions):
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Checking current setting:
SELECT @@transaction_isolation;
REPEATABLE READ is MySQL’s default for a reason: it gives strong
guarantees for typical OLTP workloads without SERIALIZABLE’s full
throughput cost. Don’t downgrade to READ COMMITTED “for
performance” without first measuring that REPEATABLE READ is
actually the bottleneck — verify with EXPLAIN/profiling before
changing an isolation level for a hypothetical performance reason.SERIALIZABLE except more contention.MySQL: Default REPEATABLE READ. Achieves it via InnoDB’s MVCC
(Lesson 05) rather than locking every read — this is why MySQL’s
REPEATABLE READ also happens to prevent most phantom reads for plain
SELECTs, which is stronger than the SQL standard’s minimum
requirement for this level.
PostgreSQL: Default READ COMMITTED. PostgreSQL’s REPEATABLE
READ is implemented differently (true snapshot isolation) and its
SERIALIZABLE uses Serializable Snapshot Isolation (SSI) — worth
knowing if you’re used to MySQL’s locking-flavored SERIALIZABLE and
move to Postgres, since the failure mode under contention differs
(Postgres’s SSI can abort a transaction with a serialization-failure
error that the application must be prepared to retry).
SQL Server: Default READ COMMITTED, with an additional
READ COMMITTED SNAPSHOT database-level option that changes
READ COMMITTED’s underlying mechanism to MVCC-style row versioning
rather than locking — a hybrid MySQL and PostgreSQL users should be
aware exists before assuming SQL Server’s READ COMMITTED behaves
identically to either.
REPEATABLE READ and PostgreSQL’s READ COMMITTED are different
defaults with different phantom-read exposure.REPEATABLE READ prevents your
SELECTs from seeing certain changes, but a plain SELECT followed
by a separate UPDATE based on that read value can still race
against another session’s concurrent update unless you use a
locking read (SELECT ... FOR UPDATE, Lesson 03) or an atomic
single-statement update.SERIALIZABLE everywhere “to be safe” without
measuring the throughput cost, then being surprised by increased
lock waits and deadlocks (Lesson 04) under real concurrent load.REPEATABLE READ transaction: MVCC means a
transaction’s snapshot is fixed at (roughly) its first read — a
transaction left open for an hour continues seeing data as of that
snapshot, even as other committed changes pile up. This can
surprise developers expecting a SELECT late in a long transaction
to reflect “current” data.REPEATABLE READ: SELECT ... FOR UPDATE
and SELECT ... FOR SHARE don’t use the MVCC snapshot the same way
plain SELECTs do — they read the latest committed version and take
a lock, which is why they can still encounter phantom-adjacent gap
locks (Lesson 03) that plain reads don’t.SELECT @@transaction_isolation;), and whether the
report logic opens a new transaction per query versus one
transaction wrapping the whole report.UPDATE ... SET x = x + 1
style statement; see Lesson 03.Isolation level choice interacts directly with lock duration and
contention (Lesson 03) — SERIALIZABLE under MySQL — when autocommit is disabled — implicitly turns plain SELECTs into SELECT ... FOR SHARE, taking shared locks on the rows it reads (w[…]
lock contention under concurrent load compared to REPEATABLE READ’s
MVCC-based reads that don’t block writers. This is a structural
tradeoff to weigh against correctness needs, not a reason to avoid
SERIALIZABLE when it’s genuinely required — measure the actual
contention on your workload before deciding.
Oracle: Default is READ COMMITTED, implemented via multiversioning
similar in spirit to MySQL’s MVCC; Oracle does not implement a true
READ UNCOMMITTED.
SQLite: Effectively serializes all writers (a single writer lock at a time) regardless of isolation-level-style settings — the isolation level conversation in SQLite is less about read phenomena between concurrent writers and more about read/write concurrency generally.
DuckDB: Uses optimistic MVCC concurrency control appropriate to its typically single-process, analytical-workload use case.
SELECT ... FOR UPDATE return a
newer committed row version than a plain SELECT earlier in the same
transaction, and how does InnoDB protect the scanned index range
against concurrent inserts?” — Tests the difference between snapshot
reads (MVCC) and locking reads (latest committed data plus next-key
locks), without implying the isolation level “fails” to protect
locking reads.Isolation level names a specific point on the correctness/throughput
tradeoff, defined by which of three read phenomena (dirty,
non-repeatable, phantom) it permits. MySQL defaults to REPEATABLE
READ, achieved through MVCC (Lesson 05), which happens to prevent
most phantoms for plain reads too — stronger than the standard
requires. Isolation level alone does not prevent every concurrency
bug; a read-then-write race still needs explicit locking (Lesson 03)
or an atomic statement regardless of isolation level chosen.
READ COMMITTED, then show the same sequence does NOT show a
non-repeatable read under REPEATABLE READ.SESSION TRANSACTION ISOLATION LEVEL
SERIALIZABLE for a reporting-only read replica might be a
reasonable choice, while doing the same for the primary
transactional workload might not be.dept_id = 7 under READ COMMITTED, then show that MySQL’s
default REPEATABLE READ prevents it for the plain SELECT
version of the same query.(Some challenges are scaffolded in this lesson’s .sql file. The cross-lesson problem set, with full solutions, is in PRACTICE_PROBLEMS.md and SOLUTIONS.sql.)
Previous: Lesson 01 — ACID & Transaction Fundamentals · Next: Lesson 03 — Locking Mechanisms