Module 17: Transactions & Concurrency Control · Lesson 6 of 7
Lessons 01–05 built the mechanisms. This lesson is a catalog of how those mechanisms get misused in real systems — patterns that “work” under light load and quietly become the cause of lock contention, deadlocks, or outages under real concurrency.
Every anti-pattern below is a violation of a principle already established in Lessons 01–05, restated here as “what it looks like when someone doesn’t apply it.” Interview questions and production debugging (Module 18) both draw heavily on recognizing these patterns under time pressure, which is why they’re consolidated here rather than left scattered across the earlier lessons.
-- ANTI-PATTERN:
START TRANSACTION;
UPDATE employes SET salary = salary + 5000 WHERE emp_id = 12;
-- application code here calls an external payroll API, waits 3+ seconds
UPDATE employes SET salary = salary - 5000 WHERE dept_id = 4 ORDER BY emp_id LIMIT 1; -- compensating entry
COMMIT;
Every lock this transaction holds stays held for the entire external call’s duration. If the call hangs or is merely slow, every other session needing those locks queues up behind it. Fix: do the external call before opening the transaction (validate/prepare outside), or after committing (finalize outside) — never during.
-- ANTI-PATTERN (relies on autocommit, no explicit boundary):
UPDATE departments SET dept_name = 'Sales & Marketing' WHERE dept_id = 4;
-- if the application crashes HERE, only this statement is committed
UPDATE employes SET salary = salary * 1.02 WHERE dept_id = 4;
Under autocommit (Lesson 01, §7), each statement is its own
independent transaction — a crash between them leaves the operation
half-done, permanently. Fix: wrap the whole logical operation in
one explicit START TRANSACTION / COMMIT.
-- ANTI-PATTERN: one transaction updating every row in a large table
START TRANSACTION;
UPDATE employes SET salary = salary * 1.03; -- all rows, no WHERE
COMMIT;
A transaction touching an entire large table holds locks on all of it
for the whole duration, blocking essentially everything else against
that table. Fix: batch the operation into smaller transactions
(e.g., by dept_id, or in primary-key ranges), each committing
independently, so lock duration and blast radius per transaction stay
bounded.
-- ANTI-PATTERN (application pseudocode):
-- try:
-- run_transaction()
-- except DeadlockError:
-- raise -- surfaces as an unhandled 500 error to the user
A deadlock (Lesson 04) is an expected, occasional outcome under concurrent access — not treating it as retryable turns a normal, recoverable event into a user-facing failure. Fix: catch deadlock (and, on engines like PostgreSQL, serialization-failure) errors specifically and retry the transaction, typically with a brief backoff and a retry limit.
-- ANTI-PATTERN: SERIALIZABLE by default for everything, "to be safe"
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ...applied to a read-heavy reporting endpoint that doesn't need it
SERIALIZABLE (Lesson 02, §6) implicitly makes plain reads shared-locking reads when autocommit is disabled (Lesson 02, §12), which can increase contention under concurrent load
for workloads that don’t actually need the extra guarantee. Fix:
default to MySQL’s REPEATABLE READ; reserve SERIALIZABLE for
operations where the specific guarantee (no phantom reads even under
locking reads / true serial-equivalent ordering) is a genuine, named
requirement — not a default reflex.
-- ANTI-PATTERN: plain read, application-level check, separate write
-- SELECT quantity FROM inventory WHERE product_id = 55;
-- (application code checks quantity > 0)
-- UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 55;
Covered fully in Lesson 03, §4 and §10 — this is the classic
lost-update race. Fix: SELECT ... FOR UPDATE on the read, or
(preferably, where the logic allows it) a single atomic statement:
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 55
AND quantity > 0.
-- ANTI-PATTERN: code written and tested against MySQL's default
-- REPEATABLE READ, then deployed against a PostgreSQL instance
-- (default READ COMMITTED) without re-verifying phantom-read exposure
Lesson 02, §8 documents that MySQL defaults to REPEATABLE READ while
PostgreSQL defaults to READ COMMITTED — code relying on
phantom-read prevention “because that’s just how the database
behaves” can silently become unsafe when the underlying engine (or
even just its configuration) changes. Fix: state the isolation
level a piece of logic actually depends on explicitly in code/comments,
and set it explicitly rather than relying on whichever default happens
to be in effect.
The mechanisms behind each anti-pattern were already covered with per-engine differences in Lessons 01–05; §4.7 above is the one anti-pattern that is specifically about cross-engine differences — treat it as a reminder to re-check the earlier lessons’ dialect notes whenever code moves between engines.
Covered inline per anti-pattern in §4 above — each entry’s “ANTI- PATTERN” block is the common mistake, with its fix stated directly after.
Use Lesson 03’s information_schema.INNODB_TRX and
performance_schema.data_lock_waits queries, and Lesson 04’s SHOW
ENGINE INNODB STATUS\G deadlock report, as the concrete diagnostic
tools for spotting which of these anti-patterns is actually present in
a production incident — this lesson names the patterns; Lessons 03–04
give you the tools to catch them in the act.
Every anti-pattern in this catalog degrades specifically under concurrent load — most are invisible or harmless in single-user testing and low-traffic staging environments, which is exactly why they tend to reach production undetected. Load-testing with realistic concurrency, not just realistic data volume, is what actually surfaces these before real users do.
Not applicable as a separate section here — dialect-specific detail for each underlying mechanism lives in Lessons 01–05; this lesson is a synthesis, not a new mechanism.
START TRANSACTION block. What’s wrong with
this, specifically?” — Tests for 4.1, with the specific mechanism
(lock duration tied to external call latency), not just “that seems
bad.”This lesson names seven concrete anti-patterns, each a violation of a
principle from Lessons 01–05: holding a transaction across an external
call, missing transaction boundaries, oversized transactions, no
deadlock retry logic, reflexive SERIALIZABLE overuse, unguarded
read-then-write races, and assuming isolation-level defaults hold
across engines. Recognizing these in real code is the practical skill
this whole module has been building toward.
(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 05 — MVCC · Next: Lesson 07 — Transaction Design Workflow