Part V — Transactions, Concurrency, and Performance

Chapter 18. Transactions and Concurrency Control

Until now, one user has touched the database at a time. Real databases serve hundreds simultaneously — the university's registration rush being the canonical example — and that is where correctness gets hard. Two students pressing "enroll" for the last seat at the same moment; a registrar updating a GPA while a dean's report reads it; a payment recorded in two steps with a crash between. Transactions are the database's answer: the all-or-nothing unit of work, and concurrency control the machinery that makes thousands of simultaneous transactions behave as if executed one at a time.

This chapter is the theory every production bug in this territory traces back to: ACID, the isolation levels, the named anomalies (lost update, dirty read, phantom), locking, MVCC, and deadlocks. It uses two-terminal experiments throughout — you will need two client sessions side by side, and the anomalies stop being abstract the moment you produce one yourself.

After studying this chapter you will be able to:

• Define transactions and their state machine, and use BEGIN, COMMIT, ROLLBACK, and SAVEPOINT fluently.
• Explain each ACID property and the mechanism that delivers it.
• Set transaction boundaries well: autocommit, short transactions, read-only scopes.
• Demonstrate lost updates, dirty reads, non-repeatable reads, and phantoms in live sessions.
• Match isolation levels to anomalies and set them on both platforms.
• Use locking deliberately, including SELECT ... FOR UPDATE.
• Explain MVCC as both platforms implement it, with its maintenance costs.
• Detect, diagnose, and prevent deadlocks.

18.1 Transaction concepts and states

A transaction is a sequence of operations performed as one logical unit of work — the unit the database treats as atomic: all of it happens, or none of it does. Transferring an enrollment between sections is two UPDATEs that must live or die together; recording a payment is an INSERT and a balance UPDATE with no acceptable middle state.

Transactions live a small state machine:

             BEGIN
              │
         ┌────▼────┐   statement ok   ┌──────────────────┐
         │ ACTIVE  ├─────────────────►│ PARTIALLY        │
         │         │                  │ COMMITTED        │
         └──┬───┬──┘                  └────────┬─────────┘
    failure │   │ last statement done          │ COMMIT
            ▼   └────────────► (statements     │
       ┌─────────┐              continue)      ▼
       │ FAILED  │                        ┌─────────┐
       └────┬────┘                        │COMMITTED│
            │ ROLLBACK (undo)             └─────────┘
            ▼
       ┌─────────┐
       │ ABORTED │  (effects erased; transaction ends)
       └─────────┘

From ACTIVE, statements run one at a time; a failure moves the transaction to FAILED and then ABORTED, where the DBMS undoes every effect (via its log). COMMIT is the point of no return: the moment durability is promised. Both platforms implement exactly this machine; the difference is who can see intermediate states — the isolation business of Sections 18.6–18.8.

18.2 ACID properties

The four guarantees, each with the machinery that delivers it:

• Atomicity — all or nothing. Mechanism: the write-ahead log (Chapter 4): a rollback replays the undo side; a crash mid-transaction replays the log to completion or erasure.
• Consistency — every committed transaction moves the database from one legal state to another (all constraints hold). Mechanism: the constraints of Chapter 9 are the enforcement; the transaction is the scope in which the DBMS may briefly hold states that would violate (in-flight), provided COMMIT finds them legal. Consistency is thus co-authored: the schema declares legality, transactions respect it.
• Isolation — concurrent transactions do not observe each other's in-flight states. Mechanism: locks and MVCC (Sections 18.9–18.10); degree: chosen by isolation level (18.8) — perfect isolation exists (SERIALIZABLE) at the price of concurrency.
• Durability — once COMMIT returns, the change survives crashes. Mechanism: the WAL/redo log is flushed at commit before the acknowledgement (fsync); recovery (Chapter 21) replays it.

The classic multi-statement example, run against university_dev:

BEGIN;
UPDATE enrollment SET grade = 'B' WHERE student_id = 21100001 AND section_id = 1;
UPDATE enrollment SET grade = NULL WHERE student_id = 21100002 AND section_id = 1;
-- a mistake is caught before COMMIT:
ROLLBACK;                       -- both updates vanish

18.3 BEGIN, COMMIT, and ROLLBACK

The three verbs, with the fourth that grades in between:

BEGIN;                          -- PostgreSQL; MySQL: START TRANSACTION

INSERT INTO enrollment (student_id, section_id, grade)
VALUES (21600001, 12, NULL);    -- Zara joins CSE221 too

SAVEPOINT after_enroll;

UPDATE student SET total_credits = total_credits + 3
WHERE  student_id = 21600001;

ROLLBACK TO SAVEPOINT after_enroll;   -- undo the credit bump, keep the insert

COMMIT;                               -- only the enrollment survives

PostgreSQL and MySQL share this spelling (MySQL's SAVEPOINT/ROLLBACK TO since forever; PostgreSQL uses BEGIN where MySQL prefers START TRANSACTION — both accept both). A ROLLBACK TO SAVEPOINT keeps the transaction alive and rewinds to the mark; a bare ROLLBACK ends it entirely. The discipline: savepoints mark validated stages inside long operations — load 1,000 rows, savepoint, validate, continue — so one bad batch does not refund an hour of work.

18.4 Transaction boundaries

Where transactions begin and end is a design decision with real costs. Both platforms run autocommit by default: every statement outside an explicit BEGIN is its own transaction (commit immediately). Explicit BEGIN starts a scope that ends at COMMIT, ROLLBACK — or implicitly: client disconnect (rollback), most DDL in MySQL (implicit commit — the Chapter 17 trap), or a session-ending error class.

Three boundary rules of professional practice. First, make units logical, not lazy: the transaction should be "record this enrollment," not "everything this script does." Second, keep transactions short: every open transaction pins resources — locks held, and in MVCC systems, old row versions that cleanup cannot reclaim (Section 18.10). A transaction that begins, waits on a human, and commits an hour later is the classic production villain ("the long-running transaction" on every DBA dashboard). Third, declare read-only transactions (BEGIN TRANSACTION READ ONLY / START TRANSACTION READ ONLY) — the engine may then skip bookkeeping it does not need, and the intent is documented.

18.5 Concurrent transaction execution

A schedule is the interleaving of operations from concurrent transactions. Serial execution — one transaction at a time — is trivially correct but wastes the machine; the DBMS's promise is that legal concurrent schedules have the same effect as some serial order (serializability). The seat problem shows why interleaving needs care:

 time ──►   T1 (Nusrat's enroll)          T2 (Rakib's enroll)
              read section 13: 2 seats
                                            read section 13: 2 seats
              check: 2 ≥ 1 → ok
                                            check: 2 ≥ 1 → ok
              insert enrollment
                                            insert enrollment
              update seats→1
                                            update seats→1

Both transactions read the same seat count, both conclude space exists, and one seat is oversold — the lost update anomaly of the next section, and the entire motivation for isolation levels and locking. Everything from here to Section 18.10 is the machinery that prevents this interleaving (or its damage).

18.6 Lost updates and dirty reads

The two write-side and read-side anomalies, with the platform settings that permit them:

Lost update. T1 and T2 both read a value, compute from it, and write — the second write silently erases the first:

 T1: gpa = read() → 3.90;  write(3.95)   ← an earned raise
 T2: gpa = read() → 3.90;  write(3.42)   ← a recalculation from stale data
 result: 3.42 — T1's update is gone

Plain SELECT-then-UPDATE in two sessions does this by default at READ COMMITTED. The fixes come in three flavors: atomic single-statement updates (SET gpa = gpa + 0.05 — the read and write fuse into one statement, and row locks serialize the writers), explicit row locks (SELECT ... FOR UPDATE before computing — Section 18.9), or SERIALIZABLE isolation.

Dirty read. T2 reads data T1 has written but not committed; T1 then rolls back — T2 has acted on facts that never existed. Both platforms default far above this: a READ UNCOMMITTED level exists in MySQL's syntax book (PostgreSQL maps the name to READ COMMITTED, refusing the anomaly outright), and no default configuration on either platform permits dirty reads. It appears in this chapter to be produced once in a laboratory (MySQL, explicitly at READ UNCOMMITTED) and then never allowed again — seeing the ghost data is what makes the higher levels feel earned.

18.7 Non-repeatable reads and phantom reads

The read-side anomalies at stronger levels:

Non-repeatable read. T1 reads a row; T2 commits a change to that row; T1 re-reads and sees a different value — within one transaction, reality shifted:

 T1: SELECT gpa FROM student WHERE id=21300003;   → 3.90
 T2: UPDATE student SET gpa=3.93 WHERE id=21300003;  COMMIT;
 T1: SELECT gpa FROM student WHERE id=21300003;   → 3.93

Phantom read. T1 reads a set by predicate; T2 inserts a matching row and commits; T1 re-runs the same predicate and sees a row that was not there — the changed entity is a set, not a row:

 T1: SELECT COUNT(*) FROM enrollment WHERE section_id=12;  → 2
 T2: INSERT enrollment(21600001, 12, NULL);  COMMIT;
 T1: SELECT COUNT(*) FROM enrollment WHERE section_id=12;  → 3

The standard's anomaly table (permitted = ✓):

MySQL's famous footnote: InnoDB at REPEATABLE READ also prevents phantoms (via next-key locking — Section 18.9), so MySQL's default is anomaly-free for these three, a deliberate deviation from the standard's letter in the user's favor.

18.8 Isolation levels in PostgreSQL and MySQL

The levels are set per session or transaction, spelled identically:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;         -- per transaction
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION
    LEVEL REPEATABLE READ;                              -- per session (PG)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- per session (MySQL)

The two-session experiment that makes defaults visible (PostgreSQL, default READ COMMITTED):

 Session A                                Session B
 BEGIN;
 SELECT COUNT(*) FROM enrollment;   → 28
                                          BEGIN;
                                          INSERT INTO enrollment
                                          VALUES (21100003, 11, 'B');
                                          COMMIT;
 SELECT COUNT(*) FROM enrollment;   → 29   ← saw B's committed insert
 COMMIT;

Run the same script on MySQL's default REPEATABLE READ and session A's second SELECT still says 28 — its snapshot was taken at first read and holds for the transaction. Neither behavior is a bug; they are the two platforms' defaults doing what their names promise — and an application ported between them changes behavior silently (Chapter 17's quietest hazard, now demonstrated).

Choosing levels is a trade of correctness-guarantee against concurrency: READ COMMITTED for short, independent operations (the common case); REPEATABLE READ for report consistency (all the dean's queries over one stable snapshot); SERIALIZABLE for invariants that must not be raced (seat-selling: two concurrent transactions at SERIALIZABLE — one gets aborted with a retryable error instead of overselling). PostgreSQL implements SERIALIZABLE with SSI (true serializability via dependency tracking), MySQL by locking harder; both surface conflicts as the 40001 SQLSTATE — the application's retry signal (Section 18.11, and Chapter 23's retry loops).

18.9 Locking mechanisms

Locks are the oldest concurrency tool: grant permission, make others wait. The vocabulary:

• Shared (S) locks permit concurrent readers; exclusive (X) locks permit one writer and no readers. Writing takes X on the affected rows (both platforms, automatically); plain SELECTs in MVCC systems take none — they read snapshots instead (Section 18.10), which is why readers never block writers in either engine.
• Granularity: row locks (the default for DML) and table locks (LOCK TABLE ... IN ... MODE in PostgreSQL; MySQL's explicit LOCK TABLES exists but is a MyISAM-era tool — InnoDB's row locking made it obsolete for transactional work).
• Intention locks (InnoDB) mark table-level intent before row locks, making conflict checks cheap.
• Gap and next-key locks (InnoDB at REPEATABLE READ) lock the spaces between index keys, so a phantom-producing INSERT into a scanned range waits — the mechanism behind MySQL's phantom-free REPEATABLE READ.

The deliberate-locking tool every application developer must know is **SELECT ... FOR UPDATE** — read and lock, so the read-compute-write pattern is safe:

-- Session A: guard the seat check
BEGIN;
SELECT section_id, capacity FROM course_section
WHERE  section_id = 13
FOR UPDATE;                        -- row locked: B's FOR UPDATE waits here

SELECT COUNT(*) FROM enrollment WHERE section_id = 13;   -- 2
-- capacity 35, 2 enrolled: seats available
INSERT INTO enrollment VALUES (21600002, 13, NULL);
COMMIT;                            -- lock released; B proceeds, now sees 3

The lock serializes the check-and-insert pairs that interleaved so badly in Section 18.5. Its siblings: FOR SHARE (readers' lock), and PostgreSQL's SKIP LOCKED — "lock what is free, skip what is not" — the queue-worker's tool (Chapter 23's job-queue pattern). FOR UPDATE is portable between both platforms; SKIP LOCKED is native to PostgreSQL and MySQL 8.0+.

18.10 Multiversion concurrency control (MVCC)

Locking asks a binary question (may I?). MVCC answers a different one: which version of the data should I see? Both platforms keep old versions of rows so readers get a consistent past while writers build a future:

• PostgreSQL: every row version carries transaction IDs (xmin = creator, xmax = deleter). A statement takes a snapshot — the set of transactions visible now — and a row version is visible iff its creator committed before the snapshot and its deleter did not. UPDATE writes a new version and marks the old one dead. Readers never block writers, writers never block readers; only writer-writer conflicts on the same row lock. Dead versions accumulate until VACUUM reclaims them (autovacuum by default — Chapter 21's maintenance star), and long transactions pin snapshots that hold dead rows hostage: the Section 18.4 rule, now with its mechanism.
• MySQL/InnoDB: every row carries hidden system columns and a roll pointer into the undo log; consistent reads reconstruct the needed version by walking the undo chain back to the reader's snapshot. REPEATABLE READ keeps one snapshot for the transaction's life; READ COMMITTED takes a fresh one per statement (the Section 18.8 experiment, mechanized). Old undo records are purged when no snapshot needs them — and the same long-transaction pathology applies.

MVCC's price is bookkeeping (version storage, cleanup) and its prize is the property everyone loves about both engines: readers never block writers. The pathology to internalize: long transactions freeze history — dead versions cannot be cleaned while any snapshot might still see them, so a hung transaction on a busy table means bloated storage (PG) or a swelling undo history (MySQL) until it ends. "Keep transactions short" is now a storage rule, not just a courtesy.

18.11 Deadlock detection and handling

Two transactions, each holding what the other needs, both waiting forever:

 T1: UPDATE enrollment  (locks row A)
 T2: UPDATE enrollment  (locks row B)
 T1: UPDATE enrollment  (wants row B)  → waits for T2
 T2: UPDATE enrollment  (wants row A)  → waits for T1   ← deadlock cycle

Neither platform waits long. PostgreSQL's deadlock detector runs on a timeout (deadlock_timeout, default 1 s): it finds the cycle and aborts one transaction with SQLSTATE 40P01. InnoDB detects immediately and rolls back the cheapest victim (fewest rows changed), reporting SQLSTATE 40001; MySQL's SHOW ENGINE INNODB STATUS prints the LATEST DETECTED DEADLOCK — the two statements, the two locks, and the victim — which is the diagnostic to capture when deadlocks recur.

Deadlocks are not corruption — they are the scheduler deciding a conflict is unresolvable — but they are failures your application must handle: the aborted transaction must be retried (the 40P01/40001 retry loop is Chapter 23's pattern, written there in real code). Prevention beats retry, and the classics are two: lock in a consistent order (sort the rows before updating — enrollment swaps that touch (student 1, section 2) and (student 2, section 1) should both proceed in sorted order), and keep transactions short (fewer statements inside locks, smaller cycles). The professional checklist for any deadlock report: find the two statements (from the log), find the two lock targets, fix the order or fuse the statements — and only then add the retry.

Chapter Summary

• A transaction is the all-or-nothing unit of work: ACTIVE → (partially committed | failed → aborted) → COMMITTED; SAVEPOINTs mark rewound stages.
• ACID: atomicity and durability come from the WAL; consistency from schema constraints plus disciplined transactions; isolation from locks/MVCC at the chosen level.
• Boundaries: autocommit by default; BEGIN/COMMIT scopes; MySQL DDL commits implicitly; transactions should be logical, short, and read-only when reading.
• Interleaving without control produces lost updates (stale read-compute-write) and dirty reads (ghost data) — the seat oversell is the canonical schedule.
• Non-repeatable reads change a row between reads; phantoms change a set; the standard's table maps levels to permitted anomalies — MySQL's REPEATABLE READ also blocks phantoms (next-key locks).
• Defaults: PostgreSQL READ COMMITTED (sees committed changes mid-transaction), MySQL REPEATABLE READ (one snapshot per transaction) — porting changes behavior silently; SERIALIZABLE trades concurrency for invariants, surfacing 40001 as the retry signal.
• Locks: S/X, row-granular DML locks, InnoDB intention/gap/next-key locks, and SELECT ... FOR UPDATE (plus SKIP LOCKED) as the application's deliberate tools.
• MVCC: PostgreSQL's xmin/xmax snapshots with VACUUM cleanup; InnoDB's undo chains with purge — readers never block writers, and long transactions freeze cleanup.
• Deadlocks: cycles detected (PG on timeout, InnoDB immediately), victim aborted (40P01/40001), handled by retry, prevented by lock ordering and short transactions.

Key Terms

Laboratory Exercises

1. Savepoint choreography: run Section 18.3's script in university_dev on both platforms, predicting each stage's visible state with a count query between statements, and verify only the enrollment survived. Expected results: enrollment count 28 → 29 → (savepoint) 29 with credits changed → 29 with credits restored after ROLLBACK TO → committed 29.
2. Produce a dirty read (MySQL only): session A BEGIN and UPDATE a student's gpa without commit; session B at SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED reads the new value; A ROLLBACKs; B re-reads the old value — the ghost, twice. Expected result: B saw 3.95 that never existed, then 3.90 — and one paragraph on why no default allows this.
3. Non-repeatable read versus snapshot: on PostgreSQL defaults, run the Section 18.8 two-session experiment (28 → 29); on MySQL defaults, run the same script (28 → 28); record both and explain from the levels' names. Expected result: the exact divergence of the chapter, produced by you.
4. Phantom demonstration: on PostgreSQL at READ COMMITTED, a transaction counts Fall 2026 enrollments twice while a second session inserts a section-13 enrollment between reads; repeat at SERIALIZABLE and capture the 40001 abort. Expected results: count 8 → 9 at READ COMMITTED; at SERIALIZABLE the second read (or the other session's commit) triggers the serialization failure — retry once and succeed.
5. Seat guarding with FOR UPDATE: two sessions run the Section 18.9 enrollment flow for the last seat of a capacity-limited section (set section 5's capacity to 2 in dev and enroll two students first); verify serialization without oversell, then repeat with plain SELECTs to reproduce the oversell. Expected result: guarded runs admit exactly the seats available; unguarded runs oversell (both sessions saw "1 seat free").
6. Deadlock clinic: build the Section 18.11 two-row cycle in two sessions on both platforms; capture both error messages (40P01 / 40001 and the victim text), read MySQL's SHOW ENGINE INNODB STATUS deadlock section, then fix it by sorting the updates and show the interleaved-but-ordered version runs clean. Expected result: one aborted transaction each platform pre-fix; both succeed post-fix; the deadlock log sections pasted in your lab notes.

Review Questions and Exercises

1. Define transaction in one sentence and name the state after a failed statement. A sequence of operations applied as one all-or-nothing unit of work; FAILED, then ABORTED once the undo completes.
2. Which ACID property does each mechanism deliver: WAL flush at commit; CHECK constraints; snapshots; undo replay? Durability; consistency (with transaction discipline); isolation; atomicity.
3. Why is SET gpa = gpa + 0.05 immune to lost updates where SELECT-then-UPDATE is not? The read and write are one statement — the row lock spans both, so writers serialize on the row instead of racing stale reads.
4. Name the anomaly in each scenario: a row's value changes between two reads; a new row matches a re-run predicate; data vanishes after its writer rolls back; two updates race from one stale read. Non-repeatable read; phantom; dirty read; lost update.
5. Why does MySQL's REPEATABLE READ table-entry differ from the SQL standard's? Next-key/gap locks prevent phantoms too — the standard permits them at RR, InnoDB blocks them.
6. What changes for a ported application when PostgreSQL's READ COMMITTED becomes MySQL's REPEATABLE READ? Mid-transaction reads stop seeing others' committed changes — report queries within a transaction become snapshot-consistent instead of latest-committed, silently.
7. Give the seat-selling invariant and the three tools that enforce it under concurrency. Enrolled ≤ capacity; atomic conditional update, SELECT ... FOR UPDATE, or SERIALIZABLE with 40001 retry.
8. Why do plain SELECTs take no locks in both engines, and what do they use instead? MVCC — snapshots of committed versions; readers never block writers, no locks needed for reading.
9. What holds dead row versions hostage in each platform, and what is the operational symptom? Long-running transactions pinning snapshots — PostgreSQL bloat (until VACUUM can run), InnoDB undo history growth (until purge); symptom: storage creep tied to a hung transaction.
10. A deadlock report names two UPDATEs on the same two rows in opposite order. State the fix and the fallback. Fix: consistent lock order (sorted updates) or fusing statements; fallback: retry loop on 40P01/40001.
11. What does SKIP LOCKED buy a queue worker, and what must the worker still guarantee? Workers take free jobs without queuing behind each other; the worker must still complete or release its job (idempotent processing, visibility timeout) so a crash does not strand it.
12. Explain "keep transactions short" as three rules with their mechanisms. Logical units (correct rollback scope); short duration (locks released early, MVCC versions reclaimable); read-only where reading (no locks/bookkeeping needed) — the long transaction is the villain in all three.

Mini-Project

Produce the concurrency lab report, concurrency_lab.md plus concurrency_lab.sql: every anomaly of this chapter produced live, on both platforms where expressible — dirty read (MySQL), lost update (both, then fixed three ways: atomic update, FOR UPDATE, SERIALIZABLE), non-repeatable read (PG at RC; show MySQL's RR refusing it), phantom (PG at RC and at SERIALIZABLE with the 40001 capture), deadlock (both platforms, with both error texts and MySQL's INNODB STATUS excerpt), and the seat-guard experiment with and without FOR UPDATE. For each: the two-session script (labeled A/B), the observed outputs pasted verbatim, the anomaly named with its mechanism, and the fix demonstrated. Close with the application-facing deliverable: a retry helper specified in comments — catch SQLSTATE 40P01/40001, back off briefly, re-execute the whole transaction, cap the attempts — ready for Chapter 23 to implement in Java, Python, and PHP.