📚 Learning Objectives
By the end of this lesson, you will be able to:
- Use OVER (PARTITION BY … ORDER BY …) to compute rankings, running totals, moving averages and row-to-row differences without collapsing rows
- Choose between ROW_NUMBER, RANK, DENSE_RANK and NTILE, and use LAG/LEAD/FIRST_VALUE/LAST_VALUE
- Control window frames with ROWS BETWEEN … and avoid the LAST_VALUE default-frame trap
- Structure multi-step queries with common table expressions (WITH), and walk hierarchies or generate series with recursive CTEs
- Group statements into transactions with START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT
- Explain ACID, the four isolation levels and the anomalies each one prevents, and demonstrate them with two MySQL sessions
- Recognise and handle lock waits and deadlocks in InnoDB
🎯 Prerequisites
- Completed Lessons 1-17 (especially Lesson 10 Aggregation, Lesson 11 Subqueries, Lesson 15 Performance)
- MySQL 8.0 or later (window functions and CTEs were added in 8.0; they do not exist in 5.7)
- The course database from
setup_database.sql
📖 Table of Contents
- Why This Lesson Exists
- Window Functions: the OVER Clause
- Ranking Functions
- Aggregates as Window Functions
- Window Frames: ROWS and RANGE
- LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTILE
- Common Table Expressions
- Recursive CTEs
- Transactions: START, COMMIT, ROLLBACK, SAVEPOINT
- ACID and Isolation Levels
- Locks, Lock Waits and Deadlocks
- Transactions from Python (SQLAlchemy)
- Best Practices
- Common Pitfalls
- Exercises
1. Why This Lesson Exists
Lesson 10 introduced window functions in one section, Lesson 11 used subqueries for "per-group" questions, and Lesson 6 mentioned transactions in passing. The three topics here are what separate "can query a table" from "can write the reports and the write paths a real application needs":
- Window functions answer "rank within group", "running total", "compared with the previous month" in one pass, where subqueries need a self-join per question.
- CTEs make multi-step queries readable and add recursion, which plain SQL otherwise lacks.
- Transactions make multi-statement writes safe when many users work at once.
The teaching data set
Results below are shown for this subset of the course database so you can verify your output:
students: 1 Alice 3.90 | 2 Bob 3.40 | 3 Carol 3.70 | 4 Dan 2.90 | 5 Eve 3.40
courses: CS101 (3 cr) | MATH201 (4 cr) | CS201 (3 cr) | CS301 (3 cr)
enrollments (enrollment_date, student, course, grade)
2024-01-10 Alice CS101 92 | 2024-01-12 Bob CS101 85 | 2024-01-15 Eve CS101 85
2024-02-02 Carol CS101 78 | 2024-02-05 Alice MATH201 88 | 2024-02-07 Carol MATH201 95
2024-03-01 Dan MATH201 70 | 2024-03-03 Bob MATH201 882. Window Functions: the OVER Clause
A window function computes a value for each row using a set of related rows (its window) without collapsing the rows the way GROUP BY does.
function_name(args) OVER (
[PARTITION BY expr, ...] -- split rows into independent groups
[ORDER BY expr [ASC|DESC], ...] -- order within each partition
[frame_clause] -- which rows around the current row (Section 5)
)GROUP BY collapses, OVER keeps every row
-- GROUP BY: one row per course
SELECT c.course_code, COUNT(*) AS n, ROUND(AVG(e.grade), 2) AS avg_grade
FROM enrollments e JOIN courses c ON c.course_id = e.course_id
GROUP BY c.course_code;
-- CS101 4 85.00 | MATH201 4 85.25
-- OVER: every enrollment row, with its course's statistics attached
SELECT c.course_code, s.first_name, e.grade,
ROUND(AVG(e.grade) OVER (PARTITION BY c.course_code), 2) AS course_avg,
ROUND(e.grade - AVG(e.grade) OVER (PARTITION BY c.course_code), 2) AS diff_from_avg,
COUNT(*) OVER (PARTITION BY c.course_code) AS class_size
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
ORDER BY c.course_code, e.grade DESC, s.first_name;+-------------+------------+-------+------------+---------------+------------+
| course_code | first_name | grade | course_avg | diff_from_avg | class_size |
+-------------+------------+-------+------------+---------------+------------+
| CS101 | Alice | 92.00 | 85.00 | 7.00 | 4 |
| CS101 | Bob | 85.00 | 85.00 | 0.00 | 4 |
| CS101 | Eve | 85.00 | 85.00 | 0.00 | 4 |
| CS101 | Carol | 78.00 | 85.00 | -7.00 | 4 |
| MATH201 | Carol | 95.00 | 85.25 | 9.75 | 4 |
| MATH201 | Alice | 88.00 | 85.25 | 2.75 | 4 |
| MATH201 | Bob | 88.00 | 85.25 | 2.75 | 4 |
| MATH201 | Dan | 70.00 | 85.25 | -15.25 | 4 |
+-------------+------------+-------+------------+---------------+------------+The same question in Lesson 11 needed a correlated subquery per column. Here the window is computed once per partition.
Where window functions may appear
Only in the SELECT list and ORDER BY. They are evaluated after WHERE, GROUP BY and HAVING, so you cannot filter on them directly — wrap the query in a CTE or derived table (Section 7).
3. Ranking Functions
SELECT c.course_code, s.first_name, e.grade,
ROW_NUMBER() OVER (PARTITION BY c.course_code ORDER BY e.grade DESC, s.first_name) AS row_num,
RANK() OVER (PARTITION BY c.course_code ORDER BY e.grade DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY c.course_code ORDER BY e.grade DESC) AS dense_rnk
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
ORDER BY c.course_code, row_num;+-------------+------------+-------+---------+-----+-----------+
| course_code | first_name | grade | row_num | rnk | dense_rnk |
+-------------+------------+-------+---------+-----+-----------+
| CS101 | Alice | 92.00 | 1 | 1 | 1 |
| CS101 | Bob | 85.00 | 2 | 2 | 2 |
| CS101 | Eve | 85.00 | 3 | 2 | 2 | <- tie
| CS101 | Carol | 78.00 | 4 | 4 | 3 |
| MATH201 | Carol | 95.00 | 1 | 1 | 1 |
| MATH201 | Alice | 88.00 | 2 | 2 | 2 |
| MATH201 | Bob | 88.00 | 3 | 2 | 2 | <- tie
| MATH201 | Dan | 70.00 | 4 | 4 | 3 |
+-------------+------------+-------+---------+-----+-----------+| Function | Ties | Gaps after ties | Use for |
|---|---|---|---|
ROW_NUMBER() |
broken arbitrarily unless ORDER BY is unique |
— | de-duplication, pagination, "exactly one row per group" |
RANK() |
same rank | yes (1, 2, 2, 4) | competition ranking |
DENSE_RANK() |
same rank | no (1, 2, 2, 3) | "top 3 distinct scores" |
Always add a tie-breaker to ROW_NUMBER's ORDER BY (here s.first_name); otherwise the same query can return different numbering on different runs.
Top-N per group
Window functions cannot be used in WHERE, so rank in a CTE and filter outside:
WITH ranked AS (
SELECT c.course_code, s.first_name, e.grade,
DENSE_RANK() OVER (PARTITION BY c.course_code ORDER BY e.grade DESC) AS dense_rnk
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
)
SELECT course_code, first_name, grade
FROM ranked
WHERE dense_rnk <= 2
ORDER BY course_code, grade DESC, first_name;
-- CS101: Alice 92, Bob 85, Eve 85 MATH201: Carol 95, Alice 88, Bob 88De-duplication: keep the latest enrollment per student
WITH numbered AS (
SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enrollment_date DESC, enrollment_id DESC) AS rn
FROM enrollments e
)
SELECT student_id, course_id, enrollment_date
FROM numbered
WHERE rn = 1
ORDER BY student_id;
-- 1 → MATH201 2024-02-05 | 2 → MATH201 2024-03-03 | 3 → MATH201 2024-02-07 | 4 → MATH201 2024-03-01 | 5 → CS101 2024-01-15The same pattern with DELETE removes duplicate rows while keeping one.
4. Aggregates as Window Functions
Any aggregate (SUM, AVG, COUNT, MIN, MAX) becomes a window function with OVER. Adding ORDER BY inside OVER turns it into a running aggregate.
Running total of credits per student
SELECT s.first_name, c.course_code, e.enrollment_date, c.credits,
SUM(c.credits) OVER (PARTITION BY s.student_id
ORDER BY e.enrollment_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_credits
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
ORDER BY s.first_name, e.enrollment_date;+------------+-------------+-----------------+---------+-----------------+
| first_name | course_code | enrollment_date | credits | running_credits |
+------------+-------------+-----------------+---------+-----------------+
| Alice | CS101 | 2024-01-10 | 3 | 3 |
| Alice | MATH201 | 2024-02-05 | 4 | 7 |
| Bob | CS101 | 2024-01-12 | 3 | 3 |
| Bob | MATH201 | 2024-03-03 | 4 | 7 |
| Carol | CS101 | 2024-02-02 | 3 | 3 |
| Carol | MATH201 | 2024-02-07 | 4 | 7 |
| Dan | MATH201 | 2024-03-01 | 4 | 4 |
| Eve | CS101 | 2024-01-15 | 3 | 3 |
+------------+-------------+-----------------+---------+-----------------+Share of total
SELECT d.dept_name, d.budget,
ROUND(100 * d.budget / SUM(d.budget) OVER (), 1) AS pct_of_total
FROM departments d;An empty OVER () makes the window the whole result set.
5. Window Frames: ROWS and RANGE
When OVER has an ORDER BY, each row's window is a frame of rows around it:
ROWS BETWEEN <start> AND <end>
UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING| Frame | Meaning |
|---|---|
| (default, with ORDER BY) | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — from the start through all peers of the current row |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
running total, physical rows |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW |
3-row moving window |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING |
centred 3-row window |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
whole partition (needed for LAST_VALUE) |
ROWS counts physical rows; RANGE groups rows with equal ORDER BY values (peers) together. With duplicate dates, a RANGE running total jumps by the whole group at once while ROWS increases row by row. Say ROWS explicitly when you mean rows.
Moving average
WITH monthly AS (
SELECT DATE_FORMAT(enrollment_date, '%Y-%m') AS month, COUNT(*) AS enrollments
FROM enrollments
GROUP BY month
)
SELECT month, enrollments,
ROUND(AVG(enrollments) OVER (ORDER BY month
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 2) AS moving_avg_2
FROM monthly
ORDER BY month;
-- 2024-01 3 3.00 | 2024-02 3 3.00 | 2024-03 2 2.50The first row has only itself in its frame, so its "2-month average" is just its own value — decide whether that is acceptable or whether you should drop the first n-1 rows.
6. LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTILE
Month-over-month change with LAG
WITH monthly AS (
SELECT DATE_FORMAT(enrollment_date, '%Y-%m') AS month, COUNT(*) AS enrollments
FROM enrollments
GROUP BY month
)
SELECT month, enrollments,
LAG(enrollments) OVER (ORDER BY month) AS prev_month,
enrollments - LAG(enrollments) OVER (ORDER BY month) AS `change`,
ROUND(100.0 * (enrollments - LAG(enrollments) OVER (ORDER BY month))
/ LAG(enrollments) OVER (ORDER BY month), 1) AS pct_change
FROM monthly
ORDER BY month;+---------+-------------+------------+--------+------------+
| month | enrollments | prev_month | change | pct_change |
+---------+-------------+------------+--------+------------+
| 2024-01 | 3 | NULL | NULL | NULL |
| 2024-02 | 3 | 3 | 0 | 0.0 |
| 2024-03 | 2 | 3 | -1 | -33.3 |
+---------+-------------+------------+--------+------------+LAG(col, n, default) looks n rows back (default 1) and returns default (default NULL) when there is no such row; LEAD looks forward. Note that months with no enrollments are missing, so LAG compares with the previous existing month — Section 8 fixes this with a generated month series.
FIRST_VALUE / LAST_VALUE and the default-frame trap
SELECT c.course_code, s.first_name, e.grade,
FIRST_VALUE(s.first_name) OVER w AS top_student,
LAST_VALUE(s.first_name) OVER w AS wrong_bottom,
LAST_VALUE(s.first_name) OVER (PARTITION BY c.course_code
ORDER BY e.grade DESC, s.first_name
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS bottom_student
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
WINDOW w AS (PARTITION BY c.course_code ORDER BY e.grade DESC, s.first_name)
ORDER BY c.course_code, e.grade DESC, s.first_name;+-------------+------------+-------+-------------+--------------+----------------+
| course_code | first_name | grade | top_student | wrong_bottom | bottom_student |
+-------------+------------+-------+-------------+--------------+----------------+
| CS101 | Alice | 92.00 | Alice | Alice | Carol |
| CS101 | Bob | 85.00 | Alice | Bob | Carol |
| CS101 | Eve | 85.00 | Alice | Eve | Carol |
| CS101 | Carol | 78.00 | Alice | Carol | Carol |
| MATH201 | Carol | 95.00 | Carol | Carol | Dan |
| ... |
+-------------+------------+-------+-------------+--------------+----------------+With the default frame (start … current row) LAST_VALUE is always the current row. Give it an explicit frame that extends to UNBOUNDED FOLLOWING. The WINDOW w AS (...) clause names a window so it can be reused by several functions.
NTILE: buckets
SELECT first_name, gpa, NTILE(2) OVER (ORDER BY gpa DESC) AS half
FROM students
ORDER BY gpa DESC, first_name;
-- Alice 1 | Carol 1 | Bob 1 | Eve 2 | Dan 2 (5 rows → buckets of 3 and 2)NTILE(4) gives quartiles, NTILE(100) percentiles; earlier buckets get the extra rows when the count does not divide evenly.
7. Common Table Expressions
A CTE is a named result set that lives for one statement:
WITH name1 [(col, ...)] AS (SELECT ...),
name2 AS (SELECT ... FROM name1 ...) -- may reference earlier CTEs
SELECT ... FROM name2 ...;Compared with a derived table (Lesson 11):
| Derived table | CTE | |
|---|---|---|
| Readability | inside-out | top-down, each step named |
| Reuse in the same statement | repeat the subquery | reference by name as often as needed |
| Recursion | no | WITH RECURSIVE |
| Allowed in | SELECT |
SELECT, UPDATE, DELETE, INSERT ... SELECT |
Chaining steps
WITH course_stats AS (
SELECT course_id, AVG(grade) AS avg_grade, COUNT(*) AS n
FROM enrollments
WHERE status = 'completed'
GROUP BY course_id
),
above_avg AS (
SELECT e.student_id, e.course_id
FROM enrollments e
JOIN course_stats cs ON cs.course_id = e.course_id
WHERE e.grade > cs.avg_grade
)
SELECT s.first_name, COUNT(*) AS courses_above_average
FROM above_avg a
JOIN students s ON s.student_id = a.student_id
GROUP BY s.first_name
ORDER BY courses_above_average DESC, s.first_name;
-- Alice 2 | Bob 1 | Carol 1MySQL may materialize a CTE (compute it once into a temporary table) or merge it into the outer query, exactly as it does for derived tables; EXPLAIN shows which. A CTE is not an index — if the same heavy CTE is used in many statements, consider a view (Lesson 12) or a summary table.
8. Recursive CTEs
A recursive CTE references itself. It has an anchor part (the starting rows) and a recursive part that is applied repeatedly until it produces no new rows:
WITH RECURSIVE cte AS (
SELECT ... -- anchor
UNION ALL
SELECT ... FROM cte JOIN ... WHERE <termination condition> -- recursive member
)
SELECT * FROM cte;Generating a series
WITH RECURSIVE seq (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 5
)
SELECT n FROM seq;
-- 1 2 3 4 5Filling in missing months
WITH RECURSIVE months AS (
SELECT DATE('2024-01-01') AS month_start
UNION ALL
SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months
WHERE month_start < '2024-04-01'
),
monthly AS (
SELECT DATE_FORMAT(enrollment_date, '%Y-%m-01') AS month_start, COUNT(*) AS n
FROM enrollments
GROUP BY month_start
)
SELECT DATE_FORMAT(m.month_start, '%Y-%m') AS month, COALESCE(mo.n, 0) AS enrollments
FROM months m
LEFT JOIN monthly mo ON mo.month_start = m.month_start
ORDER BY m.month_start;
-- 2024-01 3 | 2024-02 3 | 2024-03 2 | 2024-04 0 <- April now appears with 0Feed this result into the LAG query of Section 6 and the month-over-month change becomes correct even across empty months.
Walking a hierarchy: course prerequisites
The course database has no hierarchy table, so create a small one for this example:
CREATE TABLE course_prerequisites (
course_code VARCHAR(10) NOT NULL,
prereq_code VARCHAR(10) NOT NULL,
PRIMARY KEY (course_code, prereq_code)
);
INSERT INTO course_prerequisites VALUES
('CS201', 'CS101'), ('CS301', 'CS201'), ('CS301', 'MATH201');
WITH RECURSIVE chain AS (
SELECT course_code, prereq_code, 1 AS depth,
CAST(prereq_code AS CHAR(200)) AS path
FROM course_prerequisites
WHERE course_code = 'CS301' -- anchor: direct prerequisites
UNION ALL
SELECT p.course_code, p.prereq_code, chain.depth + 1,
CONCAT(chain.path, ' -> ', p.prereq_code)
FROM course_prerequisites p
JOIN chain ON p.course_code = chain.prereq_code -- prerequisites of prerequisites
)
SELECT prereq_code, depth, path
FROM chain
ORDER BY depth, prereq_code;+-------------+-------+-----------------+
| prereq_code | depth | path |
+-------------+-------+-----------------+
| CS201 | 1 | CS201 |
| MATH201 | 1 | MATH201 |
| CS101 | 2 | CS201 -> CS101 |
+-------------+-------+-----------------+The CAST(... AS CHAR(200)) in the anchor matters: MySQL fixes the column type from the anchor, and without it the growing path would be truncated to the anchor's width.
Safety
- A cycle (A requires B, B requires A) recurses forever. MySQL stops at
cte_max_recursion_depth(default 1000) with error 3636; raise it only deliberately:SET SESSION cte_max_recursion_depth = 10000;. - To survive cycles in data, carry the path and add
WHERE FIND_IN_SET(p.prereq_code, chain.path_csv) = 0to the recursive member. - Use
UNION DISTINCTinstead ofUNION ALLto stop when a row repeats (slower, but terminates on cycles).
9. Transactions: START, COMMIT, ROLLBACK, SAVEPOINT
A transaction groups statements so that either all of them take effect or none of them do.
START TRANSACTION; -- or BEGIN
UPDATE departments SET budget = budget - 50000 WHERE dept_name = 'Computer Science';
UPDATE departments SET budget = budget + 50000 WHERE dept_name = 'Mathematics';
-- Check the invariant before committing
SELECT dept_name, budget FROM departments WHERE dept_name IN ('Computer Science', 'Mathematics');
COMMIT; -- make both changes permanent
-- or ROLLBACK; -- undo bothIf the connection drops between the two UPDATEs, InnoDB rolls the first one back automatically — money never disappears.
Autocommit
By default MySQL runs with autocommit = 1: every single statement is its own transaction. START TRANSACTION suspends autocommit until COMMIT/ROLLBACK. You can also turn it off for a session:
SET autocommit = 0; -- now nothing is permanent until you COMMITSAVEPOINT: partial rollback
START TRANSACTION;
INSERT INTO students (first_name, last_name, email) VALUES ('Grace', 'Hopper', 'grace@example.com');
SAVEPOINT after_student;
INSERT INTO enrollments (student_id, course_id) VALUES (LAST_INSERT_ID(), 999); -- fails: no course 999
ROLLBACK TO SAVEPOINT after_student; -- undo only the enrollment; Grace stays
COMMIT; -- Grace is committed, the bad enrollment never wasStatements that commit implicitly
DDL (CREATE, ALTER, DROP, TRUNCATE), LOCK TABLES, and START TRANSACTION itself implicitly commit the current transaction in MySQL. Never mix schema changes into a transaction you expect to roll back.
10. ACID and Isolation Levels
| Property | Meaning | Who provides it in MySQL |
|---|---|---|
| Atomicity | all or nothing | undo log, ROLLBACK |
| Consistency | constraints hold before and after | constraints + your transaction boundaries |
| Isolation | concurrent transactions do not see each other's half-done work | MVCC snapshots + locks |
| Durability | once committed, survives a crash | redo log (innodb_flush_log_at_trx_commit = 1) |
The anomalies
| Anomaly | What you observe |
|---|---|
| Dirty read | you read a row another transaction has changed but not yet committed (it may roll back) |
| Non-repeatable read | you read the same row twice in one transaction and get different values because someone committed in between |
| Phantom read | you run the same WHERE twice and get new rows because someone inserted and committed in between |
| Lost update | two transactions read the same value, both compute a new one, the second UPDATE overwrites the first |
The four isolation levels
| Level | Dirty | Non-repeatable | Phantom | Notes |
|---|---|---|---|---|
READ UNCOMMITTED |
possible | possible | possible | almost never appropriate |
READ COMMITTED |
no | possible | possible | default in PostgreSQL, Oracle, SQL Server |
REPEATABLE READ |
no | no | possible per the standard; InnoDB prevents them for plain reads via consistent snapshots and for locking reads via next-key locks | MySQL/InnoDB default |
SERIALIZABLE |
no | no | no | plain SELECTs become SELECT ... FOR SHARE; most blocking |
SELECT @@transaction_isolation; -- REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- for this connection
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- for new connectionsSee it yourself: two sessions
Open two mysql clients (A and B) on the course database.
-- Session A -- Session B
START TRANSACTION;
SELECT gpa FROM students WHERE student_id = 2; -- 3.40
UPDATE students SET gpa = 3.50 WHERE student_id = 2;
-- (autocommit) committed immediately
SELECT gpa FROM students WHERE student_id = 2;
-- REPEATABLE READ: still 3.40 (snapshot taken at A's first read)
-- READ COMMITTED : now 3.50 (each statement sees the latest commit)
COMMIT;
SELECT gpa FROM students WHERE student_id = 2; -- 3.50 in both casesUnder REPEATABLE READ, session A's snapshot is established by its first consistent read, not by START TRANSACTION (use START TRANSACTION WITH CONSISTENT SNAPSHOT to take it immediately).
Preventing lost updates
-- Wrong: read, compute in the application, write
SELECT budget FROM departments WHERE dept_id = 1; -- 500000
-- (another session also reads 500000 and writes 450000)
UPDATE departments SET budget = 480000 WHERE dept_id = 1; -- overwrites their change
-- Right 1: compute in the UPDATE itself (atomic)
UPDATE departments SET budget = budget - 20000 WHERE dept_id = 1;
-- Right 2: lock the row while you decide
START TRANSACTION;
SELECT budget FROM departments WHERE dept_id = 1 FOR UPDATE; -- others wait here
UPDATE departments SET budget = budget - 20000 WHERE dept_id = 1;
COMMIT;
-- Right 3: optimistic locking with a version column
UPDATE departments SET budget = 480000, version = version + 1
WHERE dept_id = 1 AND version = 7; -- 0 rows affected → someone else changed it, retry11. Locks, Lock Waits and Deadlocks
InnoDB uses row locks. Plain SELECTs take no locks (they read a snapshot); UPDATE, DELETE, SELECT ... FOR UPDATE and SELECT ... FOR SHARE lock the rows they touch — and, under REPEATABLE READ, the gaps between index records (next-key locks) to stop phantoms.
SELECT ... FOR UPDATE; -- exclusive lock, others' writes wait
SELECT ... FOR SHARE; -- shared lock, others may read-lock but not write
SELECT ... FOR UPDATE NOWAIT; -- fail immediately (error 3572) instead of waiting
SELECT ... FOR UPDATE SKIP LOCKED; -- skip rows someone else holds (job queues)Lock wait timeout
If a row you need is locked, your statement waits up to innodb_lock_wait_timeout seconds (default 50) and then fails with error 1205 Lock wait timeout exceeded. The usual cause is a transaction left open in another session — a forgotten COMMIT in a client, or an application that opened a transaction and then waited on the user.
Deadlocks
-- Session A -- Session B
START TRANSACTION; START TRANSACTION;
UPDATE students SET gpa = 3.5 WHERE student_id = 1;
UPDATE students SET gpa = 3.6 WHERE student_id = 2;
UPDATE students SET gpa = 3.5 WHERE student_id = 2; -- waits for B
UPDATE students SET gpa = 3.6 WHERE student_id = 1; -- waits for A
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionInnoDB detects the cycle immediately and rolls back one of the transactions (the one that changed fewer rows). Your application must be prepared to retry a transaction that fails with 1213. SHOW ENGINE INNODB STATUS\G prints the "LATEST DETECTED DEADLOCK" section with both statements.
Avoiding deadlocks:
- Touch rows in a consistent order in every transaction (e.g. ascending primary key).
- Keep transactions short; never wait for user input inside one.
- Lock everything you need up front with one
SELECT ... FOR UPDATE ... ORDER BY id. - Index the columns in your
WHEREclauses — anUPDATEwithout a usable index locks every row it scans, not just the ones it changes.
12. Transactions from Python (SQLAlchemy)
Lesson 16 introduced sessions; here is the minimal correct pattern for the two-UPDATE transfer, with a retry on deadlock:
from sqlalchemy import create_engine, text
from sqlalchemy.exc import OperationalError
import time
engine = create_engine("mysql+pymysql://user:pass@localhost/university")
def transfer_budget(from_dept, to_dept, amount, retries=3):
for attempt in range(retries):
try:
with engine.begin() as conn: # BEGIN … COMMIT, ROLLBACK on exception
conn.execute(text("""
UPDATE departments SET budget = budget - :amt
WHERE dept_name = :d AND budget >= :amt"""),
{"amt": amount, "d": from_dept})
conn.execute(text("""
UPDATE departments SET budget = budget + :amt
WHERE dept_name = :d"""),
{"amt": amount, "d": to_dept})
return True
except OperationalError as exc:
if exc.orig.args[0] in (1213, 1205) and attempt < retries - 1: # deadlock / lock wait
time.sleep(0.1 * (attempt + 1))
continue
raiseengine.begin() commits when the block exits normally and rolls back on any exception. The budget >= :amt guard moves the business rule into the atomic UPDATE, so no separate SELECT is needed and no lost update is possible. Check result.rowcount if you need to know whether the guard rejected the transfer.
13. Best Practices
- Add a tie-breaker to every
ROW_NUMBERwindow so results are deterministic. - State the frame explicitly (
ROWS BETWEEN …) for running totals andLAST_VALUE. - Rank in a CTE, filter outside — window functions cannot appear in
WHEREorHAVING. - Name repeated windows with the
WINDOWclause. - Anchor recursive CTEs with the right column types (
CAST), and keep a depth guard. - Keep transactions short and never hold one open across user interaction or network calls.
- Write updates as
SET col = col ± xor useFOR UPDATE/ a version column to prevent lost updates. - Retry on 1213 and 1205 in application code; treat them as normal events, not bugs.
- Know your isolation level (
SELECT @@transaction_isolation) and that DDL commits implicitly.
14. Common Pitfalls
| Pitfall | Symptom | Fix |
|---|---|---|
WHERE ROW_NUMBER() OVER (...) = 1 |
syntax/semantic error | rank in a CTE, filter outside |
LAST_VALUE with default frame |
always returns the current row | ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
RANGE default with duplicate dates |
running total jumps by whole groups | use ROWS |
LAG over months with gaps |
compares with the wrong month | generate the series with a recursive CTE, LEFT JOIN |
| Recursive CTE never ends | error 3636 after 1000 levels | fix the termination condition; guard against cycles |
Truncated path in recursive CTE |
strings cut off | CAST(... AS CHAR(n)) in the anchor |
ALTER TABLE inside a transaction |
earlier changes silently committed | run DDL outside transactions |
| Read-modify-write in the application | lost updates under load | atomic UPDATE, FOR UPDATE, or version column |
| Transaction left open in a client | other sessions hang, then error 1205 | COMMIT/ROLLBACK; check information_schema.INNODB_TRX |
UPDATE without an index on the WHERE column |
whole table locked | add the index |
15. Exercises
- For every enrollment show the student's name, the course, the grade and the student's best grade so far in chronological order (running maximum).
- Find, for each course, the student with the second-highest grade. Decide whether
RANKorDENSE_RANKfits and explain your choice. - Produce a report of enrollments per month from January to June 2024 that shows every month (0 where there were none) together with the month-over-month change.
- Write a recursive CTE that lists all courses that depend on
CS101, directly or indirectly, using thecourse_prerequisitestable from Section 8. - Write a transaction that enrols student 5 in course
CS201only if the course currently has fewer than 30 enrollments, in a way that stays correct when two advisers run it at the same time. - Two sessions both run
UPDATE enrollments SET grade = grade + 5 WHERE course_id = 2;andUPDATE enrollments SET grade = grade + 5 WHERE course_id = 1;but in opposite orders. What can happen, and how do you prevent it?
Solutions
1.
SELECT s.first_name, c.course_code, e.enrollment_date, e.grade,
MAX(e.grade) OVER (PARTITION BY e.student_id ORDER BY e.enrollment_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS best_so_far
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
ORDER BY s.first_name, e.enrollment_date;
-- Alice: 92, 92 | Bob: 85, 88 | Carol: 78, 95 | Dan: 70 | Eve: 85DENSE_RANK() = 2 returns the second-highest distinct grade (CS101: Bob and Eve with 85; MATH201: Alice and Bob with 88). RANK() = 2 gives the same here, but if two students tied for first, RANK would skip 2 entirely and return nobody, so DENSE_RANK is the robust choice for "second-highest score".
3.
WITH RECURSIVE months AS (
SELECT DATE('2024-01-01') AS m
UNION ALL
SELECT DATE_ADD(m, INTERVAL 1 MONTH) FROM months WHERE m < '2024-06-01'
),
monthly AS (
SELECT DATE_FORMAT(enrollment_date, '%Y-%m-01') AS m, COUNT(*) AS n
FROM enrollments GROUP BY m
),
filled AS (
SELECT months.m, COALESCE(monthly.n, 0) AS n
FROM months LEFT JOIN monthly ON monthly.m = months.m
)
SELECT DATE_FORMAT(m, '%Y-%m') AS month, n,
n - LAG(n) OVER (ORDER BY m) AS `change`
FROM filled ORDER BY m;
-- 2024-01 3 NULL | 2024-02 3 0 | 2024-03 2 -1 | 2024-04 0 -2 | 2024-05 0 0 | 2024-06 0 0prereq_code = 'CS101' and follow course_code upward.
WITH RECURSIVE dependents AS (
SELECT course_code, 1 AS depth FROM course_prerequisites WHERE prereq_code = 'CS101'
UNION ALL
SELECT p.course_code, d.depth + 1
FROM course_prerequisites p JOIN dependents d ON p.prereq_code = d.course_code
)
SELECT DISTINCT course_code, depth FROM dependents ORDER BY depth, course_code;
-- CS201 (1), CS301 (2)START TRANSACTION;
SELECT course_id FROM courses WHERE course_code = 'CS201' FOR UPDATE; -- serializes on this course
SELECT COUNT(*) INTO @n FROM enrollments e
JOIN courses c ON c.course_id = e.course_id WHERE c.course_code = 'CS201';
-- application (or a stored procedure) checks @n < 30, then:
INSERT INTO enrollments (student_id, course_id)
SELECT 5, course_id FROM courses WHERE course_code = 'CS201';
COMMIT;FOR UPDATE, two advisers can both count 29 under REPEATABLE READ and both insert, giving 31. A UNIQUE (student_id, course_id) constraint additionally prevents double enrolment of the same student.
6. Each session locks the rows of one course and then waits for the rows of the other: a deadlock. InnoDB detects it, rolls back one session with error 1213, and the other completes. Prevent it by updating in the same order in every code path (e.g. course_id ascending), or by doing both updates in one statement: UPDATE enrollments SET grade = grade + 5 WHERE course_id IN (1, 2);, and always retry on 1213.
🎯 Summary
OVER (PARTITION BY … ORDER BY … frame)adds per-group calculations to every row;GROUP BYcollapses rows.ROW_NUMBER/RANK/DENSE_RANKdiffer only in how they treat ties;LAG/LEADcompare neighbouring rows;LAST_VALUEneeds an explicit frame.- CTEs name intermediate steps; recursive CTEs generate series and walk hierarchies, with
cte_max_recursion_depthas a safety net. - Transactions give all-or-nothing writes; InnoDB defaults to
REPEATABLE READwith snapshot reads and row/next-key locks. - Prevent lost updates with atomic
UPDATEs,FOR UPDATEor version columns; keep transactions short, order row access consistently, and retry on deadlock.
🔄 Next Steps
- Revisit Lesson 15 and run
EXPLAINon your window-function and CTE queries - Lesson 16-17: apply the transaction patterns through SQLAlchemy sessions and the Unit of Work pattern
- Build the course project's reporting endpoints with CTEs and window functions instead of application-side loops
📚 Additional Resources
- MySQL Reference Manual: Window Functions
- MySQL Reference Manual: WITH (Common Table Expressions)
- MySQL Reference Manual: Transaction Isolation Levels
- MySQL Reference Manual: Deadlocks in InnoDB
- Use The Index, Luke: Window Functions