How Merged Seckill Boosts MySQL Write Throughput 5× for High‑Traffic E‑Commerce

Background

Flash‑sale (秒杀) scenarios are a classic high‑concurrency workload for e‑commerce platforms. Xiaohongshu’s live‑streaming sales required order throughput exceeding 10,000 orders per second, making database write performance a critical metric.

Merge‑Seckill Optimization Overview

By extending the MySQL kernel, the merge‑seckill design aggregates concurrent updates into a single combined transaction. Compared with the traditional queue‑based approach, write throughput improves roughly fivefold, and performance can be up to one hundred times higher than the community MySQL version. The change is transparent to business logic: upgrading the MySQL kernel alone yields the gain without any SQL modifications.

Sysbench stock‑deduction tests show TPS rising from 4,276 to 23,543 on 128 threads (≈5.5×) and maintaining a 4.7× boost at 1,024 threads. The optimal thread count is 128–256 to limit thread‑switch overhead.

Classic Stock‑Deduction Transaction Model

BEGIN;
INSERT INTO inventory_log VALUES (...);  -- log row
UPDATE inventory SET quantity = quantity‑1 WHERE sku_id=? AND quantity>0;  -- deduct stock
COMMIT;

Under high concurrency the UPDATE on the same row becomes a bottleneck, dropping TPS to 100–200 and causing severe stalls.

Queue‑Based Seckill vs. Merge‑Seckill

The queue‑based method serializes updates by placing each transaction in a FIFO queue, limiting scalability. Merge‑seckill aggregates multiple updates into a single transaction, allowing concurrent inserts while protecting the critical UPDATE section.

Design Details

Cache Visibility

A global cache is introduced at the table level. All threads operating on the same table share this cache, whose lifecycle matches the table’s, simplifying management and enabling data sharing among threads.

Data Consistency (Leader‑Follower Model)

Three clients issue identical UPDATE statements; the merge‑seckill mode bypasses the queue.

The first thread acquires an exclusive lock, becomes the Leader, reads InnoDB data, writes the modified row to the global cache, releases the lock, and waits.

Subsequent threads become Followers: they copy the global cache to a thread‑local cache, apply their own deduction, write back to the global cache, and release the lock.

Followers repeat the read‑deduct‑write cycle until all updates are applied.

The Leader aggregates the final global cache, initiates a two‑phase commit (2PC), writes the merged result to the storage engine, and wakes Followers to finish.

Row‑Lock Extreme Optimization

The process is split into two phases: (1) collecting updates into the cache, and (2) committing the aggregated result. While one group commits, the next group can start collecting, reducing overall latency.

Binlog Parallel Commit

All transactions in a group share a single binlog batch, which the Leader commits in one step, improving durability and replication efficiency.

Crash Recovery Enhancements

During crash recovery, the binlog generates a transaction set that is compared against the redo log. Because merge‑seckill writes the entire group’s binlog as a unit, recovery must handle the whole group atomically to maintain consistency.

Advantages of Merge‑Seckill

Zero impact on ecosystem components : Changes are confined to the MySQL kernel; binlog format remains unchanged, so downstream tools (e.g., DTS, Canal) require no upgrades.

Kernel‑upgrade transparency : No InnoDB format changes; version compatibility and rollback remain safe.

No business‑SQL modification : The mode is compatible with existing queue‑seckill SQL, allowing dynamic switching without DBA intervention.

Key Metrics

TPS: 4,276 → 23,543 (≈5.5×) on 128 threads.

Sustained order throughput: >15,000 orders/s.

Performance gain remains >4.7× at 1,024 threads.

Illustrative Diagrams