The Night the Payment Button Wouldn’t Click — When Coupons, Carts, and Checkout Pages Crashed the Database All at Once, from Reproduction to Recovery

At 8 p.m., a coupon event goes live, and the entire checkout flow collapses at once. Recreate it with Docker: the CPU burned by hundreds of coupons in users’ coupon wallets, coupon validations repeated on every screen, and the flood of INSERTs and UPDATEs as customers move from cart to order form. Then follow the night’s timeline from the war room to see what gets cut first—and what gets fixed.

1 learners are taking this course

Level Intermediate

Course period Unlimited

SQL
SQL
MySQL
MySQL
DBMS/RDBMS
DBMS/RDBMS
database
database
SQL
SQL
MySQL
MySQL
DBMS/RDBMS
DBMS/RDBMS
database
database

What you will gain after the course

  • We count the number of SQL calls generated by each payment request to identify amplification, such as repeated coupon validation across screens.

  • We use an execution plan to see what a coupon wallet reads for a member with hundreds of coupons, then measure to find the real CPU culprit that indexes alone can’t fix.

  • Bundle or eliminate the small writes and frequent commits when moving from the cart to checkout to reduce database writes and response times.

  • We reproduce the race condition that lets the same coupon be used twice, then prevent it with a single conditional UPDATE statement.

  • Set the order for disabling features during an incident and re-enabling them based on metrics, and prepare post-incident analyses, recurrence-prevention measures, and pre-event checklists.

  • Directly reads the data_locks, INNODB STATUS, and metadata_locks output to explain the causes of record, gap, and next-key locks, deadlocks, and metadata lock queues.

  • Compare pessimistic locking, conditional UPDATEs, optimistic locking (version), and atomic increments under the same concurrent requests, then choose the right approach for the situation based on the results and costs.

  • Measure the redo and fsync costs of a single commit with counters, and turn batched writes, idempotency keys, and pool deadlock fixes into actual code diffs.

At 8 p.m., the payment button won’t respond. We recreate the night when the coupon wallet, coupon validation, and order form all brought down the database at once, then walk through what to cut off first and what to fix.

The Numbers from That Night — What This Course Reproduces and Fixes

  • Within 30 seconds of launch, p95 response time: 90ms → 2,337ms; DB connection wait: 381 → 539

  • Average during the launch collapse: p95 2,399ms · 12.5% failure rate

  • Three war-room decisions later, under the same load: p95 77ms · 0% failures

  • Meanwhile, because some people clicked the payment button twice, 816 coupons were used twice—reduced to 0 with a single conditional UPDATE statement.

  • Step-load test after the improvements: initially, p95 was 1,125ms at 60 users per second; after the improvements, it was 76ms even at 100 users per second.

All the numbers are records from actually running a simulated online store. Lesson 1 begins with the collapse that night, without any explanation.

Advanced Course, Lecture 39 — Numbers That Measure the Principles Directly

  • With 200 coupons and four identical requests each: read-then-write creates 799 usage records (599 duplicates); conditional UPDATE, FOR UPDATE, and optimistic locking create 200—but their costs differ: 36 lock waits, 599 lock waits, and 600 conflicts, respectively.

  • The same 2,000-row write: autocommit fsync 2,058 times → one transaction 277 times → multi-row INSERT 2 times

  • With one open transaction, the history list goes from 66 to 1,205—something response metrics don’t show.

  • After a single ALTER during an event, 61 connections were left waiting, and 25.6% of coupon wallet lookups failed.

  • Code that checks out one more connection while holding a transaction: under the same load, failures rose from 0% to 57.2%

  • Payment double-clicks: 30 orders from 20 members without idempotency keys → 20 orders with idempotency keys

Quiz 4 includes 40 questions that require you to read the actual output and make judgments, and all 9 assignments, including 3 additional tasks, are automatically graded as pass or fail using setup and verify scripts.

At 8 PM, the coupon event opens, and the entire pre-payment flow collapses at once. Recreate with Docker the CPU load caused by coupon wallets containing hundreds of coupons, coupon validations repeated on every screen, and the flood of INSERT·UPDATE operations as users move from cart to order form. Then follow that night’s timeline in the war room to see what to cut first and what to fix.

Most coupon courses end with “issuance.” This course goes beyond issuance to show how coupon wallet lookups, coupon-use validation, cart validation, and order creation become entangled and bring things crashing down on the path to checkout—one night. It recreates and documents “that night” in a fictional online store, with every lesson sharing the same timeline in a cinematic format. Every number on screen comes from running that recreation, and the course follows the decision-making process used in an incident war room by a DBA team lead at a major e-commerce company. After the 50 main lessons come 39 advanced lessons. They take fixes made with a single configuration line in the main course and revisit them from first principles, digging into EXPLAIN, optimizer traces, lock tables, fsync counters, and actual code. You’ll test your understanding with output-reading quizzes and automatically graded assignments.

By the end of this course, you will be able to

  • Count the number of SQL calls generated by a single payment request and identify amplification, such as coupon validation repeated on every screen.

  • Use an execution plan to see what the coupon wallet reads for a member with hundreds of coupons, then use measurements to find the real CPU culprit that indexes alone can’t fix.

  • Bundle or eliminate the small writes and frequent commits when moving from the cart to checkout, reducing database write volume and response time.

  • Reproduce the race condition that lets the same coupon be used twice, and prevent it with a single conditional UPDATE statement.

  • Determine the order for disabling and re-enabling features during an outage based on metrics, and write postmortems, prevention measures, and pre-event checklists.

  • Read data_locks·INNODB STATUS·metadata_locks output directly and explain the causes of record, gap, and next-key locks, deadlocks, and metadata lock wait queues.

  • Apply pessimistic locking, conditional UPDATEs, optimistic locking (version), and atomic increments to the same concurrent requests, compare the results and costs, and choose the right approach for each situation.

  • Measure the redo and fsync costs of a single commit using counters, then apply batched writes, idempotency keys, and connection pool deadlock fixes as actual code diffs.

Who This Is For

  • E-commerce backend developers (2–7 years of experience) working with coupons, shopping carts, and orders

  • DBAs and SREs who need to check database limits ahead of major events

  • Those who have run load tests but found it difficult to say “which part is the culprit”

Curriculum (13 sections · 89 lessons · approximately 1,091 minutes)

  • Section 0. Prologue — 8 p.m., the checkout button — At 8 p.m., the checkout button won’t respond, the hands-on environment and Loop Market’s pre-checkout flow, reading the war room dashboard, a calm baseline

  • Section 1. Act 1 — The coupon wallet burns CPU — When one member has hundreds of coupons, the execution plan for a coupon wallet lookup — It reads even used-up coupons, When the DB calculates applicable coupons, reducing the number of rows read with index ordering and covering indexes, Indexes alone weren’t enough, Moving recommended coupon calculations to the app, Coupon wallet cache — What to cache and what to check every time, When the expired coupon cleanup batch runs during an event, Act 1 results — Same load, different graphs

  • Section 2. Act 2 — Coupon validation hammers the database — How many coupon validation queries run per payment, repeating the same validation on every screen, the gap between checking and using — the same coupon gets used twice, check and use in one statement with a conditional UPDATE, coupon usage history INSERTs pile up in one place, use a short cache and do the final check in the database, the trap of validation caching — an already-used coupon appears in the preview, Act 2 results — fewer calls mean less database load

  • Section 3. Act 3 — From Cart to Order Form: The Write Storm — What happens when moving to the order form, one UPDATE per item — small writes and frequent commits, a surge of INSERTs for saving order form drafts, the cost of deleting accumulated order forms, editing the same cart in two tabs, batch writes and reduce commits, eliminate writes that aren't necessary, Act 3 result — the write storm subsides

  • Section 4. Act 4 — The Night Everything Breaks at Once — Three areas fail simultaneously — it looks like there’s only one cause, What to check first when you enter the war room — the order to check metrics, Split the issue into three using pending events, Repeatedly tapping the payment button makes the outage worse, Connections run out and timeouts spread, What to disable first — the order to turn off features, Coupons may fail, but payments still work — graceful degradation, The order to turn things back on and the second wave, Act 4 outcome — a one-page timeline of that night

  • Section 5. Epilogue — The Next Event — Postmortem — What was decided, when, and why; prevention checklist and owners; rehearsal — Change the conditions and replay that night; pre-event payment flow checklist; measuring the limits of the next event; wrap-up and next steps

  • Section 6. Assignment — Facing an Unfamiliar Night Alone — Assignment 1 — A Member with a Thousand Coupons, Assignment 2 — A Payment That Triggers Validation Five Times, Assignment 3 — A Shopping Cart with Fifty Items, Assignment 4 — Concurrent Edits Across Three Tabs, Assignment 5 — Deciding the Kill Switch Order, Assignment 6 — An Unfamiliar Night

  • Section 7. Advanced 1 — War Room Diagnostic Toolbox — Diagnostic Query Kit — Five windows to open in the war room, when a query from 8.0 doesn’t work in 8.4, reading EXPLAIN columns — type·key·key_len·rows·filtered·Extra, EXPLAIN ANALYZE — when estimates and actuals diverge, optimizer trace — why did it choose that index, slow log and statement fingerprint — one load test on a single page

  • Section 8. Deep Dive 2 — A Closer Look at Reading the Coupon Box — Measuring three index column orders in practice, adding an index during an event — the progress and cost of online DDL, when a coupon IN list gets long — the limits of index dives, paging through the coupon box — from OFFSET to keyset pagination, when to use JSON and generated-column indexes, the midnight expiration boundary — time zones and half-open intervals

  • Section 9. Advanced 3 — Locks and Concurrency — InnoDB Lock Map — Records, gaps, next-key locks, and insert intentions; isolation levels and consistent reads — Why does SELECT still think it’s there?; comparing pessimistic locks, conditional UPDATEs, and optimistic locks; one use per person — two different coupons used simultaneously; deadlock autopsy — lock order for orders and coupons; locking a nonexistent row — gap locks and upsert deadlocks; a half-finished transaction after a lock wait timeout.

  • Section 10. Deep Dive 4 — Writes and Commits — What happens in a single commit — redo·fsync·group commit, The cost of durability settings — change them only in the lab, Comparing four solutions to cart lost updates, Batch-write code — multi-row INSERT·IN queries·ON DUPLICATE KEY, Bulk DELETE and purge — reading history list length

  • Section 11. Advanced 5 — Going Deeper into the Night of Operations — One long-running transaction drags out the entire night, ALTER TABLE during an event — metadata lock queue, limitations of INSTANT DDL — row versions and rebuilds, duplicate payment clicks — idempotency key design and implementation, one request uses two connections — connection pool deadlock, what happens when you validate on a replica

  • Section 12. Review and Wrap-Up — Quizzes, Extra Assignments, Interview Questions, and Resources — Act 1 Quiz — Reading the Coupon Wallet Output, Act 2 Quiz — Reading the Validation and Lock Output, Act 3 Quiz — Reading the Write and Commit Output, Act 4 Quiz — Reviewing the Decision Made That Night, Assignment 7 — Fix the Order Using Only the Deadlock Log, Assignment 8 — Clear the Metadata Lock Queue, Assignment 9 — Prevent Duplicate Payment Requests, Twenty Interview Questions — The Pre-Payment Flow and the Database, How to Use the Downloadable Resources — Query Pack, Cheat Sheet, and Runbook

Hands-on Lab Environment

With Docker, we launch MySQL 8.4, three instances of the shopping mall API, the load-testing tool k6, and a war room dashboard with metrics updated every second. The data for Loopmarket, a virtual shopping mall (200,000 members, approximately 2.75 million coupons, and about 350,000 cart rows), is generated using fixed rules so everyone gets the same data. Each lesson can be recreated with a single script, `./scenarios/sNN.sh`, and you can switch between the before and after versions with a one-line setting change (`./set.sh`). The downloadable materials include a war room diagnostic query kit, an event-day runbook, a completed sample postmortem, a cheat sheet, a kill switch sequence checklist, a capacity planning worksheet, 20 interview questions, and nine assignments automatically graded as pass or fail by the `setup` and `verify` scripts.

Prerequisites

  • SQL Joins and Index Basics

  • Experience working with web APIs and transactions

  • A computer with Docker installed (allocating at least 8 GB of memory is recommended)

What This Course Does Not Cover

  • Coupon Issuance and First-Come, First-Served Issuance Design

  • Payment gateway (PG) integration and authorization logic

  • Redis Inventory and Message Queue Migration

  • SQL Basics

Notice

The slide narration is generated using synthetic voices. All data is fictional and for educational purposes, and the numbers may vary depending on the execution environment.

Recommended for
these people

Who is this course right for?

  • Commerce Backend Developer (2–7 Years of Experience) Specializing in Coupons, Carts, and Orders

  • DBAs and SREs who need to check database limits ahead of a major event

  • For those who have run load tests but struggled to say 'which part is the culprit'

Need to know before starting?

  • The Basics of SQL Joins and Indexes

  • Experience working with web APIs and transactions

  • A computer with Docker installed (allocating at least 6 CPUs and 8 GB of memory is recommended)

Hello
This is dorian05202988

Career Verified

A data craftsman with over 20 years of experience in databases and IT. As a DBA and manager of a DB Platform team, I work with database platforms and specialize in MySQL, PostgreSQL, Oracle, MongoDB, SQL tuning, AWS, Terraform, and Python. Drawing on experience in the gaming, finance, and IT services industries, I explain database and cloud architecture. I have published 『Oracle Practical Tuning: Learning SQL Patterns』 and 『Oracle Pro*C Practical Project Guide』, and have presented on serverless architectures, database selection, and multi-database strategies at AWS Summit 2018 and 2019 and DataYa Nolja 2020. Recently, I have been working on slow-query analysis and tuning recommendations using Bedrock LLM, automated DB specification sizing, and query workflows through a DB metadata MCP server and Slack bot. I explain database automation for developers and AI applications for non-developers through hands-on exercises tailored to the audience and objectives of each course. Rather than applying AI suggestions as-is, I teach a decision-making framework that verifies result equivalence, execution plans, and observability conditions, and defers action when the supporting evidence is insufficient. Company operational data and credentials are not included in the course materials.

More

Curriculum

All

89 lectures ∙ (18hr 13min)

Course Materials:

Lecture resources
Published: 
Last updated: 

Reviews

Not enough reviews.
Please write a valuable review that helps everyone!

dorian05202988's other courses

Check out other courses by the instructor!

Similar courses

Explore other courses in the same field!

Limited time deal

$57,750.00

25%

$59.40