Mapping Database & Architecture Patterns onto an E‑Commerce High‑Concurrency Diagram

01 Indexes

MySQL indexes accelerate data lookup for large tables. B+Tree reduces I/O due to shallow depth. Leaf nodes are linked, enabling efficient range queries. Clustered indexes store the full row in leaf nodes. Non‑clustered indexes store indexed columns plus the primary‑key value, which may require a lookup (back‑table). Covering indexes contain all queried columns, eliminating the need for a lookup.

02 ACID and Isolation

ACID guarantees reliable transactions for operations such as payment, inventory deduction, and order creation. The four properties are Atomicity, Consistency, Isolation, and Durability. The three classic concurrency anomalies are dirty reads, non‑repeatable reads, and phantom reads. Preventing these anomalies ensures that payment status, inventory, and order state remain consistent.

03 MVCC and Locks

MVCC implements snapshot reads using historical row versions, reducing read‑write blocking. An Undo Log stores old versions for rollback; a version chain links multiple versions of a row; a ReadView determines which versions are visible to a transaction. Ordinary SELECT statements perform snapshot reads. UPDATE, DELETE, and SELECT FOR UPDATE require current reads and acquire locks. Locks resolve write‑write conflicts. Optimistic lock checks a version column before update; pessimistic lock acquires a lock before the operation. Next‑Key Lock combines record and gap locks to prevent phantom reads.

04 High Availability

High availability aims to minimize downtime (RTO) and data loss (RPO) after failures. Strategies include primary‑secondary failover, master‑slave replication (writes to master, reads from slaves with possible lag), and clustering to eliminate single points of failure.

05 Performance Optimization

Five major tactics: caching to reduce database hits; asynchronous processing via message queues for non‑critical work; database tuning (indexes, SQL optimization, read‑write splitting, sharding); horizontal scaling with multiple instances behind load balancers; and rate‑limiting/degradation to protect core functions. Bottlenecks should be located first by monitoring response time, throughput, concurrency, CPU, memory, disk, network, and DB connections.

06 Case‑Study Template

Answers should combine a technical point, a business object, and its effect. Example: “Use Redis/CDN to cache product details, categories, and hot‑product images, reducing DB access and response time.” The pattern is “For X problem, adopt Y solution, used for Z, achieving W.”

07 Problem Statement

During a promotion, an e‑commerce system experiences massive traffic, slow product pages, order‑API latency >5 s, DB CPU >90 %, synchronous inventory deduction, points, SMS, a single order‑server crash, and inventory‑display mismatches.

08 Architecture Overview

Requests first hit CDN/Cache for static assets and hot data, then pass through an API gateway (authentication, routing, rate limiting), followed by a load balancer distributing traffic to multiple stateless order‑service instances. Order services read from Redis cache, then persist core data to the database, which is tuned with indexes, SQL optimization, read‑write splitting, and optional sharding. Post‑order actions (SMS, points, logging) are sent to Kafka for asynchronous handling. Critical components (order service, Redis, Kafka, service registry) run in clusters; the database uses primary‑secondary replication. Monitoring, health checks, and automatic failover reduce RTO and RPO. Inventory deduction includes cache‑based display and real‑time verification on order submission.

09 Answer 1 – Caching & CDN

Cache product details, categories, and hot‑product images in Redis; cache static resources in a CDN to cut database and origin‑server hits, lowering response time.

10 Answer 2 – Service Scaling & Load Balancing

Make the order service stateless, deploy multiple instances, and use load balancing to eliminate single‑point failures and increase throughput.

11 Answer 3 – Asynchronous Message Queue

Keep only core order creation in the main path; offload SMS, points, logging, etc., to Kafka. Inventory deduction must remain synchronous or use a reliable pre‑deduction scheme.

12 Answer 4 – Database Optimization

Start with slow‑SQL analysis and execution plans, add appropriate indexes, then consider read‑write splitting, and only later sharding when data volume demands it. Avoid premature sharding because of its complexity (cross‑DB queries, distributed transactions, data migration, global IDs, pagination issues).

13 Answer 5 – Rate Limiting & Degradation

Apply rate limiting at the gateway or service entry for flash‑sale and order APIs, and degrade non‑core features (recommendations, comments, complex reports) during spikes to preserve browsing, ordering, and payment.

14 Answer 6 – High Availability & Disaster Recovery

Deploy clusters for order service, Redis, Kafka, and service registry; use primary‑secondary or master‑slave replication for the database; employ heartbeat, health checks, monitoring, and automatic failover to lower RTO and RPO.

15 Answer 7 – Concurrency Control & Consistency

Show cached inventory for fast reads, but re‑verify the latest stock on order submission. Use conditional updates, optimistic/pessimistic locks, or transaction locks to prevent overselling. Design asynchronous processing with idempotency and retry mechanisms.

16 Combined Final Answer

Cache product details, categories, hot images, and static assets with Redis/CDN.

Statelessize the order service, run multiple instances behind a load balancer.

Keep core order creation synchronous; handle SMS, points, logging via Kafka.

Analyze slow SQL, add indexes, use read‑write splitting, consider sharding only when necessary.

Rate‑limit flash‑sale and order endpoints; degrade non‑core features during promotion.

Cluster key components and use replication with health‑check‑driven failover.

Re‑verify inventory on submission using conditional updates or locks; ensure async messages are idempotent.

Self‑Test Questions

What should be prioritized for a slow product‑detail page? → Use Redis/CDN to cache read‑heavy data.

How to speed up a slow order API with SMS, points, logs? → Keep core steps synchronous; offload SMS, points, logs to a message queue.

Can high DB CPU be solved by immediate sharding? → No; first analyze slow queries, optimize indexes/SQL, then consider read‑write splitting before sharding.

What does a single order‑server crash indicate? → A single‑point failure; deploy multiple instances with load balancing and health checks.

Why does displayed stock differ from actual stock on checkout? → Display may use cached data; checkout must re‑validate latest stock.

How to prevent overselling? → Re‑validate stock on order, use conditional updates, optimistic/pessimistic locks, and ensure async processing is idempotent.

Why limit and degrade during a promotion? → To control incoming traffic and protect core browsing, ordering, and payment functions.