From MySQL to 10M QPS: A Full‑Stack Engineering Blueprint for High‑Throughput Transaction Systems

Problem Overview

E‑commerce transaction systems quickly hit performance walls when a single Spring Boot + MySQL service must handle all order‑related writes, inventory checks, payment callbacks, and cross‑service coordination. The synchronous write path becomes a bottleneck, leading to lock contention, connection pool exhaustion, and latency spikes.

Why Simple Optimizations Fail

Increasing CPU, adding indexes, or enlarging connection pools only address isolated symptoms. The core issue is the coupling of strong consistency requirements with high‑volume traffic, which forces every request to traverse a long, fragile write chain.

Key Design Principles

Shortest Critical Path : Keep the synchronous order path to the absolute minimum—parameter validation, risk control, stock reservation, and order acceptance.

Asynchronous Expansion : Offload non‑critical work (order details, marketing, notifications, fulfillment) to message queues.

State‑Driven Architecture : Use explicit state machines and status tables instead of ad‑hoc procedural code.

Idempotency Everywhere : Interface request IDs, message keys, status updates, and compensation tasks must all be idempotent.

Observability First : Metrics, tracing, and alerts are built into every layer to detect lock contention, queue lag, and resource saturation.

Core Architecture Layers

Access Layer : API gateway, authentication, rate‑limiting, hot‑parameter protection.

Transaction Orchestration Layer : Handles only the essential steps and writes a minimal order_status record.

Domain Service Layer : Separate services for order, inventory, payment, and marketing, each owning its state.

Asynchronous Event Layer : RocketMQ topics for order creation, payment success, inventory updates, with transactional messages and retry/dead‑letter handling.

Data Storage Layer : Redis (fast stock pre‑allocation), MySQL (core persistence), Elasticsearch (search), and archival storage.

Stock Reservation with Redis

Hot‑SKU inventory is reserved in Redis using a single‑threaded SETNX ‑style Lua script, guaranteeing atomic decrement and lock record creation. If the script fails, the request is rejected with “库存不足”.

local stockKey = KEYS[1]
local lockKey = KEYS[2]
local qty = tonumber(ARGV[1])
local bizNo = ARGV[2]
local stock = tonumber(redis.call('GET', stockKey) or '0')
if stock < qty then return 0 end
redis.call('DECRBY', stockKey, qty)
redis.call('HSET', lockKey, bizNo, qty)
return 1

Transactional Messaging with RocketMQ

Order creation publishes a transactional message. The local transaction inserts the minimal status record; the broker commits only if the local transaction succeeds, providing exactly‑once guarantees.

Message msg = MessageBuilder.withPayload(JSON.toJSONString(order))
    .setHeader(RocketMQHeaders.KEYS, orderId)
    .setHeader(RocketMQHeaders.TRANSACTION_ID, orderId)
    .build();
rocketMQTemplate.sendMessageInTransaction("order-tx-group", "order-create", msg, order);

Idempotent Order Acceptance

A distributed lock keyed by request_id prevents duplicate submissions. After processing, the lock is released and the request ID is cleared.

if !idempotentService.tryAcquire("order:"+requestId, 300) {
    throw new BizException("重复请求");
}
// process order
idempotentService.release("order:"+requestId);

Payment Callback Handling

Payment callbacks are verified, checked for idempotency, and then update the order status via an atomic SQL UPDATE ... WHERE status = 'UNPAID'. If the update count is zero, the callback is ignored because the order is already paid or closed.

Sharding Strategy

Orders are sharded by order_id (or user_id) to distribute write load across many MySQL instances. Queries use the same key to route to the correct shard; analytical queries are served from Elasticsearch or a data warehouse.

Thread‑Pool & Connection‑Pool Isolation

Separate pools for order intake, payment callbacks, async persistence, and RPC calls.

Dedicated pools for status DB, order shard DB, and read‑only DB to prevent cross‑contamination.

Observability & Alerting

Metrics: QPS, P95/P99 latency, error codes, Redis hit rate, MQ lag, DB TPS, lock wait time.

Tracing: Propagate traceId, requestId, orderId across services.

Critical alerts: order acceptance rate drop, payment‑callback backlog, MQ consumer lag, Redis replication lag, MySQL lock spikes.

Release & Multi‑Env Governance

Features are gated by configuration flags, MQ tags, and can be gray‑released to a small traffic slice. Separate topics, Redis clusters, and DB instances isolate dev, test, pre‑prod, and production environments.

Capacity Planning & Stress Testing

Test real‑world scenarios: hotspot SKU bursts, payment‑callback storms, consumer retry buildup, shard hot‑spot writes.

Measure not only success rate but also P99 latency, consumer lag recovery, and automatic compensation convergence.

Incident Cases & Lessons Learned

Payment‑callback queue buildup blocked order status updates – solved by separating payment status consumer from fulfillment consumer.

Redis master‑slave switch caused overselling – solved by stricter write‑availability checks and inventory reconciliation jobs.

Shard expansion without dual‑write caused missing orders – solved by versioned routing, dual‑write during migration, and post‑migration data verification.

Conclusion

The transformation from a monolithic MySQL service to a multi‑layered, asynchronous transaction system is less about raw hardware and more about engineering mindset: shrink the synchronous critical path, make every state transition explicit and idempotent, and embed observability and fault‑tolerance as core capabilities.